How to write a QueryBuilder query correctly?

Question

Doctrine ORM

Greetings! I have never worked with Doctrine before, so I am asking for help!

There is an entity House . It has many one-to-many related entities -

• house inspection
  
• house evaluation
  
• HousePriceHistory
  
• HouseStatusHistory
  
• HouseComment
  

All related entities for House are pulled as EXTRA_LAZY

There are two problems:
1) It is necessary to pull into all related entities (the main House entity is not necessary) using QueryBuilder (important, because later this query will be modified to filter and paginate results, and custom write a paginator - not ok) and sort by the createdAt field (all entities have it).

2) For each entity type, you need to add a field(?) type in the query . those. if we pull the HouseInspection collection, then in each entity you need additional. field denoting this entity ( eg - Inspection, Evaluation, Price, etc.). This is necessary in order to modify this query later, and only pull entities of a certain type. On account of this point, I'm not sure if I'm thinking in the right direction. Maybe this can be done in a simpler way. Although, if you solve the first problem, then immediately the solution to the second will be found.

What is/tried:

1)

Joins

$this->getEntityManager()->createQueryBuilder()
            ->select('h') 
            ->from(House::class, 'h')
            ->leftJoin(HouseHistoryRecord::class, 'sh', Join::LEFT_JOIN, 'sh.house = h.id')->addSelect('sh as statusHistory')
            ->leftJoin(HousePriceHistory::class, 'ph', Join::WITH, 'ph.house = h.id')->addSelect('ph as priceHistory')
            ->leftJoin(HouseEvaluation::class, 'eval', Join::WITH, 'eval.house = h.id')->addSelect('asmt as houseEvaluations')
            ->leftJoin(HouseInspection::class, 'insp', Join::WITH, 'insp.house = h.id')->addSelect('insp as houseInspections')
            ->leftJoin(HouseComments::class, 'cmts', Join::WITH, 'cmts.house = h.id')->addSelect('cmts as houseComments')
            ->where('h.id = :houseId')
            ->setParameter('houseId', $house->getId());

HouseComments

Not OK. Pulls only House and loads related entities into itself. Actually pagination will not work.

2)

Frontal variant

$sh = $this->getEntityManager()->createQueryBuilder()
            ->select('sh')
            ->from(HouseHistoryRecord::class, 'sh')
            ->where('sh.house = :houseId')
            ->setParameter('houseId', $house->getId());

        $ph = $this->getEntityManager()->createQueryBuilder()
            ->select('ph')
            ->from(HousePriceHistory::class, 'ph')
            ->where('ph.house = :houseId')
            ->setParameter('houseId', $house->getId());

        $eval = $this->getEntityManager()->createQueryBuilder()
            ->select('eval')
            ->from(HouseEvaluation::class, 'eval')
            ->where('eval.house = :houseId')
            ->setParameter('houseId', $house->getId());

        $insp = $this->getEntityManager()->createQueryBuilder()
            ->select('insp')
            ->from(HouseInspection::class, 'insp')
            ->where('insp.house = :houseId')
           ->setParameter('houseId', $house->getId());

        $cmts = $this->getEntityManager()->createQueryBuilder()
            ->select('cmts')
            ->from(HouseComments::class, 'cmts')
            ->where('cmts.house = :houseID')
            ->setParameter('houseId', $house->getId());

Almost ok. - Everything pulls out as it should, but naturally in different collections. Maybe you can somehow combine all these requests into one? And then already sort by date?

Pure SQL (+ ResultSetMapping) I do not consider, because it will be harder to customize later for pagination/filtering. I prefer DQL.

I call upon the guru of doctrine. I feel that I am spinning, somewhere nearby, but the knowledge of the doctrine is not enough.

Thanks in advance!

Answer 1

Pull out separate requests. Otherwise, intersections from each connection will fall into the sample, which can return a lot of data. Yes, and pagination will be easier to do this way.