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

我从另一个帖子中得到了这个查询 这里在stackoverflow上.

I got this query from another post here on stackoverflow.

我已经尝试了所有方法来使用 Laravel 中的 fluent 查询构建器来做到这一点，但是没有成功.

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?

推荐答案

好吧，对于那些在绝望中寻找同样问题的人来说.我希望你能比我更快地找到这个；O.

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.

这里是代码.不同的表和名称，但你会明白吗?它t

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屋！