如何在Laravel复杂DB Raw查询中使用Paginate类

嘿，我懂你这种困扰——当原生查询逻辑比较复杂时，直接用Laravel自带的paginate()确实会卡壳。针对你给出的查询，我整理了几个可行的解决方案：

方案1：手动使用Paginator类实现分页 #

因为DB::select()返回的是普通数组，我们可以手动计算分页参数，再用Laravel的Paginator来包装结果：

use Illuminate\Pagination\Paginator;
use Illuminate\Support\Facades\Request;

// 1. 获取当前页码，默认1
$page = Request::get('page', 1);
// 2. 每页显示条数，对应你原来的LIMIT 9
$perPage = 9;
// 3. 计算偏移量
$offset = ($page - 1) * $perPage;

// 获取当前页的数据
$data = DB::select(DB::raw("
    SELECT DISTINCT c.id, c.name, c.active, c.image, c.created_at,
           (SELECT COUNT(*) FROM categories WHERE c.id=parent_id) AS subcat_count,
           (SELECT COUNT(*) FROM post_ads WHERE c.id=cat_id) AS postad_count
    FROM categories AS c
    LEFT OUTER JOIN categories AS subc ON c.id = subc.parent_id
    LEFT OUTER JOIN post_ads AS postadc ON c.id = postadc.cat_id
    WHERE c.parent_id=0
    ORDER BY c.name ASC
    LIMIT ? OFFSET ?
"), [$perPage, $offset]);

// 获取总记录数（注意去掉原查询的LIMIT和OFFSET，只保留主查询逻辑）
$total = DB::select(DB::raw("
    SELECT COUNT(DISTINCT c.id) as total
    FROM categories AS c
    LEFT OUTER JOIN categories AS subc ON c.id = subc.parent_id
    LEFT OUTER JOIN post_ads AS postadc ON c.id = postadc.cat_id
    WHERE c.parent_id=0
"))[0]->total;

// 实例化分页器
$paginatedData = new Paginator($data, $perPage, $page, [
    'path' => Request::url(),
    'query' => Request::query(),
]);

这样你就可以像使用普通分页结果一样，在视图里调用$paginatedData->links()渲染分页链接了。

方案2：将原生查询转换为查询构造器写法（推荐） #

如果能把原生查询转成Laravel查询构造器的语法，就能直接调用paginate()，这是最符合Laravel生态的方式：

$data = DB::table('categories as c')
    ->select([
        'c.id',
        'c.name',
        'c.active',
        'c.image',
        'c.created_at',
        DB::raw('(SELECT COUNT(*) FROM categories WHERE c.id=parent_id) AS subcat_count'),
        DB::raw('(SELECT COUNT(*) FROM post_ads WHERE c.id=cat_id) AS postad_count')
    ])
    ->leftJoin('categories as subc', 'c.id', '=', 'subc.parent_id')
    ->leftJoin('post_ads as postadc', 'c.id', '=', 'postadc.cat_id')
    ->where('c.parent_id', 0)
    ->distinct()
    ->orderBy('c.name', 'asc')
    ->paginate(9);

这段代码和你原生查询的逻辑完全一致，但现在可以直接使用$data->links()，Laravel会自动帮你处理分页参数和总条数计算。

方案3：使用子查询包装原生查询 #

如果实在不想改动原生查询的结构，可以用子查询把你的查询包装起来，然后调用分页：

$subQuery = DB::raw("
    SELECT DISTINCT c.id, c.name, c.active, c.image, c.created_at,
           (SELECT COUNT(*) FROM categories WHERE c.id=parent_id) AS subcat_count,
           (SELECT COUNT(*) FROM post_ads WHERE c.id=cat_id) AS postad_count
    FROM categories AS c
    LEFT OUTER JOIN categories AS subc ON c.id = subc.parent_id
    LEFT OUTER JOIN post_ads AS postadc ON c.id = postadc.cat_id
    WHERE c.parent_id=0
    ORDER BY c.name ASC
");

$data = DB::table(DB::raw("($subQuery) as categories"))
    ->paginate(9);

这种方式也能让Laravel自动处理分页，不过注意子查询需要设置别名，不然会触发SQL语法错误。

内容的提问来源于stack exchange，提问作者Sanjay