CODEWITHSUNDEEP
phpsymfonydoctrine
Mankeeey
Mankeeey

Reputation: 31

Calculating field in entity, order by doctrine

I have a user entity which has a calculated field totalPoints. Now I want to order my users in a highscore by the totalpoints. I already have a working solution but it is really slow for only 150 records at this time. I'm worried if I have more records sooner or later.

In my user entity I have this function to calculate the points

public function getTotalPoints()
{
        $totalPoints = 0;
        $totalPoints += $this->totalLevel;
        $totalPoints += $this->getWeeksJoined() * $this->staticsRepository->findOnByName("Points Weeks Joined")->getValue();
        $totalPoints += $this->forumBumps * $this->staticsRepository->findOnByName("Points Forum Bumps")->getValue();
        $totalPoints += $this->lootSplit * $this->staticsRepository->findOnByName("Points Loot Split")->getValue();
        $totalPoints += $this->isVeteran * $this->staticsRepository->findOnByName("Points Veteran Rank")->getValue();
        $totalPoints += $this->getSotwPlaces(1)->count() * $this->staticsRepository->findOnByName("Points SOTW Wins")->getValue();
        $totalPoints += $this->getSotwPlaces(2)->count() * $this->staticsRepository->findOnByName("Points SOTW Second Place")->getValue();
        $totalPoints += $this->getSotwPlaces(3)->count() * $this->staticsRepository->findOnByName("Points SOTW 3rd Place")->getValue();
        $totalPoints += $this->osrsUserRefs->count() * $this->staticsRepository->findOnByName("Points Referals")->getValue();
        $totalPoints += $this->eventsAttended->count() * $this->staticsRepository->findOnByName("Points Events Attended")->getValue();
        $totalPoints += $this->eventsHosted->count() * $this->staticsRepository->findOnByName("Points Events Hosted")->getValue();

        if($this->discordUser != null)
            $totalPoints += $this->discordUser->getLevel() * $this->staticsRepository->findOnByName("Points Discord Level")->getValue();

        return $totalPoints;
    }

Now to get them ordered by totalpoints have to usort the array of users and this is slow.

/**
* @Route("/", name="highscores_index")
*/
    public function index(OsrsUserRepository $osrsUserRepository)
    {
        $osrsUsers = $osrsUserRepository->findAllHighscores();

        usort($osrsUsers, function($a, $b)
        {
            return $b->getTotalPoints() > $a->getTotalPoints();
        });

        return $this->render('highscores/index.html.twig', [
            'users' => $osrsUsers,
        ]);
    }

Is there a way to do this inside doctrine query builder? I have not found any documentations on this kind of sorting.

Upvotes: 0

Views: 371

Answers (1)

Jakumi
Jakumi

Reputation: 8374

The problem in your case is essentially this:

You do the complete calculation each time you call getTotalPoints, ... and every call includes 10 calls to staticsRepository (fuckup 1: why the f*** do you have a repository in an entity? - it's a code smell!) and counts sub-entities (+1 query for each collection).

I really really hope, that your staticsRepository isn't user-dependent because in that case the queries aren't even cached. for your counts, they probably aren't.

So what you essentially have is the n+1 problem (see no. 5).

You probably can write a specialized query that calculates the totalScores for all your users in the database, but to be honest, I'm not here to teach you DQL or SQL. However, do the research, or you're wasting so much potential for improvement...

Now, there are some options (incomplete list):

  1. do it all in the database, every time, all the time. In this case, you add a function to your UserRepository, that executes the query for either one User or it returns all users including their total scores. Creating a database view including that query as field could make this even transparent for your code. but it can also be done in querybuilder, that is DQL, ... or SQL. Maybe you'll need doctrine-extensions.

    this solution is reasonably to extremely performant, and it always produces sufficiently recent results

  2. implement the logic in php, store the result in database (adding a new db field for totalScore), recalculate at convenient points in time. This solution is very performant if done correctly, but might produce slightly outdated results. (convenient point in time is like ... daily at midnight or once an hour, or every 5 minutes - if your server can stem it, add some means to figure out if a score has changed: timestamp everything that changes the score, store the timestamp of the score ...)

    this solution is somewhat performant, the update (that probably will be done via cron jobs) and it always produces correct and recent results. - recent == your interval. essentially it streamlines your current approach... combine with 1 for very good results.

  3. use aggregate fields, which essentially updates the score in the database - almost like in 2. but - whenever it changes. Have a look at aggregate fields and figure out how to adapt this to your use case. Please note the race conditions and how to avoid them. Depending on your concrete approach, this might reduce performance for many actions, but will increase performance greatly when requesting the high scores...

    it's VERY performant on the high scores, and it always produces sufficiently recent results BUT you have to handle race conditions and all point updates, which can be extremely annoying and bug-prone!

My choice would be 1. This requires the calculation to be expressible in sql or dql, which probably can be done.

Upvotes: 1

Related Questions