将原始sql查询转换为laravel db builder查询

cczfrluj  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(429)

我需要帮助使用db query builder将以下原始查询转换为laravel查询。查询必须使用db(facade)查询生成器构建,而不是雄辩的。

SELECT distinct p.client_id
  FROM people p
JOIN (SELECT client_id, COUNT(*)
  FROM people
GROUP BY client_id
HAVING count(client_id) > 1 ) b
ON p.client_id = b.client_id
ORDER BY p.client_id')

这是我目前的解决方案:

$subQuery = DB::table('people')
            ->select('client_id', DB::raw('count(*)'))
            ->groupBy('client_id')
            ->havingRaw('count(client_id) > ?', [1]);

$mainQuery = DB::table('people')
            ->distinct('client_id')
            ->joinSub($subQuery, 'b', function ($join) {
                $join->on('people.client_id', '=', 'b.client_id');
            })
            ->orderBy('client_id')
            ->get();
r7knjye2

r7knjye21#

尝试 DB::raw() 比如:

DB::select(DB::raw("Your query here"));
``` `DB::raw()` 用于生成任意sql命令,这些命令不会被查询生成器进一步解析。
mnemlml8

mnemlml82#

我找到了我想要的解决方案:

$subQuery = DB::table('people')
   ->select('client_id', DB::raw('count(*)'))
   ->groupBy('client_id')
   ->havingRaw('count(client_id) > ?', [1]);

$peopleWithSameClient = DB::table('people')
   ->distinct('people.client_id')
   ->joinSub($subQuery, 'b', function ($join) {
          $join->on('people.client_id', '=', 'b.client_id');
   })
   ->orderBy('people.client_id')
   ->get();

相关问题