在getResult上强制瀑布保湿

Question

I have an entity that is called 'Video', and attached to it, are other entities associated by ManyToOne and ManyToMany.

My problem, is that the amount of queries being executed is getting on the wrong side of uncomfortable with regular traffic. For instance, I have a variable amount of Queries being executed on the front page, and was getting to about 49 queries.

The extreme majority of the queries being executed were because the sub-entities to my Video entity are not being initialized automatically when called. When Dumped, I see a private property "is_initialized" set to 0. When called in my template, as they tend to be reported, Doctrine then executes the query to get that data.

I understand why Doctrine does this, if that data isn't being used, why get it in the first place? In the majority of cases, this behaviour is fine.

But in my case, I have a few methods in my VideoRepository class for fetching specific sets of videos. So I am using DQL here. In response, I decided to set the Result Cache to Apc right in the DQL.

Here's a relatively harmless example from the repository

    /**
     * Get Videos based on recently submitted
     *
     * @param null $limit
     * @param int $page
     * @return array
     */
    public function latest($limit = null, $page = 1)
    {
        $hash = md5(sprintf("%s_%s", $limit, $page));

        $query = $this->getEntityManager()->createQueryBuilder();
        $query->select('v')->from($this->getEntityName(), 'v')
            ->where('v.published = :published')
            ->orderBy('v.datePublished', 'DESC')
            ->setParameter('published', true);

        if($limit) {
            $query->setFirstResult((int)(($page-1) * $limit));
            $query->setMaxResults($limit);
        }

        $resultQuery = $query->getQuery();
        $resultQuery->setResultCacheDriver(new ApcCache())->useResultCache(true, 300, sprintf('results_latest.%s', $hash));

        return $resultQuery->getResult();
    }

So on the front page (where the 49 queries are being executed, more to come once production data starts hitting) there are 10 videos being requested from this method.

Inside of each video, is an associated "Product" entity (ManyToMany). In each video's thumbnail display, is a little Product icon, so naturally I need to access the $product property containing the Product Entity from the Video entity.

At this point, it's executing 10 more queries, because Doctrine never bothered to fetch the Product data for each video, and so it creates a query for each icon I ask for, which beings us up to 11 (minimum, ManyToMany means each Video can be associated with more than one product). There are 3 more strips of Videos queried for on this page for differing lists, using different methods in the VideoRepository

The problem here, is that while the Caching system works to store the result data of each main query for 5 minutes, it's also stashing that the Product and other Embedded Entities have not been acquired.

So with simple math, this means using the cache system has only saved me 4 queries, bringing my Doctrine query count to 45.

Is there a simple way to tell Doctrine to sub-query and auto-hydrate for ALL of the sub-entities when using getResult?

Answer 1

Yes, change your query to select more entities and doctrine will need fewer lazy loads.

$query->select('v', 'sub', 'others')
        ->from($this->getEntityName(), 'v')
        ->leftJoin('v.subtitles','sub')
        ->leftJoin('v.others','others')
        ->where('v.published = :published')
        ->orderBy('v.datePublished', 'DESC')
        ->setParameter('published', true);

This will return more data and doctrine will not have successive queries every time you need a subtitle or some other entity. As a warning, this will return more data so make sure this method is acceptable.