long hoangvan long hoangvan - 3 months ago 102
MySQL Question

Laravel query builder join with multiple subquery

I has try to convert query in mysql to laravel query builder but it's not working. My query in sql something like this :

SELECT *
FROM claim.t_meter a
JOIN
(
SELECT MAX(REPLACEMENT_COUNT) AS MAX_REPLACEMENT_COUNT, meter_no
FROM claim.t_meter
WHERE bukken_uid = 1. AND deleted = 0
GROUP BY meter_no
) b ON a.meter_no = b.meter_no AND a.REPLACEMENT_COUNT = b.MAX_REPLACEMENT_COUNT
LEFT JOIN
(
SELECT NAME, meter_no, INSPECTION_GROUP_UID
FROM claim.m_inspection_group c
JOIN claim.t_inspection_group_setting d ON c.uid = d.inspection_group_uid
) e ON a.meter_no = e.meter_no
GROUP BY a.meter_no
ORDER BY inspection_group_uid ASC


Now i'm using :

$query = ' SELECT * FROM t_meter a JOIN (';
$query .= ' SELECT MAX(REPLACEMENT_COUNT) AS MAX_REPLACEMENT_COUNT, meter_no';
$query .= ' FROM t_meter WHERE bukken_uid = '.$id.' AND deleted = 0 GROUP BY meter_no)';
$query .= ' b ON a.meter_no = b.meter_no AND a.REPLACEMENT_COUNT = b.MAX_REPLACEMENT_COUNT';
$query .= ' LEFT JOIN (SELECT NAME,meter_no,INSPECTION_GROUP_UID FROM m_inspection_group c';
$query .= ' JOIN t_inspection_group_setting d ON c.uid = d.inspection_group_uid';
$query .= ' ) e ON a.meter_no = e.meter_no GROUP BY a.meter_no ORDER BY inspection_group_uid ASC';
$arrInFo = DB::select($query);


It's not best way, please help!

Answer

My solution is :

$arrInFo = TMeter::SELECT(
                    't_meter.*',
                    'ORDER_BY_TERMINAL'
                )
            ->join(DB::raw('(SELECT MAX(REPLACEMENT_COUNT) AS MAX_REPLACEMENT_COUNT, METER_NO FROM `t_meter` WHERE BUKKEN_UID = '.$bukken_uid.' AND DELETED = '.Constants::IS_NOT_DEL.' GROUP BY METER_NO) b'), function($join)
            {
                $join->on('t_meter.METER_NO', '=', 'b.METER_NO')
                    ->on('t_meter.REPLACEMENT_COUNT', '=', 'b.MAX_REPLACEMENT_COUNT');
            })
            ->leftJoin(DB::raw('(SELECT NAME, METER_NO, ORDER_BY_TERMINAL, INSPECTION_GROUP_UID, d.UID as setting_uid FROM `m_inspection_group` c JOIN t_inspection_group_setting d on c.UID = d.INSPECTION_GROUP_UID AND c.UID = '.$id.') e'), function($join) {
                $join->on('t_meter.METER_NO', '=', 'e.METER_NO');
            })
            ->groupBy('METER_NO')
            ->get();