mercredi 7 novembre 2018

Laravel sub join

I'm trying to work out how to replicate the below query in Laravel. What I'm trying to do is get the very latest status from table2 / t2 and merge it with our primary record table1 / t1

select id, created_at, t2.status from table1 t1

left join table2 t2 ON 52.id = (
    SELECT MAX(id) from table2
    where table2.submission_id = submission_id
) AND t1.id = t2.submission_id

This query above works brilliantly but I can't work out how to replicate this in 'native' Laravel -

$query = Table1::query();
$query->join('table2 as t2', function (JoinClause $join) {
    $join->selectRaw('MAX(id)')
        ->where('t2.submission_id', '=', 'submission_id');
}, '=', 't2.id');

I've also tried joinSub -

$query->joinSub(function (Builder $query) {
                $query
                    ->selectRaw('MAX(id)')
                    ->from('table2 as t2')
                    ->where('t2.submission_id', '=', 'bespoke_submission_id');
            }, 't2', 't1.id', '=', 't2.id', 'left');

However I can't seem to get my desired outcome! Any ideas?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire