I run this query in my mysql program,
SELECT t1.t1_name, t1.created, COUNT(t2.t1_id) AS users,
t4.t4_col3
FROM t1
LEFT JOIN t2
ON t1.t1_id = t2.t1_id AND t2.deleted_ind = 0
LEFT JOIN t3
ON t1.t1_id = t3.t1_id AND t3.active_ind = 1
INNER JOIN t4
ON t3.t4_id = t4.t4_id
WHERE t1.t1_id != 0
AND t1.deleted_ind = 0
GROUP BY t2.t1_id
and it gives me the right results, now I need this with db class and have something like this
$query = DB::select('t1.t1_id',
't1.t1_name',
DB::expr('DATE(t1.created) AS created'),
't4.t4_col3',
DB::expr('COUNT(t2.t1_id) AS users')
)
->from('t1')
->join('t3', 'left')
->on('t1.t1_id', '=', 't3.t1_id AND t3.active_ind = 1') // error here
->join('t4', 'inner')
->on('t4.t4_id', '=', 't3.t4_id')
->join('t2', 'inner')
->on('t1.t1_id', '=', 't2.t1_id AND t2.deleted_ind = 0')) // error here
->where('t1.deleted_ind', '=', 0)
->and_where('t1.deleted_ind', '=', 0)
->and_where('t1.t1_id', '!=', 0)
->group_by('t2.t1_id');
But it gives me errors, I tried with "and_where" instead putting "AND ..." in the join and in certain situations I get wrong results.
Is there a way to make the join - on statement work with two conditions with the query builder?
Donate
