SQL/Laravel-为什么我的查询返回空集合? [英] SQL/Laravel - Why does my query returns an empty collection?

问题描述

我的Laravel查询生成器返回一个空集合，而在phpmyadmin中执行时，SQL字符串本身返回正确的记录.

My Laravel Query Builder returns an empty collection, while the SQL string itself returns the correct records when executed inside phpmyadmin.

这是我的代码:

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

下面是在phpmyadmin中运行的sql，但不适用于laravel查询生成器.另外，这是使用dd($times->toSql());时返回的SQL字符串(其中$times->getBindings()返回['b.stroke_id', 'b.time', '4298584']来填写问号?)

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

推荐答案

有点棘手".您在phpmyadmin中执行的查询确实是正确的.但是，Laravel使用where()和on()的方式有所不同.

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

在处理列时，将where()用作值，将on()用作

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

文档: https://laravel.com/docs/5.4/queries ，该部分(CTRL + F):高级加入

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

如果要在联接上使用"where"样式子句，则可以在联接上使用where和orWhere方法.这些方法将比较列和一个值，而不是比较两个列.

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.

更清楚一点:

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

导致:

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

当toSql()返回您的查询并且您假设Laravel知道以下信息时，混乱就出现了:

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

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

前两个绑定绑定是列.但是where()认为它们只是字符串.

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

这篇关于SQL/Laravel-为什么我的查询返回空集合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！