2

My current query is 

select * from `users` where `group_id` = 1 and ((select count(*) from `works` inner join `user_work` on `works`.`id` = `user_work`.`work_id` where `user_work`.`user_id` = `users`.`id` and `work_duration_id` >= 3) >= 1)

For this query I have written following eloquent method in laravel 4.2 and it is working fine

$u = User::where('group_id', '=', 1);    
$u = $u->where(function($m) use($minWorkDuration,$searchCriteria){
     $m = $m->whereHas('work', function($q) use($minWorkDuration){
          $q->where('work_duration_id', '>=', $minWorkDuration );
     });
});

But I want to change in my query little bit and this is 

select * from `users` where `group_id` = 1 and ((select count(*) from `works` inner join `user_work` on `works`.`id` = `user_work`.`work_id` where `user_work`.`user_id` = `users`.`id` and (SELECT SUM(`work_duration_id`) from `works` group by `user_id`) >= 3) >= 1)

If I want to write this query then what will be in eloquent method. If anybody is there please suggest me how to write this query in eloquent method in laravel 4.2. Thanks in advance.

Dixit Sourav
  • 350
  • 1
  • 3
  • 10
  • 1
    replace inner line with $q->where(DB::raw("SELECT SUM(`work_duration_id`) from `works` group by `user_id`"), '>=', $minWorkDuration ); – kamlesh.bar May 26 '15 at 13:08
  • Yes I have tried in this way but it's giving error. I need the query like `and (SELECT SUM(`work_duration_id`) from `works` group by `user_id`) >= 3)` After replacing as you told it's coming like this way that's why error is showing `and SELECT SUM(`work_duration_id`) from `works` group by `user_id` >= 3)` – Dixit Sourav May 26 '15 at 13:19
  • query building ain't for complex queries. – itachi May 26 '15 at 20:35
  • Thanks @kamlesh.bar I have done it in your way with a little change `$q->where(DB::raw("(SELECT SUM(work_duration_id) from works group by user_id)"), '>=', $minWorkDuration );` – Dixit Sourav May 27 '15 at 07:28
  • 1
    @user3676972 yes you are right i forgot () around select statement. – kamlesh.bar May 27 '15 at 07:48

0 Answers0