Laravel. Eloquent query for two tables

Question

I want to write laravel eloquent query which could select all the titles from titles table where title_id doesnt't exist in title_count table. Here is an example.

titles table:

title_id
    1
    2
    3
    4

title_count table:

title_id 
    3
    4

So my query should select titles with id's 1, 2 from title table. To be honest, I have no ideo how to do it. I'm using laravel 5.
I hope you can help me. Thanks in advance!

Answer 1

Use a join to to identify titles that do not appear in title_count.

DB::table('titles')->leftJoin('title_count', 'titles.title_id', '=', 'title_count.title_id')
                   ->select('titles.*')
                   ->whereNull('title_count.title_id')
                   ->get();

Answer 2

Try this

DB::table('titles')->whereNotExists(function($query)
{
    $query->select(DB::raw(1))
       ->from('title_count')
       ->whereRaw('title_count.title_id = titles.title_id');
})->get();

Answer 3

Not tested

DB::table('title_count')
  ->leftJoin('titles as t', 't.title_id', '=', 'title_count.title_id')
  ->select('t.*')
  ->where('t.title_id', '!=', 'title_count.title_id')
  ->get();