时间戳

首页 > 解决方案 > 如何将 sql 转换为查询生成器 laravel

laravel - 如何将 sql 转换为查询生成器 laravel

问题描述

谁能帮我将此 SQL 转换为查询生成器！

SELECT topwords.*, 
       mw.word AS my_word 
FROM   topwords 
       LEFT JOIN (SELECT DISTINCT words.word 
                  FROM   definition_word 
                         JOIN words 
                           ON words.id = definition_word.word_id 
                  WHERE  definition_word.user_id = $user) AS mw 
              ON topwords.word = mw.word

我对如何在 leftjoin 中使用子查询有疑问！

我试过这样的东西，但它有错误！ 将错误视为图像

DB::table('topwords')
                    ->leftJoin(DB::raw("SELECT DISTINCT
                    words.word
                    FROM definition_word 
                    JOIN words ON words.id = definition_word.word_id
                    WHERE definition_word.user_id = $user as mw"),"topwords.word", "=", "mw.word" )
                    ->select(
                        "topwords.*",
                        "mw.word AS my_word"
                    )->orderBy('id','desc')->paginate(15);

标签： laraveleloquentquery-builder

解决方案

由于分页和聚合，您有此错误

尝试使用 LengthAwarePaginator 进行自定义分页

这是示例：Laracast

所以你需要做这样的事情：

  $query = DB::table('topwords')
                        ->leftJoin(DB::raw("SELECT DISTINCT
                        words.word
                        FROM definition_word 
                        JOIN words ON words.id = definition_word.word_id
                        WHERE definition_word.user_id = $user as mw"),"topwords.word", "=", "mw.word" )
                        ->select(
                            "topwords.*",
                            "mw.word AS my_word"
                        );
    
    
    $paginator = new LengthAwarePaginator($query->get(), $query->count(), $request->input('per_page', 15), $request->input('page', 1));

然后你可以在集合中使用它

推荐阅读