Issue with Laravel pagination()

[joecip]

Attempting to utilize orderByRelation('relationship:field')->paginate(25)

->orderByRelation([ 'relationship:column' => function ($query) use ($account_id) { $query->where('account_id', $account_id); }, 'desc' ]) ->paginate(25)

This works great on page 1, but as soon as I go to page 2+, the query errors:

SQLSTATE[HY000]: General error: 20018 Invalid column name 'relationship_field_max'. [20018] (severity 16)

[Alexmg86]

Hi @joecip
To be honest, I could not catch such an error. For example:

$account_id = 6;
$invoices = Invoice::orderByRelation(['items:price' => function ($query) use ($account_id) {
    $query->where('price', $account_id);
}, 'desc'])->paginate(5);

i will have that sql query for 3 page:

select `invoices`.*, (select max(price) from `items` where `invoices`.`id` = `items`.`invoice_id` and `price` = 6) as `items_price_max` from `invoices` order by `items_price_max` desc limit 5 offset 10

And it's work for me

[joecip]

@Alexmg86 I think I’ve identified it down to Microsoft SQL Server which does not allow for order by alias. The full su query is required which is why that error produces on the alias column.

[Alexmg86]

@joecip
No, SQL is much more powerful than MySql and this query will work there as well. Try to run it directly in Sql Studio, I'm sure it will work.

[joecip]

@Alexmg86 Here are the queries from MS SQL:

Page 1:
select
[accounts].*,
(select
max(total_items)
from
[counts_view]
where
[accounts].[account_id] = [counts_view].[collector_id]
and [agent_id] = '157') as [counts_view_total_items_max]
from
[accounts]
where
[account_status_id] = 'A'
order by
[counts_view_total_items_max] desc

And here is Page 2 which triggers the error:
select
*
from
(select
[accounts].*,
(select
max(total_items)
from
[counts_view]
where
[accounts].[account_id] = [counts_view].[collector_id]
and [agent_id] = 157) as [counts_view_total_items_max],
row_number() over (
order by
[counts_view_total_items_max] desc) as row_num
from
[accounts]
where
[account_status_id] = 'A'
) as temp_table
where
row_num between 11 and 20
order by
row_num

[Alexmg86]

@joecip
Hey! I tried using the default SQL and the pagination works well. But you have some kind of non-standard request for the following pages. To be honest, I find it difficult to advise anything here so far.