Sql/laravel - Why Does My Query Returns An Empty Collection?

My Laravel Query Builder returns an empty collection, while the SQL string itself returns the correct records when executed inside phpmyadmin. This is my code: $times = Time::join(

Solution 1:

That's something a bit "tricky". The query you are executing in phpmyadmin is, indeed, correct. However, Laravel uses where() and on() differently.

Use where() with a value and on() when working with columns.

$times = Time::join(\DB::raw('(SELECT `ath_id`, `stroke_id`, MIN(time) AS time FROM times GROUP BY ath_id, stroke_id) b'), function($join) {
                 $join->on('times.ath_id', '=', 'b.ath_id')
                      ->on('times.stroke_id', '=', 'b.stroke_id')
                      ->on('times.time', '=', 'b.time');
             })
             ->where('times.ath_id', '=', $id)
             ->orderBy('times.stroke_id', 'ASC')
             ->orderBy('times.date', 'DESC');

Docs: https://laravel.com/docs/5.4/queries, section (CTRL+F): Advanced joins

If you would like to use a "where" style clause on your joins, you may use the where and orWhere methods on a join. Instead of comparing two columns, these methods will compare the column against a value.

To make a bit clearer:

$join->on('times.ath_id', '=', 'b.auth_id')->where('times.stroke_id', '=','b.stroke_id');

results in:

JOIN on `times`.`ath_id` = `b`.`auth_id` WHERE `times`.`stroke_id` = 'b.stroke_id' -- AS STRING

The confusion came when toSql() returned your query and you assumed that Laravel knows that:

['b.stroke_id', 'b.time', '4298584']

the first two bind bindings are columns. But where() thinks that they just strings.