mysql - 原始 SQL 到 laravel 查询构建器

Question

您如何将此原始 sql 构建器转换为 laravel DB Builder

SELECT * 
FROM   `applications` 
WHERE  EXISTS (SELECT * 
               FROM   `applicants` 
                      INNER JOIN `applicant_application` 
                              ON `applicants`.`id` = 
                                 `applicant_application`.`applicant_id` 
               WHERE  `applications`.`id` = 
                      `applicant_application`.`application_id` 
                      AND `user_id` = 18) 
ORDER  BY `created_at` DESC 
LIMIT 20

这是我尝试过但似乎不起作用的方法

DB::table('applications as apt')
->whereExists( function ($q) {
    $q->from('applicants as app')
    ->join('applicant_application as aa', 'app.id', '=', 'aa.applicant_id')
    ->where('apt.id', '=', 'aa.application_id')
    ->where('app.user_id', '=', 18);
})->orderBy('created_at', 'DESC')->paginate(10);

将不胜感激任何帮助

标签： mysqllaravel

Answer 1

因为apt.idandaa.application_id是 diff 表中的两列，您正在使用与字符串where进行比较。apt.id 'aa.application_id'

您需要用于whereColumn比较两个不同的列：

->whereColumn('apt.id', '=', 'aa.application_id')