Laravel Fluent Query Builder 使用子查询进行连接

11 浏览
0 Comments

Laravel Fluent Query Builder 使用子查询进行连接

经过几个小时的研究，我仍然在使用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

我从stackoverflow的另一个帖子中获得了这个查询，请查看这里。

我尝试了使用Laravel的查询构建器来实现这个目标，但是没有成功。

我知道手册上说可以这样做：

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

但是这并没有帮助太多，因为我不知道如何在其中使用子查询？

有人能帮我开心一下吗？

0

3 答案

0 Comments

Laravel Fluent Query Builder中的join操作可以通过使用joinSub方法来实现与子查询的连接。该方法在Laravel版本5.6及以上的版本中原生支持，但在低于5.6版本的Laravel中可以通过在应用程序服务提供者文件中注册宏来实现。

下面是一个使用joinSub方法的示例代码：

$subquery = DB::table('catch-text')
    ->select(DB::raw("user_id, MAX(created_at) as MaxDate"))
    ->groupBy('user_id');
$query = User::joinSub($subquery, 'MaxDates', function($join) {
    $join->on('users.id', '=', 'MaxDates.user_id');
})->select(['users.*', 'MaxDates.*']);

以上代码中，首先使用DB门面的table方法创建一个子查询，该子查询从catch-text表中选择user_id和最大的created_at，并按user_id分组。然后，使用joinSub方法将该子查询与User模型进行连接，连接条件是users表的id与MaxDates表的user_id相等。最后，通过select方法选择需要的字段。

如果你的Laravel版本低于5.6，你可以在应用程序服务提供者文件中注册宏来实现joinSub方法。具体的实现可以参考这个链接：https://github.com/teamtnt/laravel-scout-tntsearch-driver/issues/171#issuecomment-413062522

0
0 Comments

问题的原因是作者想要解决一个相关问题：找到每个分组中的最新记录，这是典型的greatest-n-per-group问题，其中N = 1。解决方案涉及到了在Eloquent中如何构建查询的问题，因此我将其发布出来，希望对其他人有所帮助。它展示了使用强大的Eloquent流接口和多个连接列以及连接子查询中的where条件的更清晰的子查询构建方式。

在这个例子中，我想要获取每个由watch_id标识的分组的最新DNS扫描结果（表scan_dns）。我单独构建了子查询。

我想要Eloquent生成的SQL语句是：

SELECT * FROM `scan_dns` AS `s`
INNER JOIN (
  SELECT x.watch_id, MAX(x.last_scan_at) as last_scan
  FROM `scan_dns` AS `x`
  WHERE `x`.`watch_id` IN (1,2,3,4,5,42)
  GROUP BY `x`.`watch_id`) AS ss
ON `s`.`watch_id` = `ss`.`watch_id` AND `s`.`last_scan_at` = `ss`.`last_scan`

我通过以下方式实现：

// model的表名
$dnsTable = (new DnsResult())->getTable();
// 在子查询中选择的分组
$ids = collect([1,2,3,4,5,42]);
// 要连接的子查询
$subq = DnsResult::query()
    ->select('x.watch_id')
    ->selectRaw('MAX(x.last_scan_at) as last_scan')
    ->from($dnsTable . ' AS x')
    ->whereIn('x.watch_id', $ids)
    ->groupBy('x.watch_id');
$qqSql = $subq->toSql();  // 编译为SQL
// 主查询
$q = DnsResult::query()
    ->from($dnsTable . ' AS s')
    ->join(
        DB::raw('(' . $qqSql. ') AS ss'),
        function(JoinClause $join) use ($subq) {
            $join->on('s.watch_id', '=', 'ss.watch_id')
                 ->on('s.last_scan_at', '=', 'ss.last_scan')
                 ->addBinding($subq->getBindings());  
                 // 添加子查询WHERE的绑定
        });
$results = $q->get();

更新：

自Laravel 5.6.17以来，已经添加了子查询连接的本地方法来构建查询。

$latestPosts = DB::table('posts')
               ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
               ->where('is_published', true)
               ->groupBy('user_id');
$users = DB::table('users')
        ->joinSub($latestPosts, 'latest_posts', function ($join) {
            $join->on('users.id', '=', 'latest_posts.user_id');
        })->get();

对于我来说，Laravel 5.2版本的JoinClause没有addBinding方法，还有其他建议吗？

我发现5.6版本的新方式非常好用（使用joinSub、leftJoinSub等），但是我有一个问题，我的子查询没有考虑到连接的ON条件来给连接提供结果。我像这样有一个子查询：`$userInfoSubquery = DB::table('user_info')->select()->orderBy('info_date', 'DESC')->limit(1);`。但是如果最新的信息不属于用户，它就不会返回任何结果... 所以我需要在子查询中做一些类似于：`->where('user_id', '=', userid)`的操作...

你可以直接在子查询中使用查询构建器，或者如果模型中已经有作用域和关联关系，你可以使用Eloquent模型来帮助你。

0
0 Comments

Laravel Fluent Query Builder加入子查询出现问题的原因是，无法直接将子查询对象传递给查询，而是需要通过selectRaw语句将查询字符串注入。如果子查询中有参数，那么传递查询对象变得不可能。为了解决这个问题，需要使用toSql和addBinding方法来处理查询对象。

解决方法如下：

1. 使用DB::raw方法创建子查询字符串，并通过别名给子查询命名。

2. 使用join方法将主查询与子查询连接，并通过on方法指定连接条件。

3. 使用orderBy方法对查询结果进行排序。

4. 使用get方法执行查询并获取结果。

具体的代码示例如下：

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();

通过给子查询添加别名，可以更好地处理查询结果，并且可以避免因为连接的数量不同而导致计算错误的问题。此解决方案非常有效。

0