Geek asks and answers

Doom of Symfony query line number

I store data for lap times in a database, the data consists of distance, time, average speed and maximum speed.

I'm trying to display a leaderboard that shows the top ten people in any query that you asked (who is the most in general, the best average term, etc.). However, below the top ten I want to show the user by registering a position in the leaderboard. To do this, I try to run the same query, ordering my results, and adding ROW NUMBER to get the position.

I use symfony 1.4 with Doctrine ORM, and I can’t understand for life how to get line numbers in a query. I know that you can do this in SQL like this:

SELECT full_name, ROW_NUMBER() OVER(ORDER BY distance) AS row_number

But I cannot get it to work in Doctrine Symfony.

Does anyone have any ideas how I can do this? (or even another way around this)

Thanks in advance.

+3
source share
1 answer

Ok, my solution is:

I kind of found the answer to this question after several experiments and cooperation.

What I did was get rid of the selection and simply return an ordered list of results;

$results = self::getInstance()->createQuery('r')
    ->orderBy('r.distance')
    ->execute();

Then I moistened this result in the array and used array_search () to find the key of the result in the array (fortunately, I return the user data here and I know the user I'm looking for in the array)

$index = array_search($user->toArray(), $results->toArray());

Then I can return $ index + 1 to give me the user's position in the leaderboard.

There is probably a better way to do this part of the database, but I have not been able to learn how to do this for my whole life.

- , .

0

More articles:


All Articles