Laravel OrderBy by related table column-CodePudding

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();

CodePudding user response：

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();

CodePudding user response：

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();