Level 29
You can add group by to avoid duplicates for the event booking id
->groupBy('event_booking_id')
If you only want for example max 5 results you can add this to only take the 5 first results from the DB
->take(5)
Jan 23, 2024
5
Level 2
Order and limit query results
Hey guy's,
In the result set shown below my goal is to select distinct 'booking_event_id', using the row with the highest 'id' if there are duplicate 'event_booking_id'.
In this instance, the last three rows should not be returned.
{"event_booking_id":16,"id":16}
{"event_booking_id":17,"id":17}
{"event_booking_id":18,"id":18}
{"event_booking_id":19,"id":19}
{"event_booking_id":20,"id":20}
{"event_booking_id":21,"id":24}
{"event_booking_id":21,"id":23}
{"event_booking_id":21,"id":22}
{"event_booking_id":21,"id":21}
I have tried many variations on the code below, which produced the output above, but to no avail:
$query = EventBookingProposal::query()
->select('event_booking_id', 'id')
->orderBy('event_booking_id')
->orderByDesc('id');
Thanks in advance for any help you may be able to provide.
Cheers, Paul
Level 2
Hey @gych,
Thank you for your reply. Take limits the entire query so I only see the single result below:
->take(1)
{"event_booking_id":1,"id":1}
The groupBy seems to disregard my orderBy clause and returns the results below.
->groupBy('event_booking_id')
{"event_booking_id":16,"id":16}
{"event_booking_id":17,"id":17}
{"event_booking_id":18,"id":18}
{"event_booking_id":19,"id":19}
{"event_booking_id":20,"id":20}
{"event_booking_id":21,"id":21}
The last row returned should be:
{"event_booking_id":21,"id":24}
Level 29
@Cyburg Ok I see, can you try with this code?
$query = EventBookingProposal::query()
->select('event_booking_id', \DB::raw('MAX(id) as max_id'))
->groupBy('event_booking_id')
->orderBy('event_booking_id')
->orderByDesc('max_id');
This uses a raw query to select the rows with the highest id
And how many max results do you want to fetch?
2 likes
Level 2
@gych thank you very much, that works perfectly!
1 like
Level 8
@cyburg Hey you can use below query
$query = EventBookingProposal::select('event_booking_id', 'id') ->groupBy('event_booking_id') ->orderBy('event_booking_id') ->orderByDesc('id') ->get();
Please or to participate in this conversation.