I have this SQL Query with Laravel Query builder
$this->candidate->select('candidates.*', 'candidates.id as candidate_id', 'mj.interviewScheduledDate')
->join('candidates_positions', 'candidates_positions.candidate_id', '=', 'candidates.id')
->leftJoin(DB::raw("(SELECT mat1.* FROM matches AS mat1
JOIN (SELECT candidate_id, MAX(id) AS id FROM matches GROUP BY candidate_id)
AS mat2 ON mat1.candidate_id = mat2.candidate_id AND mat1.id = mat2.id)
AS mj"), function ($join) {
$join->on("candidates.id", "=", "mj.candidate_id");
})
->where(function ($query) {
$query->where("mj.stage", "<", "4")
->whereNull('mj.stage', "or");
})
->groupBy('candidates.id')
->paginate(Config::get('match.pagination'));
This returns wrong results, where the Generated query from same Query builder returns correct results. $candidate->toSql()
returns below query. even tried removing group by statements. It didn't help
SELECT
`candidates`.*,
`candidates`.`id` AS `candidate_id`,
`mj`.`interviewScheduledDate`
FROM `candidates`
INNER JOIN `candidates_positions` ON `candidates_positions`.`candidate_id` = `candidates`.`id`
LEFT JOIN (SELECT mat1.*
FROM matches AS mat1
JOIN (SELECT
candidate_id,
MAX(id) AS id
FROM matches
GROUP BY candidate_id)
AS mat2 ON mat1.candidate_id = mat2.candidate_id AND mat1.id = mat2.id)
AS mj ON `candidates`.`id` = `mj`.`candidate_id`
WHERE (`mj`.`stage` < ? OR `mj`.`stage` IS NULL)
GROUP BY `candidates`.`id`
LIMIT 10 OFFSET 0
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire