0

I have 3 entities in Symfony. Candidate, School, Game a candidate is assigned to one school and can start games. The games entity contains the score (of that) game. So far so good, here comes the tricky part:

I have to display to the candidates, what rank they have currently, as well as the top 10 players.

I know, I could use a native query for mysql and do something like proposed here: https://stackoverflow.com/a/3333697/2989952 But then, to get the rank of the candidate I'd have to load all entries from the db and loop throu them in PHP (or with Doctrines Criteria). Is there a better way to get the rank? I could also add a column point's or something in the candidate entity and update that after every game and then just sort by that column, still I'd have to loop throu all entries again in PHP.

Thanks for your help.

Community
  • 1
  • 1
wawa
  • 4,816
  • 3
  • 29
  • 52
  • 1
    How Game relates to Candidate? Shouldn't there be a game_candidate table where the score with N:M would be? Would be possible to order data by score and provide rank from loop inside php? – Keo Jan 28 '15 at 17:49
  • Candidate contains a `OneToMany` mapping for the Game Entity, Game contains a `ManyToOne` mapping. DB is handled by Symfony. I know, I can loop throu all entries and get the index, but wouldn't that cost a lot of resources, since PHP would perform this task in the memory and has to load all the entities first? Wouldn't it be better then to use a native query and get the rank directly from MySQL? – wawa Jan 29 '15 at 08:38

1 Answers1

0

For the second part 'How to rank top 10 players' i would use index from loop with this query.

$gameRepository->findBy([],['score'=>'desc'],10);

For the first part.This looks promising and can be transfered into DQL easily.

Community
  • 1
  • 1
Keo
  • 1,143
  • 8
  • 19
  • Problem here: There are several rounds (each week one) and a candidate can play multiple times per round (not just one game). To get all the games of the round I could just add a where clause as first param to find by with startTime and endTime, so no problem there, but then I'd still need to summarize all the `score` fields. – wawa Jan 29 '15 at 14:11