I would like to get in laravel 5.2 the same result as this from mysql:
select a3.user_id,sum(a3.task_hour)
from activities a3
where id not in
( select a1.id from activities a1 inner join
(SELECT * FROM `activities` where validated = 1) a2
on a1.user_id = a2.user_id and a1.project_id = a2.project_id where a1.validated = 0)
group by a3.user_id
So in order to get clear view on the code, I try to get the following first:
(SELECT * FROM `activities` where validated = 1) a2
with this
$activity_onlyFromOTL = DB::table('activities AS a2');
$activity_onlyFromOTL->select('a2.id','a2.year','a2.month','a2.user_id','a2.project_id','a2.task_hour')->where('a2.from_otl','=','1');
For the second part, I have this:
( select a1.id from activities a1 inner join
(SELECT * FROM `activities` where validated = 1) a2
on a1.user_id = a2.user_id and a1.project_id = a2.project_id where a1.validated = 0)
For clarity, I tried to use the first variable into this second statement so I can read it better:
$activity_whereOTLandNonOTL = DB::table('activities AS a1');
$activity_whereOTLandNonOTL->select('a1.id');
$activity_whereOTLandNonOTL->join($activity_onlyFromOTL, function ($join) {
$join->on('a1.user_id', '=', 'a2.user_id')->on('a1.project_id', '=', 'a2.project_id');
});
$activity_whereOTLandNonOTL->where('a1.validated', '=' , '0');
But when I try to get the toSql for that one, I get the error:
ErrorException in Grammar.php line 39:
Object of class Illuminate\Database\Query\Builder could not be converted to string
How can I work this way so that it is a much more clear code?