CODEWITHSUNDEEP
mysqlsqllaravellaravel-query-builder
Derk Jan Speelman
Derk Jan Speelman

Reputation: 11949

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(\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')
                      ->where('times.stroke_id', '=', 'b.stroke_id')
                      ->where('times.time', '=', 'b.time');
             })
             ->where('times.ath_id', '=', $id)
             ->orderBy('times.stroke_id', 'ASC')
             ->orderBy('times.date', 'DESC');
dd($times->get());

Below is the sql that works inside phpmyadmin, but not with the laravel query builder. Also, this is the returned SQL string when using dd($times->toSql()); (where $times->getBindings() returns ['b.stroke_id', 'b.time', '4298584'] to fill in the question marks ?)

SELECT * FROM `times`
INNER JOIN (SELECT `ath_id`, `stroke_id`, MIN(time) AS time
            FROM times GROUP BY ath_id, stroke_id) b
ON `times`.`ath_id` = `b`.`ath_id`
    ADN `times`.`stroke_id` = b.stroke_id -- ?
    AND `times`.`time` = b.time -- ?
WHERE `times`.`ath_id` = 4298584 -- ?
ORDER BY `times`.`stroke_id` asc, `times`.`date` desc

Upvotes: 1

Views: 1955

Answers (1)

Ivanka Todorova
Ivanka Todorova

Reputation: 10219

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.

Upvotes: 2

Related Questions