F
F
future coder2020-12-17 12:23:43
symfony
future coder, 2020-12-17 12:23:43

How to reduce the number of queries when selecting products on a page in a One to One relationship?

There are two entities, Catalog and CatalogPrice, a one-to-one relationship. I make a selection with pagination like this:

/**
     * @Route("/{id}/{page}", name="catalog_by_category", requirements={"id"="\d+"}, defaults={"page"=1})
     */
    public function getByCat(EntityManagerInterface $em, $id, $page, Request $request)
    {
        $query = $em->getRepository(Catalog::class)->findByCategory($id);
        $currentCategory = $em->getRepository(Categories::class)->find($id);
        $limit = 50;

        $paginator = new Paginator($query, $fetchJoinCollection = false);
        $pageCount = ceil(count($paginator) / $limit);
        $pages = range(1, $pageCount);
        $catalog = $query
            ->setFirstResult($limit * ($page-1))
            ->setMaxResults($limit)
            ->getQuery()->getResult();

        dump($catalog);

        return $this->render('catalog/index.html.twig', [
            'catalog' => $catalog,
            'pages' => $pages,
            'category' => $currentCategory,
            'categoryId' => $id,
        ]);
    }

findByCategory() method:
public function findByCategory($id)
    {
        $query = $this->createQueryBuilder('product')
//            ->join('product.catalogPrice', 'catalogPrice')
            ->where('product.category = :id')
            ->setParameter('id', $id);

        return $query;
    }

Limit of products per page: 50. As a result, after the selection, 57 queries to the database are obtained. The following fragment from the logger is noteworthy:
5fdb2171bf0bc546585599.png
I.e. for each product on request in the database to get the price. I think it's too much). What is my mistake?
By the way, when selecting in this way, everything works fine, there are few requests, as it should be.
$catalog = $em->getRepository(Catalog::class)->findBy(['category' => $id], [], 50);

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question