Laravel Fluent查询构建器与子查询​​联接 [英] Laravel Fluent Query Builder Join with subquery

问题描述

好几个小时的研究之后，仍然使用DB :: select我必须问这个问题.因为我正要把我的电脑收起来；).

Okay after hours of research and still using DB::select I have to ask this question. Because I am about to trough my computer away ;).

我想获取用户的最后输入(基于时间戳).我可以使用原始sql

I want to get the last input of a user (base on the timestamp). I can do this with the raw sql

SELECT  c.*, p.*
FROM    users c INNER JOIN
(
  SELECT  user_id,
          MAX(created_at) MaxDate
  FROM    `catch-text`
  GROUP BY user_id
 ) MaxDates ON c.id = MaxDates.user_id INNER JOIN
    `catch-text` p ON   MaxDates.user_id = p.user_id
     AND MaxDates.MaxDate = p.created_at

我从另一篇帖子

I got this query from another post here on stackoverflow.

我已经尝试过使用Laravel中的流畅查询构建器来做所有事情，但是都没有成功.

I have tried everything to do this with the fluent query builder in Laravel however with no success.

我知道手册说您可以这样做:

I know the manual says you can do this:

DB::table('users')
    ->join('contacts', function($join)
    {
        $join->on('users.id', '=', 'contacts.user_id')->orOn(...);
    })
    ->get();

但这没有太大帮助，因为我看不到在那里如何使用子查询? 有谁能照亮我的一天?

But that is not helping much because I do not see how I could use a subquery there? Anyone who can light up my day?

推荐答案

好吧，所有在绝望中来到这里的人都在寻找相同的问题.希望您能比我更快找到它.

Ok for all of you out there that arrived here in desperation searching for the same problem. I hope you will find this quicker then I did ;O.

这就是解决方法. JoostK在github上告诉我，要加入的第一个参数是您要加入的表(或数据)".而且他是对的.

This is how it is solved. JoostK told me at github that "the first argument to join is the table (or data) you're joining.". And he was right.

这是代码.不同的表格和名称，但您会明白这个主意吗?它

Here is the code. Different table and names but you will get the idea right? It t

DB::table('users')
        ->select('first_name', 'TotalCatches.*')

        ->join(DB::raw('(SELECT user_id, COUNT(user_id) TotalCatch,
               DATEDIFF(NOW(), MIN(created_at)) Days,
               COUNT(user_id)/DATEDIFF(NOW(), MIN(created_at))
               CatchesPerDay FROM `catch-text` GROUP BY user_id)
               TotalCatches'), 
        function($join)
        {
           $join->on('users.id', '=', 'TotalCatches.user_id');
        })
        ->orderBy('TotalCatches.CatchesPerDay', 'DESC')
        ->get();

这篇关于Laravel Fluent查询构建器与子查询​​联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！