How to write sub query in Laravel eloquent

Question

Following is my MySQL query

SELECT id 
FROM member_measurement_records 
WHERE date in (SELECT MAX(date) from member_measurement_records GROUP BY type)

Which I want to write using laravel eloquent. Can you please help me. As I am new to this.

Answering this is entirely dependant on what you named your models. Would you share them in your question. — ADoorMarkedPirate
– ADoorMarkedPirate, Commented Oct 24, 2017 at 9:36

Suraj · Accepted Answer · 2017-10-24 10:21:39Z

2

Well, there are several ways to do it.

Using DB facade

$data = DB::table('member_measurement_records')->where('date', DB::raw("(select max(`date`) from member_measurement_records GROUP BY type)"))->get();

Using eloquent and raw query

$data = MemberMeasurementRecords::whereRaw('date = (select max(`date`) from member_measurement_records GROUP BY type)')->get();

Using Eloquent and DB facade

$data = MemberMeasurementRecords::find(DB::table('member_measurement_records')->max('date')->groupBy('type'));

answered Oct 24, 2017 at 10:21

Suraj

2,2972 gold badges21 silver badges25 bronze badges

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

Comments

Moritz Büttner · Accepted Answer · 2017-10-24 10:04:39Z

1

Haven't tested it, but you can try it:

(It is assumend your corresponding model is called MemberMeasurementRecords)

MemberMeasurementRecords::whereIn('date', function ($query) {
            $query->max('date')->orderBy('type');
        })->get('id');

answered Oct 24, 2017 at 9:51

Moritz Büttner

9041 gold badge9 silver badges23 bronze badges

using subquery here is great