Laravel OrderBy by related table column

Question

I have a table (A) that has a One to Many relation with another table (B).

I want to query Table A and eager load Table B with the Table A results - but I also want to sort Table A by a value in Table B.

I have tried using OrderBy in the query and also trying SortBy on the resultant collection but cannot get the Table A data to be sorted by the value found in Table B.

Example of what I have tried:

$query = ModelA::with("ModelB"])->get()->sortByDesc('ModelB.sortValue');

Keep in mind, I am only interested in the LATEST record from Table B. So I need to query Table A and sort by a value in the LATEST records of Table B.

How can I achieve this?

~~EDIT:~~

The below (as suggested by @ljubadr) works pretty close, but the issue is that there are many record in Table B which means that it doesn't reliably sort as it doesn't seem to sortby the latest records in Table B. Can I have the join return ONLY the latest record for each ID?

$query = ModelA::select('TableA.*') ->join('TableB', 'TableA.id', '=', 'TableB.col_id') ->groupBy('TableA.id')->orderBy('TableB.sortCol', 'desc') ->with(['x']) ->get();
EDIT 2:
@Neku80 answer has gotten me closest but it seems to not sort the column with the greatest accuracy.. I'm sorting a Decimal column and for the most part it is in order but in some places the items are out of order..

$latestTableB = ModelB::select(['TableA_id', 'sortByColumnName'], DB::raw('MAX(created_at) as created_at')) ->groupBy('TableA_id'); $query = ModelA::select('TableA.*') ->joinSub($latestTableB, 'latest_TableB', function ($join) { $join->on('TableA.id', '=', 'latest_TableB.TableA_id'); }) ->orderBy('latest_TableB.sortByColumnName') ->get();

For example, the ordering is like:

0.0437 0.0389 0.0247 <-- -1 0.025 <-- +1 0.0127

~~When I delete all rows except for the 'latest' rows, then it orders correctly, so it still must be ordering with old data...~~

I have found a solution:

ModelA::select('TableA.*', 'TableB.sortByCol as sortByCol')
        ->leftJoin('TableB', function ($query) {
            $query->on('TableB.TableA_id', '=', 'TableA.id')
            ->whereRaw('TableB.id IN (select MAX(a2.id) from TableB as a2 join TableA as u2 on u2.id = a2.TableA_id group by u2.id)');
        })
        ->orderBy('TableB.sortByCol')
        ->get();

You could use join and group by. Try something like this $rows = ModelA::with('ModelB')->select('table_a.*')->join('table_b', 'table_a.id', '=', 'table_b.a_id')->groupBy('table_a.id')->orderBy('table_b.<column>')->get(); — ljubadr
– ljubadr, Commented Jan 25, 2022 at 8:51
@ljubadr Thanks, I tried your first method but get ORDER BY clause is not in GROUP BY clause and contains nonaggregated column error. — Tim Rowley
– Tim Rowley, Commented Jan 25, 2022 at 9:01
^^ Fixed the above, changed Strict mode to false in laravel DB config. But still not ordering by the value I need. Could this be because I need to select the LATEST records from Table B somehow as there are many records? — Tim Rowley
– Tim Rowley, Commented Jan 25, 2022 at 9:12

James Risner · Accepted Answer · 2022-10-23 22:51:02Z

3

Another alternative to order is like this:

$users = User::orderBy(
    Company::select('name')
        ->whereColumn('companies.user_id', 'users.id'),
    'asc'
)->get();

Here we are ordering in asc order by company name field.

In this article it is explained in detail.

edited Oct 23, 2022 at 22:51

James Risner

6,09711 gold badges33 silver badges60 bronze badges

answered Oct 19, 2022 at 8:14

Qendrim Vrella

311 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Majid Akbari · Accepted Answer · 2022-01-25 09:00:46Z

0

You can simply execute a left join query:

ModelA::query()->leftJoin('model_b_table', 'model_a_table.primary_key', '=', 'model_b_table.foreign_key')->orderBy('model_a_table.target_column')->get();

answered Jan 25, 2022 at 9:00

Majid Akbari

3003 silver badges11 bronze badges

1 Comment

Tim Rowley Over a year ago

I don't think it's that simple.. that would also return the many records from Model B

Neku80 · Accepted Answer · 2022-01-25 09:49:01Z

0

This should work if you only need TableB's ID and created_at columns:

$latestTableB = ModelB::select('TableA_id', DB::raw('MAX(created_at) as created_at'))
    ->groupBy('TableA_id');

$query = ModelA::select('TableA.*')
    ->joinSub($latestTableB, 'latest_TableB', function ($join) {
            $join->on('TableA.id', '=', 'latest_TableB.TableA_id');
        })
    ->orderBy('latest_TableB.created_at')
    ->get();

answered Jan 25, 2022 at 9:49

Neku80

4715 silver badges16 bronze badges

1 Comment

Tim Rowley Over a year ago

It's gotten me close to solving it, thank you, but please see the latest edit with what is happening with your example code.

Collectives™ on Stack Overflow

Laravel OrderBy by related table column

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related