Doesn't exist query

user_14311769 0
Viewed 132

I have a problem with a query.

in posts table, i have posts.id, then, in reviews table, i have posts_id(foreign key -> posts.id), reviews.id. i want to query those posts_id which doesn't exist in reviews table(post_id).

i am using laravel query builders. i am trying like ->

  $r = DB::table('reviews')
            ->select(DB::raw('count(id) as rev_count, posts_id'))
            ->groupBy('posts_id')
            ->get();
        foreach ($r as $rr)
        {
            $p = DB::table('posts')
                ->select('id')
                ->where('id', '!=', $rr->posts_id)
                ->get();
        }

i have successfully extracted the value. but with too much complexity, maybe buggy. if any there direct query to extract it?

query-builder laravel mysql sql
1 Answers

How about using not exists,

SQL query:

select *
   from posts
  where not exists
    (select reviews.posts_id
       from reviews
      where reviews.posts_id = posts.id)

Equivalent laravel query:

DB::table('posts')
    ->select('id')
    ->whereNotExists(function ($query) {
        $query->select("reviews.posts_id")
              ->from('reviews')
              ->whereRaw('reviews.posts_id = posts.id');
    })
    ->get();

There is this SO link also could be useful for you

user_138780230
Related