Laravel ORM join 使用子查询 (2490 views)

2018-04-10 07:12:07

遇到这样一个 SQL 语句：

SELECT
    `mm_tags`.`id`,
    `mm_tags`.`name`,
    `tb`.`count` 
FROM
    `mm_tags`
    JOIN ( SELECT `tag_id` AS `id`, count( * ) AS `count` FROM `mm_taggables` WHERE `taggable_type` = 'App\\Models\\Goods' GROUP BY `tag_id` ) AS `tb` ON `tb`.`id` = `mm_tags`.`id` 
WHERE
    `mm_tags`.`count` > 0 
ORDER BY
    `tb`.`count` DESC;

使用 Laravel 的 ORM 来写

$prefix = config('database.connections.mysql.prefix');

$taggableQuery = DB::table('taggables')
    ->select(DB::raw('tag_id as id, count(*) as count'))
    ->where('taggable_type', static::class)
    ->groupBy('tag_id');

$tags = Tag::select('tags.id', 'tags.name', 'tb.count')
    ->join(DB::raw("({$taggableQuery->toSql()}) as {$prefix}tb"), 'tb.id', 'tags.id')
    ->mergeBindings($taggableQuery)
    ->where('tags.count', '>', 0)
    ->orderBy('tb.count', 'desc')
    ->get();

注意：这里 mergeBindings() 子句和 join() 子句的位置，如果位置不对，Laravel 解析出来的参数顺序会搞错。

Tags: Laravel SQL join 子查询 ORM