Laravel DB查询，其中日期大于不使用联接

Question

我已经编写了一个非常适合我需要的SQL查询，我正在尝试将它添加到Laravel中的一个排队作业中。我没有使用雄辩的模型，而是希望使用DB查询来确保更好的性能，因为最终会有许多连接和条件。在检查日期时，where子句有问题，但它在SQL中运行时工作得非常好。我删除了该查询的其他部分，仅包括调试此问题所必需的内容。

原始SQL：

SELECT `messages`.`created_at`, `participants`.`last_read`
FROM `messages` 
LEFT JOIN `participants` ON `messages`.`thread_id` = `participants`.`thread_id` AND `messages`.`user_id` != `participants`.`user_id` 
WHERE `messages`.`created_at` > `participants`.`last_read`;

当我直接在SQL中运行这个时，我看到2个结果，这与我当前的数据是意料之中的。

我在拉拉维尔是这样写的：

$query = DB::table('messages')
    ->leftJoin('participants', function ($join) {
        $join->on('messages.thread_id', '=', 'participants.thread_id')
            ->on('messages.user_id', '!=', 'participants.user_id');
    })
    ->select('messages.created_at as message_date', 'participants.last_read as last_read')
    ->where('messages.created_at', '>', 'participants.last_read');

当我执行此操作时，结果为空。我从DB Query builder转储了最终的SQL，以确保它是正确的，它是这样的：

select `messages`.`created_at` as `message_date`, `participants`.`last_read` as `last_read`
from `messages`
left join `participants`
on `messages`.`thread_id` = `participants`.`thread_id` and `messages`.`user_id` != `participants`.`user_id`
where `messages`.`created_at` > participants.last_read

并且直接在SQL中运行它将返回准确的结果。

对于上下文，这里是数据结构和我正在使用的一些数据。

与会者

消息

DB查询代码似乎导致了具有相似名称的列被混在一起。这两个表都有一个名为created_at的列，但我只需要messages表中的该列。我的select只请求该列，指定正确的表。但是这个DB查询连接中的某些东西导致了它的混淆。

使用不同的联接，并删除where子句，我意识到日期并不总是正确的。例如，下面是我使用leftjoin时的结果

{
  "message_date": "2021-03-23 00:30:42",
  "last_read": "2021-03-26 00:22:48"
},
{
  "message_date": "2021-03-23 00:31:25",
  "last_read": "2021-03-26 00:22:48"
}

注意，message_date和last_read值与直接运行SQL时的值相反。所以这一定是问题所在。

我更改为RightJoin，结果是反向的：

{
    "message_date": "2021-03-26 19:02:53",
    "last_read": "2021-03-23 19:31:30",
},
{
    "message_date": "2021-03-26 19:02:58",
    "last_read": "2021-03-23 19:31:30",
}

那应该管用，对吧？我重新添加了where子句，但结果仍然是空的。

我想我需要做一些事情来告诉查询生成器正确处理这些列，因为它们似乎在where和select过程中被混淆了。但我不知道该怎么澄清。我试着寻找与此问题相关的其他人，但我似乎找不到任何相关的东西。

我已经尝试了几件事情，结果都没有变化。

• Changing the order of the commands - like moving the select() to the beginning of the statement, things like this.
• Using whereDate instead of where. (Note - for performance I'd rather avoid this, but wanted to try just in case).
• Using join, joinLeft, and joinRight.
• Using where in the on clause instead of two ons. Like this

  ->leftJoin('participants', function ($join) {
      $join->on('messages.thread_id', '=', 'participants.thread_id')
          ->where('messages.user_id', '!=', 'participants.user_id');
  })
  

  有人对我可以尝试的事情有什么指导吗？这应该是一个如此简单的任务，并且已经变成了数小时的努力来理解为什么它在SQL中工作，而不是在Laravel的DB查询构建器中工作。

Answer 1

查询生成器的where函数将始终假定右侧是一个值，并将其作为文字（在本例中为字符串）在准备好的语句中使用。如果要比较列，则需要使用whereColumn:

$query = DB::table('messages')
    ->leftJoin('participants', function ($join) {
        $join->on('messages.thread_id', '=', 'participants.thread_id')
            ->on('messages.user_id', '!=', 'participants.user_id');
    })
    ->select('messages.created_at as message_date', 'participants.last_read as last_read')
    ->whereColumn('messages.created_at', '>', 'participants.last_read')->get();

单据中可以找到其他where条款