Laravel Query Builder: Using `Case` with `Joins`

Question

I am trying to replicate the following psql query in laravel:

SELECT 
    DISTINCT 
    I.id, 
    T.id, 
    T.name, 
    I.first_name, 
    I.middle_name, 
    I.last_name, 
    I.dob, 
    CASE WHEN NOT E.email IS NULL THEN '<<' ELSE '' END
FROM  individuals AS I 
LEFT JOIN titles AS T ON I.title_id = T.ID 
LEFT JOIN individuals_emails_map AS IEM ON IEM.individual_id = I.id 
LEFT JOIN emails AS E ON E.id = IEM.email_id;

When I use the query builder and write:

DB::table('individuals AS I')
    ->join('titles AS T', 'T.id', '=', 'I.title_id')
    ->leftjoin('individuals_emails_map AS IEM', 'IEM.individual_id', '=', 'I.id')
    ->leftjoin('emails AS E', 'E.id', '=', 'IEM.email_id')
    ->distinct('I.title_id', 'T.name', 'I.first_name', 'I.middle_name', 'I.last_name', 'I.dob')
    ->select('T.id','T.name', 'I.first_name','I.middle_name','I.last_name','I.dob')
    ->get();

... it works fine, however when I try and insert the CASE line:

DB::table('individuals AS I')
    ->join('titles AS T', 'T.id', '=', 'I.title_id')
    ->leftjoin('individuals_emails_map AS IEM', 'IEM.individual_id', '=', 'I.id')
    ->leftjoin('emails AS E', 'E.id', '=', 'IEM.email_id')
    ->distinct('I.title_id', 'T.name', 'I.first_name', 'I.middle_name', 'I.last_name', 'I.dob')
    ->select('T.id','T.name', 'I.first_name','I.middle_name','I.last_name','I.dob',
              DB::raw("CASE WHEN NOT E.email IS NULL THEN '<<' ELSE '' END"))
    ->get();

... I get the following error:

 Undefined table: 7 ERROR:  missing FROM-clause entry for table "e"

What am I doing wrong?

Answer 1

I think that the problem in your case statement:

 DB::raw("CASE WHEN NOT E.email IS NULL THEN '<<' ELSE '' END"))

it should be:

  DB::raw('case  WHEN  E.email is Not NULL THEN  "<<" Else " " END resultEmail'))