Doctrine single query for unrelated entities (for dashboard)

Question

I have around 60 entities, all referring (manyToOne or oneToOne) to a "uber entity" called "project".

I'm developing a dashboard, so once selected the project I have to get all "children" entities referred to the project.

So I have:

class Entity1{
...
/**
 * @ORM\OneToOne(targetEntity="Project")
 */
protected $project;
...
}

or

class Entity2{
...

/**
 * @ORM\ManyToOne(targetEntity="Project")
 */
protected $project;
...
}

Of course I could do:

    $entitiesToCheckStatusFor = ['Entity1', 'Entity2', ..., 'Entity60',  ];
    $entitiesStatus = [];
    foreach ($entitiesToCheckStatusFor as $entityToCheckStatusFor){
        $entitiesStatus[$entityToCheckStatusFor] = $em->getRepository('AppBundle:'.$entityToCheckStatusFor)->findByProject($project);
    }

But it means 60 queries. It's far from elegant.

I basically need a JOIN between unrelated entities so that I can make a single query.

I tried something like:

    $query = $em->createQuery('SELECT ai, pn, pb, pd p FROM AppBundle:Project p '
        . 'LEFT OUTER JOIN AppBundle:ProjectNotification pn WITH p.id = pn.project '
        . 'LEFT OUTER JOIN AppBundle:ProjectDetail pd WITH p.id = pd.project '
        . 'LEFT OUTER JOIN AppBundle:ProjectBenefit pb WITH p.id = pb.project '
        . 'LEFT OUTER JOIN AppBundle:ActionItem ai WITH p.id = ai.project '
        . 'WHERE p.id = :projectid'
    )->setParameter('projectid', $project->getId());
    $mixedStuff = $query->getResult();

but it returns a lot of nulls when there are no entities:

array:20 [▼
  0 => Project {#8285 ▶}
  1 => null
  2 => ProjectDetail {#3028 ▶}
  3 => null
  4 => ActionItem {#2978 ▶}
  5 => null
  6 => null
  7 => ActionItem {#3191 ▶}
  8 => null
  9 => null
  10 => ActionItem {#3200 ▶}
  11 => null
  12 => null
  13 => ActionItem {#3205 ▶}
  14 => null
  15 => null
  16 => ActionItem {#3210 ▶}
  17 => null
  18 => null
  19 => ActionItem {#3214 ▶}
]

I could live with the nulls, but I was hoping to get rid of them. Besides in this case I was expecting to get 2 nulls (one for ProjectNotification and another for ProjectBenefit, while I have a lot. Why?

Any other advice?

Thank you!

abdiel · Answer 1 · 2016-01-11T13:58:34.460

1

I don't see the need of use LEFT OUTER JOIN. If relations are properly configured, especially in project entity you can make a simple JOIN and get not nulls.

 $query = $em->createQuery('SELECT p, pn, pd,pb, pa '
        . 'FROM AppBundle:Project p '
        . 'JOIN p.notifications pn '
        . 'JOIN p.details pd '
        . 'JOIN p.benefits pb '
        . 'JOIN p.actions pa '
        . 'WHERE p.id = :id ')
        ->setParameter('id', $project->getId());

edited Jan 11 '16 at 13:58

answered Jan 11 '16 at 13:51

abdiel

2,078
16
24

the problem with this solution is that, if the relationship is OneToOne, it always performs an eager loading, regardless of the fact that you specify to do a lazy loading. So it's ok in this case, but it's not ok when I retrieve the project entity for other purposes and get loaded also unnecessary stuff (details http://stackoverflow.com/questions/9848747/primary-key-of-owning-side-as-a-join-column/27112833#27112833) – Sergio Negri Jan 11 '16 at 15:03
In my opinion a relations between two entities is always a fact, so this scenario should be the standard to use and all the entities must have all it's relations properly defined . You always can have the control about what information bring or not from database. If you have another scenario where you only want to load specific data then you must adjust your query for doing that and avoid to use lazy loading. So in the case that you say that it's not ok, create another query for request less data. – abdiel Jan 11 '16 at 17:22
That's the issue, it's not a matter of query that I perform: you don't have the control of what you get. In OneToOne relationship (as explained in detail in the link I provided) you have no option: you want the "parent", it will retrieve the "child". Even doing a simple $em->getRepository('AppBundle:Project')->find($projectId) you get all OneToOne children, which is a nonsense. It's a limitation of the ORM I'm trying to overcome. In my case I have at least 10 fat children. I'm forced to retrieve more than 10 times what I need :( – Sergio Negri Jan 12 '16 at 09:41

Doctrine single query for unrelated entities (for dashboard)

1 Answers1