How to get data of 2 tables with groupBy and orderBy id & get last inserted data

Hi there!

I'm trying to get the data from 2 different tables by using join method. I have done creating a Eloquent procedure to achieve that which is working perfectly with orderBy() method as well.

Now I just wanted to group the data based on a column with the same Eloquent methods is not returning me the latest/last inserted data.

My query for your reference below,

$def_reminders = Invoice::from('invoices AS inv')
->join('reminders_calendar AS remcal', 'remcal.invoice_id', '=', 'inv.id')
->where([
    'inv.user_id' => 1,
    'inv.is_deleted' => '0',
])
->where('inv.reminder_status', '!=', 'Enabled')
->whereDate('remcal.date_when', '2024-01-03')
->whereIn('remcal.reminder_type', ['InProgress', 'Waiting'])
// ->whereRaw('remcal.id = (select max(`id`) from reminders_calendar)')
->selectRaw('MAX(remcal.id) as remcal_id, remcal.invoice_id AS remcal_invoice_id, remcal.*, inv.*')
->groupBy('remcal.invoice_id')
->orderBy('remcal.id', 'desc')
->get();

Whenever I commented the groupBy('remcal.invoice_id') method returns me all data with order of latest/ last data to previous data correctly.

I'm bit confused to get the data from 2 tables when with the groupBy() and orderBy() method in the join query.

hupp

2 years ago

Best Answer

Level 11

@manomahe Here you are using groupBy and OrderBy combine which not work proper in your case, try to do it with sub query to achieve result properly.

$sub = DB::table('reminders_calendar')
    ->select('invoice_id', DB::raw('MAX(id) as max_id'))
    ->groupBy('invoice_id');

$def_reminders = DB::table('invoices AS inv')
    ->joinSub($sub, 'max_remcal', function ($join) {
        $join->on('inv.id', '=', 'max_remcal.invoice_id');
    })
    ->join('reminders_calendar AS remcal', function ($join) {
        $join->on('inv.id', '=', 'remcal.invoice_id')
             ->on('remcal.id', '=', 'max_remcal.max_id');
    })
    ->where([
        'inv.user_id' => 1,
        'inv.is_deleted' => '0',
    ])
    ->where('inv.reminder_status', '!=', 'Enabled')
    ->whereDate('remcal.date_when', '2024-01-03')
    ->whereIn('remcal.reminder_type', ['InProgress', 'Waiting'])
    ->select('remcal.*', 'inv.*')
    ->orderBy('remcal.id', 'desc')
    ->get();

Try this version it should be worked. Let me know your thoughts on this.

1 like

ManoMahe

2 years ago

Level 1

@hupp Thanks mate, It works... may I get an explanation about your approach from you would help me to understand clearly

hupp

2 years ago

Level 11

@ManoMahe When you use groupBy, it groups the rows that have the same values in specified columns into aggregated data. In your case, it’s grouping by remcal.invoice_id. And, when you use orderBy('remcal.id', 'desc'), it’s trying to order by the id of remcal in descending order. But because of the groupBy, it might not be the latest id that gets selected in the group, hence you’re not getting the latest inserted data.

ManoMahe

2 years ago

Level 1

@hupp Oh got it. Thanks for the clear explanation :-)

Please or to participate in this conversation.