Laravel SQL query returning wrong results

Question

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

Correct Results

Laravel Returned Results

Paglian · Accepted Answer · 2017-09-06 18:28:48Z

1

Double check how you are binding the values. You might be binding the values different than how Laravel does.

This link could be useful as well: Different results using same query with DB::raw and Eloquent

answered Sep 6, 2017 at 18:28

Paglian

6,8723 gold badges30 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel SQL query returning wrong results

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related