CODEWITHSUNDEEP
mysqllaravel
SymmetricsWeb
SymmetricsWeb

Reputation: 636

raw SQL to laravel query builder

How do you convert this raw sql builder to 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

This is what I've tried but doesn't seem to work

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);

Would appreciate any help

Upvotes: 0

Views: 52

Answers (1)

TsaiKoga
TsaiKoga

Reputation: 13394

Because apt.id and aa.application_id are two columns from diff tables, and you are using where to compare the apt.id with string 'aa.application_id'.

You need to use whereColumn to compare two different columns:

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

Upvotes: 2

Related Questions