Laravel Query Builder Join clause is ignoring orderBy()

user_9807367 0
Viewed 164

I'm using Laravel query builder and I would like to ask how specify which row to use for the left join if many rows are returned?

I have code like that:

$builder->leftJoin('table2', function (JoinClause $join) {
    $join->on('table2.id', '=', "table1.id")
         ->on('table2.region', '=', "table1.region")
         ->orderBy('table2.updatedAt', 'DESC') // This is being ignored.
         ->limit(1);

});

The part $join->on()->on() returns multiple rows so I need to specify which one to use for the left join. I though I can order it by table2.updatedAt and then it will use the first row for the left join. But it is completly ignoring the orderBy. So I dont know how to get the one specific row.

Edit: Also its possible that some of the rows from table2 have exactly same table2.updatedAt. In that case it does not matter which of the ones with same table2.updatedAt will be selected.

query-builder laravel mysql
1 Answers

Checking the query in laravel, I've found that applying ORDER BY doesn't apply any clause to the query.

Reading your requirements, you want to achieve the record with the largest updatedAt field for the criteria specified. I would personally opt for a MAX() query in a view if permitted, but views can be restricted or not used by organisation, and its good to know how to do it in line with your question, plus it means you dont have to maintain a view. Optionally, you can do a MAX() in a subquery, but that requires a bit more setup.

First, you inner join on the fields you require. Second, you do a left join to look for values within that join that are bigger than the one you want the MAX for. So we want the most recent updatedAt field. Finally, you WHERE the largest updatedAt value has no value bigger than it, so its the true max: where that value is NULL.

Code is remixed without the leftJoin clause object, Im passing your builder everywhere. I cant see the rest of your query, but I think this should drop straight in since were only up to 2 tables ie table2. If you supply 1 table.id in the WHERE clause, you will get 1 row back.

Update: You need to do the same with Id, so if you have 2 datetimes with the same timestamp, you pick the one with the higher ID:

    $sql->join('table2', function ($sql) {
        $sql->on('table2.id', '=', "table1.id")
            ->on('table2.region', '=', "table1.region");
    });

    $builder->leftJoin('table2 AS biggerThanUpdatedAt', function ($builder) {
        $builder->on('table2.id', '=', "biggerThanUpdatedAt.id")
            ->on('table2.region', '=', "biggerThanUpdatedAt.region")
            ->on('table2.updatedAt', '<', 'biggerThanUpdatedAt.updatedAt');
    });

    $builder->leftJoin('table2 AS biggerThanId', function ($builder) {
        $builder->on('table2.id', '<', "biggerThanId.id") //<-notice the greater at is here this time.
            ->on('table2.region', '=', "biggerThanId.region")
            ->on('table2.updatedAt', '=', 'biggerThanId.updatedAt');
    });

    $builder->whereNull('biggerThanUpdatedAt.updatedAt');
    $builder->whereNull('biggerThanId.id');
user_9764820
Related