Laravel sub join select query

Question

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?

Kyaw Kyaw Soe · Accepted Answer · 2018-11-08 08:19:04Z

3

This code might work,

DB::table('table1 t1')
->select(['id', 'created_at', 't2.status'])
->leftJoin('table2 t2', function ($join) {
    $subQuery = DB::table('table2')
                    ->selectRaw('MAX(id)')
                    ->whereRaw('table2.submission_id = submission_id');
    $join->on('t2.id', DB::raw('(' . $subQuery->toSql() . ')'))->whereRaw('t1.id = t2.submission_id');
});

Or this one

DB::table('table1 t1')
->select(['id', 'created_at', 't2.status'])
->leftJoin('table2 t2', function ($join) {
    $subQuery = DB::raw('(select MAX(id) from table2 where table2.submission_id = submission_id)');
    $join->on('t2.id', $subQuery)->whereRaw('t1.id = t2.submission_id');
});

Note: you can use ->toSql() method to print out query string before you execute it, to make sure you really get query you want.

edited Nov 8, 2018 at 8:19

answered Nov 8, 2018 at 8:07

Kyaw Kyaw Soe

3,4001 gold badge16 silver badges25 bronze badges

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

Comments

Jonas Staudenmeir · Accepted Answer · 2018-11-07 21:28:50Z

2

You have to use whereColumn() instead of where():

->whereColumn('t2.submission_id', '=', 'bespoke_submission_id')

answered Nov 7, 2018 at 21:28

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

Comments

Chris · Accepted Answer · 2018-11-08 10:17:02Z

1

Thanks all. My final solution was the following -

// write begin making our query
$query = Model::query();

// filter by status
$args = ['status' => 'approved'];

// create our sub query
$subQuery = DB::raw(
    '(SELECT MAX(id) from table2 ' .
    'where t2.submission_id = submission_id)'
);

// join to retrieve our revisions table
$query->leftJoin('table2 as t2', 't2.id', '=', $subQuery);
$query->whereColumn('table1.id', '=', 't2.submission_id');
$query->where('t2.status', '=', $args['status']);

$results = $table->get();

This then creates the desired result so thanks @Kyaw Kyaw Soe for the pointer on implementing a bit of DB::raw() and @Jonas Staudenmeir for the tip on $query->whereColumn()

answered Nov 8, 2018 at 10:17

Chris

2,0461 gold badge21 silver badges45 bronze badges

Collectives™ on Stack Overflow

Laravel sub join select query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related