0

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?

Richard
  • 703
  • 3
  • 11
  • 33
  • why don't you use the eloquent methods to query database ? – Sagar Chamling Jun 14 '17 at 02:41
  • You should take a look at [this answer](https://stackoverflow.com/a/18120728/4772618). – Yogesh Aug 16 '17 at 16:16
  • Convert your query builder to string by calling `->activity_onlyFromOTL->toSql()` and don't forget to add corresponding bindings to $activity_whereOTLandNonOTL. – Tadej Sep 24 '18 at 01:51

0 Answers0