CODEWITHSUNDEEP
phpsymfonydoctrine-orm
Technofrood
Technofrood

Reputation: 100

Correct way to handle loading doctrine entities with multiple associations

I'm currently building an eCommerce site using Symfony 3 that supports multiple languages, and have realised they way I've designed the Product entity will require joining multiple other entities on using DQL/the query builder to load up things like the translations, product reviews and discounts/special offers. but this means I am going to have a block of joins that are going to be the same in multiple repositories which seems wrong as that leads to having to hunt out all these blocks if we ever need to add or change a join to load in extra product data.

For example in my CartRepository's loadCart() function I have a DQL query like this:

SELECT c,i,p,pd,pt,ps FROM 
AppBundle:Cart c 
join c.items i 
join i.product p
left join p.productDiscount pd
join p.productTranslation pt
left join p.productSpecial ps
where c.id = :id

I will end up with something similar in the SectionRepository when I'm showing the list of products on that page, what is the correct way to deal with this? Is there some place I can centrally define the list of entities needed to be loaded for the joined entity (Product in this case) to be complete. I realise I could just use lazy loading, but that would lead to a large amount of queries being run on pages like the section page (a section showing 40 products would need to run 121 queries with the above example instead of 1 if I use a properly joined query).

Upvotes: 0

Views: 706

Answers (2)

Francesco Abeni
Francesco Abeni

Reputation: 4265

There is no single correct answer to your question.

But if I have to make a suggestion, I'd say to take a look at CQRS (http://martinfowler.com/bliki/CQRS.html) which basically means you have a separated read model.

To make this as simple as possibile, let's say that you build a separate "extended_product" table where all data are already joined and de-normalized. This table may be populated at regular intervals with a background task, or by a command that gets triggered each time you update a product or related entity.

When you need to read products data, you query this table instead of the original one. Of course, nothing prevents you from having many different extended table with your data arranged in a separate way.

In some way it's a concept very similar to database "views", except that:

  • it is faster, because you query an actual table
  • since you create that table via code, you are not limited to a single SQL query to process data (think filters, aggregations, and so on)

I am aware this is not exactly an "answer", but hopefully it may give you some good ideas on how to fix your problem.

Upvotes: 1

Richard
Richard

Reputation: 4119

One approach (this is just off the top of my head, someone may have a better approach). You could reasonably easily have a centralised querybuilder function/service that would do that. The querybuilder is very nice for programattically building queries. The key difference would be the root entity and the filtering entity.

E.g. something like this. Note of course these would not all be in the same place (they might be across a few services, repositories etc), it's just an example of an approach to consider.

public function getCartBaseQuery($cartId, $joinAlias = 'o') {

    $qb = $this->getEntityManager()->createQueryBuilder();

    $qb->select($joinAlias)
        ->from('AppBundle:Cart', 'c')
        ->join('c.items', $joinAlias)
        ->where($qb->expr()->eq('c.id', ':cartId'))
        ->setParameter('cartId', $cartId);

    return $qb;
}

public function addProductQueryToItem($qb, $alias) {

    /** @var QueryBuilder $query */
    $qb
        ->addSelect('p, pd, pt, ps')
        ->join($alias.'product', 'p')
        ->leftJoin('p.productDiscount', 'pd')
        ->join('p.productTranslation', 'pt')
        ->join('p.productSpecial', 'ps')
    ;

    return $qb;
}


public function loadCart($cartId) {

    $qbcart = $someServiceOrRepository->getCartBaseQuery($cartId);
    $qbcart = $someServiceOrRepository->addProductQueryToItem($qbcart);

    return $qbcart->getQuery()->getResult();
}

Like I said, just one possible approach, but hopefully it gives you some ideas and a start at solving the issue.

Note: If you religiously use the same join alias for the entity you attach your product data to you would not even have to specify it in the calls (but I would make it configurable myself).

Upvotes: 1

Related Questions