Laravel Fluent 查询生成器加入子查询

发布于08月08日

好的，经过几个小时的研究，仍然使用DB::select，我不得不问这个问题.因为我要把电脑扔掉；).

我想获取用户的最后一次输入(基于时间戳).我可以使用原始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.

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.

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相关问答推荐

Laravel Fluent 查询生成器加入子查询

推荐答案

Laravel相关问答推荐

如何使中继器项目计数动态Laravel Filament V3？

使用MAATWebSite/EXCEL导入Exel时，不会创建Laravel模型

V-icon 在 Vuetify 3 中不显示图标

如何获得每种类型的总和

如何在 laravel livewire 中按 bool 值排序数组

如何将用户对象绑定到中间件中的请求

laravel render() 方法有什么用？

如何访问 Validator：：extend 中的其他输入属性？

Laravel Blade：@stop VS @show VS @endsection VS @append

克隆后设置 Laravel 项目

如何使用 Laravel 模型访问数据库视图？

如何使用 ajax 请求删除 laravel 5.3 中的记录？

Laravel 框架类在 PHPUnit 数据提供程序中不可用

SQLSTATE [HY000]：一般错误：1005 无法创建表 - Laravel 4

Laravel excel maatwebsite 3.1 导入，excel 单元格中的日期列返回为未知格式数字.如何解决这个问题？

带有时间戳的 Laravel 5.1 eloquent：：attach() 方法

如何访问 Carbon 对象类型？

Laravel - 语法错误，文件意外结束

assets资源不引用公用文件夹(Laravel)

Laravel or where