Best way in Doctrine to load only entities which have attached entities

Question

I have an entity, let's call it Foo and a second one Bar

Foo can (but doesn't have to) have one or multiple Bar entries assigned. It looks something like this:

/**
 * @ORM\OneToMany(targetEntity="Bar", mappedBy="foo")
 * @ORM\OrderBy({"name" = "ASC"})
 */
private $bars;

I now would like to load in one case only Foo entities that have at least one Bar entity assigned. Previously, there was one foreach loop to traverse all Foo entries and if it had assigned entries, the Foo entry got assigned to an array.

My current implementation is in the FooRepository a function called findIfTheresBar which looks like this:

$qb = $this->_em->createQueryBuilder()
        ->select('e')
        ->from($this->_entityName, 'e')
        /* some where stuff here */
        ->addOrderBy('e.name', 'ASC')
        ->join('e.bars', 'b')
        ->groupBy('e.id');

Is this the correct way to load such entries? Is there a better (faster) way? It kind of feels as if it should have a having(...) in the query.

EDIT:
I've investigated it a little further. The query should return 373 out of 437 entries.

Version 1: only using join(), this loaded 373 entries in 7.88ms
Version 2: using join() and having(), this loaded 373 entries in 8.91ms
Version 3: only using leftJoin(), this loaded all 437 entries (which isn't desired) in 8.05ms
Version 4: using leftJoin() and having(), this loaded 373 entries in 8.14ms

Since Version 1 which only uses an innerJoin as @Chausser pointed out, is the fastest, I will stick to that one.

Note: I'm not saying Version 1 will be the fastest in all scenarios and on every hardware, so kind of a follow up question, does anybody know about a performance comparison?

Unless you're filtering something in `/* some where stuff here */` part that query will still give you all `foo` entities and not just the ones with a `bar` attached - there's indeed a `->having('COUNT(b) > 0')` missing. — ccKep, Jun 28 '17 at 18:54
Hmm probably those entries I've checked where just by coincident gone. Will try it later. — wawa, Jun 28 '17 at 19:00
This is probably best way to do this. Also I don't think that what ccKep said is true since you've done a `join()` and not a `leftJoin()`. To be sure, you can check final SQL query in Symfony profiler. — Jakub Matczak, Jun 28 '17 at 19:58
As `join()` is just basically an alias for `innerJoin()` that should work. — Chase, Jun 28 '17 at 21:37

score 0 · Answer 1 · answered Jun 29 '17 at 15:11

Please take a look at this answer for more information on how SQL JOINs work: https://stackoverflow.com/a/16598900/1307183

Using a join, which is an alias of innerJoin, is exactly what you want. This only returns records where entries exist in both Foo and Bar - aka where the association/attached entity exists. This calls INNER JOIN in SQL, which, if your database structure is defined correctly, is the absolute best and fastest way to get the data you want.

Using a leftJoin calls LEFT JOIN in SQL, which returns all records from Foo, even if there is no Bar associated with it (for example, where bar_id in your foo table would be null).

You have no reason to use having() in any of the above scenarios you described. If you want to filter further you would do that with a ->addWhere() function. Using the having() clause is something you would only want to do if you were selecting aggregate data in your original query (like SELECT SUM(field) AS sum_field).

Best way in Doctrine to load only entities which have attached entities

1 Answers1