Doctrine loads all records twice

Question

I have an entity called Category with the following fields:

id: integer
name: string
slug: string
children: OneToMany(targetEntity="Category", mappedBy="parent")
parent: ManyToOne(targetEntity="Category", inversedBy="children")

As you can see, each Category can be a child\parent of another.

I need to map all the categories to an array, so I figured it will be best to just load them all together:

$categoryRepository->findAll();

Later on, on the mapping method, I want to get each Category's children, so I use

$category->getChildren();

I assumed that since I loaded all the categories in the first place, only 1 query will be executed, but the profiler shows differently! There is 1 query where it fetches all the records (output from the profiler):

SELECT t0.id AS id1, t0.name AS name2, t0.slug AS slug3, t0.parent_id AS parent_id4
FROM acme_category t0

And then there is another query for every record:

SELECT t0.id AS id1, t0.name AS name2, t0.slug AS slug3, t0.parent_id AS parent_id4
FROM omnt_work_category t0 WHERE t0.parent_id = 1

SELECT t0.id AS id1, t0.name AS name2, t0.slug AS slug3, t0.parent_id AS parent_id4
FROM omnt_work_category t0 WHERE t0.parent_id = 1

etc..

Why is that? How can I make it load all the records from the first place?

Thanks!

score 0 · Accepted Answer · answered May 28 '12 at 10:34

0

You can have it load automatically by declaring fetch="EAGER" on the relation annotation.

However looking at your implementation it looks like you trying to create a hierarchy of data by maintaining a child / parent relation. You might be better off implementing a nested set solution for this. There's a few libraries out there for Doctrine 2 that can handle this for you.

One I've used and recommend is >> https://github.com/blt04/doctrine2-nestedset

Its certainly interesting that Doctrine is attempting to pull in the same record multiple times instead of searching the Cache. Maybe it handles caching of relational items slightly differently. Would certainly be worth investigating.

answered May 28 '12 at 10:34

Lee Davis

4,685
3
28
39

You need to configure the result cache to make it use the cache. By default it's disabled for development purposes (because a same query may return a different result). Doctrine only ensures there's no two objects with a same id of a same entity in your application context. – meze May 28 '12 at 11:04
@Lee, I added fetch="EAGER" and got 45 queries =/ I'll try using the library you mentioned and see what happens.. – tamir May 28 '12 at 14:48
Question about this library: I have multiple root nodes, how do I fetch them all? fetchTree() fetches only 1 tree by it's id. – tamir May 28 '12 at 19:02

Doctrine loads all records twice

1 Answers1