时间戳

首页 > 解决方案 > 我如何将 paginate() 函数与 db::select 一起使用

laravel - 我如何将 paginate() 函数与 db::select 一起使用

问题描述

首先，我对如何通过简单查询使用分页功能感兴趣。例如，我有这个查询：

$query = "SELECT 
 dw.id,
 w.word,
 d.definition,
 dw.user_id,
 IF(IFNULL(dw.definition_id, 0) = 0,
    (SELECT COUNT(phrase_id) 
     FROM phrase_word wp1 WHERE wp1.user_id = dw.user_id AND wp1.word_id = dw.word_id),
    (SELECT COUNT(phrase_id)
     FROM phrase_word wp2 WHERE wp2.user_id = dw.user_id AND wp2.word_id = dw.word_id AND wp2.definition_id = dw.definition_id)
   ) phrases_count
 FROM definition_word AS dw
 INNER JOIN words AS w ON w.id  = dw.word_id 
 LEFT JOIN definitions AS d ON d.id = dw.definition_id
 WHERE dw.user_id = 2";

我不能以这种方式使用分页方法。 $unknown_words = DB::select($query)->paginate(5);

或者如果有可能如何转换

"IF(IFNULL(dw.definition_id, 0) = 0,
    (SELECT COUNT(phrase_id) 
     FROM phrase_word wp1 WHERE wp1.user_id = dw.user_id AND wp1.word_id = dw.word_id),
    (SELECT COUNT(phrase_id)
     FROM phrase_word wp2 WHERE wp2.user_id = dw.user_id AND wp2.word_id = dw.word_id AND wp2.definition_id = dw.definition_id)
   ) phrases_count";

将这部分查询放入查询构建器中。

标签： laraveleloquentquery-builderpagination

解决方案

我认为你有一个更好的选择，你可以使用 laravel joins select 和 table 方法，你可以实现你想要的输出，如下所示。

 $unknown_words = DB::table('definition_word as dw')
 ->join('words as w', 'w.id', '=', 'dw.word_id')
 ->leftJoin('definitions as d', 'd.id', '=', 'dw.definition_id')
 ->select(
    'dw.id',
    'w.word',
    'd.definition',
    'dw.user_id',
        DB::raw('IF(IFNULL(dw.definition_id, 0) = 0,
            (SELECT COUNT(phrase_id) 
            FROM phrase_word wp1 WHERE wp1.user_id = dw.user_id AND wp1.word_id = dw.word_id),
            (SELECT COUNT(phrase_id)
            FROM phrase_word wp2 WHERE wp2.user_id = dw.user_id AND wp2.word_id = dw.word_id AND wp2.definition_id = dw.definition_id)
        ) as phrases_count')
    )->where('dw.user_id', 2)->paginate(15)

推荐阅读