Level 63
Why don't you simply try this ?
$users = User::with('invoices')->get();
Then you can display each user once and his invoices list.
Oct 3, 2022
9
Level 2
Get the most recent, distinct record for a User
I have a User model which has many Invoices.
class User extends Authenticatable
{
public function invoices()
{
return $this->hasMany(Invoice::class);
}
}
As part of the dashboard, I want to paginate a list of invoices but only show each user once e.g. If a user has multiple invoices, they should only appear on the list once. In addition, I want to sort the overall pagination by the updated_at field on Invoices so that I can have a column on the resulting table showing the last invoice updated at date.
These are my attempts so far:
//Error: column "invoices.id" must appear in the GROUP BY clause or be used in an aggregate function
invoices()->groupBy('user_id')->latest('updated_at')->paginate(25);
//Returns multiple instances of the same user
invoices()->whereRaw('user_id IN (select distinct user_id FROM invoices)')->latest('updated_at')->paginate(25);
//Error: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
invoices()->distinct('user_id')->latest('updated_at')->paginate(25);
Most examples I found are sorted by the ID of invoices, which is not a good solution here as the invoice's highest (max) ID may not necessarily be the last updated invoice for that user.
Level 2
@vincent15000 A User could have numerous invoices so I'm trying to avoid the memory cost of 'with' when I can hand off the distinct operation to the database. I would also then need to sort the returned invoices by date, which again places the performance impact on PHP.
1 like
Level 122
have you considered hasOneOfMany ?
https://laravel.com/docs/9.x/eloquent-relationships#has-one-of-many
1 like
Level 2
@Snapey Thank you for reminding me of that! However, it would be perfect if I was paginating over all users but not every user will have an invoice(s), so I'd be pulling all users to maybe only need a handful.
Whatever method I use, I assume it has to be on the Invoice model as anything involving the User model would have unnessesary records.
1 like
Level 122
@colinlongworth You could use whereHas in conjunction.
Otherwise, consider joins.
1 like
Level 2
@Snapey Bingo! I got it, thank you!
public function latestInvoice()
{
return $this->hasOne(Invoice::class)->latestOfMany('updated_at');
}
->users()->has('invoices')->with('latestInvoice')->paginate(25)
1 like
Level 63
@Snapey Very interesting, I didn't know this method.
Level 51
Invoice::whereIn('id', function($q) {
$q->select(DB::raw('MAX(id)'))->from('invoices')->groupBy('user_id');
})
->with('user')
->orderBy('updated_at', 'DESC')
->get();
1 like
Level 2
@MohamedTammam This is very close, but in my tests, it returns the invoice with the highest ID, which is not necessarily the last updated invoice.
1 like
Please or to participate in this conversation.