Laravel Fluent Query Builder 加入子查询 [英] Laravel Fluent Query Builder Join with subquery

查看:42
本文介绍了Laravel Fluent Query Builder 加入子查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

好的,经过数小时的研究并仍在使用 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 中的流畅查询构建器来执行此操作,但没有成功.

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.

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

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 Query Builder 加入子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆