May 30, 2024

17

OP

Level 1

Uniq distinct top tickets

I have two models: Ticket and Player. The relationship between them is one-to-many: one player can have multiple tickets, and each ticket belongs to one player. I need help retrieving the top 10 tickets ordered by their score column, with the following constraints: Each player can appear only once in the top 10. If a player has multiple tickets in the top 10, only his highest-scoring ticket should be included. If multiple tickets have the same score, the ticket with the smaller ID should be prioritized. Could you guide me on how to achieve this? The distinct method do not work here.

Level 63

You need to group the records by player_id.

$tickets = Ticket::
    select('id', 'score', 'player_id')
    ->groupBy('player_id')
    ->orderByDesc('score')
    ->take(10)
    ->get();

OP

Level 1

@vincent15000 This return error in SQL : SELECT list is not in GROUP BY clause and contains nonaggregated column .

1 like

Level 63

@niiwill I have tested this code and it works fine.

I have created 2 tables with these fields :

players : id, name
ticket : id, score, player_id

If it doesn't work for you, it means that you have other columns in your query.

Level 63

@niiwill Have you solved your problem ?

Level 50

@vincent15000 The error message is valid.

Your suggestion will work on mysql/maria only if ONLY_FULL_GROUP_BY is disabled. On postgres it won't work at all; you have to use DISTINCT ON instead.

2 likes

OP

Level 1

@vincent15000 No, I can not group by and get ticket id, if multiple tickets have the same player_id, so if I have ticket id 1 and 2, and the same player_id, which ticket to return?

1 like

Level 63

@JussiMannisto False => I have ONLY_FULL_GROUP_BY enabled and it works.

But the difference with @niiwill is that he has other fields that need to be grouped by. If he doesn't tell us which fields, we can't help him.

Level 63

@niiwill Of course you can ... you have said that one player can't appear twice in the list, so necessarily there will be only one player in the list and for the ticket, it's the ticket with the highest score.

Level 50

@vincent15000 You're selecting the id and score columns, but you're only grouping by player_id. If you're using mysql and you have have ONLY_FULL_GROUP_BY mode enabled, that will throw an error. If you don't get an error, you must have it turned off either globally or in the connection. Or you're using an ancient version of mysql (< 5.7).

Are you using something other than mysql/mariadb?

The basics of how GROUP BY is handled by mysql is explained in the link below. It also explains why your suggestion won't work - unless ONLY_FULL_GROUP_BY is disabled.

https://dev.mysql.com/doc/refman/8.4/en/group-by-handling.html

1 like

Level 63

@JussiMannisto Exact ... I have tested with SQLite.

@niiwill But the solution is to group by all other fields retrieved from the players table.

Level 50

@vincent15000 That's not a solution because that's not how GROUP BY works. You'd get every combination of ticket id and player id, not one score per player.

@niiwill You can get the highest scores per player by selecting only the player_id and max(score), and grouping by player_id. You can then join that with the player table to get player details if you want. I made this fiddle to demonstrate. It doesn't sort by or return ticket id, though.

You could also turn off ONLY_FULL_GROUP_BY and do a GROUP BY player_id. Or you could do this through a nested query.

While it's possible to do all this with Eloquent, it might be cleaner to just do it in raw SQL.

1 like

Level 63

@JussiMannisto You are probably right, but the fact is that I have tested the code I have suggested and it works fine.

Level 50

@vincent15000 Your original suggestion would work on sqlite because it has different behavior. It would also work here if ONLY_FULL_GROUP_BY were disabled. But the suggestion about grouping by all selected fields won't work on either.

1 like

Level 63

@JussiMannisto Have you tested it ?

Level 50

@vincent15000 Tested what?

1 like

Level 63

@JussiMannisto The code ?

Level 50

@vincent15000 I don't know what you're talking about.

Your original suggestion won't work on mysql, which @niiwill is using, unless they disable ONLY_FULL_GROUP_BY. This has already been demonstrated by the error they got.

I don't need to test grouping by all selected fields. I already know it gives the wrong results because I know the basics of SQL.

1 like

Please or to participate in this conversation.