CODEWITHSUNDEEP
phpmysqllaraveleloquent
Evaldas Butkus
Evaldas Butkus

Reputation: 665

Laravel database query with case

I'm using laravel 5 in current project. Here is my query to database:

$users = User::where('status', '=', '3')->orWhere('status', '=', '2')->whereExists(function($query1)
        {
             $query1->select(DB::raw(1))
               ->from('firsts')
               ->whereRaw('firsts.user_id = users.id')
               ->where('status', '=', 'approved');
        })
        ->whereExists(function($query2)
        {
             $query2->select(DB::raw(1))
               ->from('seconds')
               ->whereRaw('seconds.user_id = users.id')
               ->where('status', '=', 'approved');
        })
        ->whereExists(function($query3)
        {
             $query3->select(DB::raw(1))
               ->from('thirds')
               ->whereRaw('thirds.user_id = users.id')
               ->where('status', '=', 'approved');
        })
        ->whereExists(function($query4) use($category_id)
        {
             $query4->select(DB::raw(1))
               ->from('jobcategories')
               ->where('provider_id', '!=', 0)
               ->where('category_id', '=',$category_id);
        })
    ->get();

Idea behind that is to pick all valid users. As you can see at the begining of query I wrote first condition for users. The problem is that users with status = '2' will never have their thirds table status = 'approved'. Is there possibility to put if statment before $query3? Thanks in advance!

Upvotes: 2

Views: 1654

Answers (1)

jedrzej.kurylo
jedrzej.kurylo

Reputation: 40909

Try the query below

$users = User::whereIn('users.status', array(2, 3))
->leftJoin('firsts f', 'f.user_id', '=', 'users.id')
->leftJoin('seconds s', 's.user_id', '=', 'users.id')
->leftJoin('thirds t', 't.user_id', '=', 'users.id')
->where(function($query) {
  $query->where('users.status', 2);
  $query->where('f.status', 'approved');
  $query->where('s.status', 'approved');
})
->orWhere(function($query) {
  $query->where('users.status', 3);
  $query->where('f.status', 'approved');
  $query->where('s.status', 'approved');
  $query->where('t.status', 'approved');
});

I'm not sure what to do with the last part of your query, as it has no relation to any of users, firsts, seconds, thirds:

->whereExists(function($query4) use($category_id)
  {
     $query4->select(DB::raw(1))
       ->from('jobcategories')
       ->where('provider_id', '!=', 0)
       ->where('category_id', '=',$category_id);
  })

Upvotes: 1

Related Questions