Doctrine custom DQL or Loop for performance in Symfony2

Question

A Company object can have many Site Objects. For a given company I'd like to know the primary Site. (isPrimary is an attribute of Site).

I wrote a function in the Company class called getPrimarySite() and implemented it like this.

public function getPrimarySiteForCompany()
    {
        foreach($this->getSite() as $site)
        {
            if($site->isPrimary())
            {
                return $site;
            }
        }

        return false;
    }

Is this OK or is it better to write a custom repository function in CompanyRepository where I get the primary Site with DQL?

It's probably fine just the way it is. You can always use the profiler to determine if it's slowing down things. I used to spend a great deal of effort making custom queries in order to minimize lazy loading. But for my models, simple bench marks showed very little gain for the effort. — Cerad, Jun 05 '13 at 21:14

score 1 · Answer 1 · answered Jun 05 '13 at 23:28

If a company can only have one primary site, the natural solution would be to add a new property Company::$primarySite to the Company entity. This would be a [One|Many]ToOne relation (depending on whether or not one site can belong to more than one company). Lookups will be fast, and your data layer then enforces the logic that a company can have only one primary site. That makes more sense than having an isPrimary flag on the site, and then having to make sure that two sites belonging to the same company don't have that flag set at the same time.

score 1 · Answer 2 · edited May 23 '17 at 12:17

While it wouldn't be hard to write a custom repository function to join the data and prevent lazy loading, this is a good example of premature optimization.

Leave it as is for now and once everything works in production, you can go back to dev and use the profiler to see what could be improved performance-wise. Additionally, if you only expect a few Site objects, then it probably doesn't matter all that much, if at all.

Doctrine custom DQL or Loop for performance in Symfony2

2 Answers2