DDC-1600: ORM loads an association without it being ever accessed

[doctrinebot]

Jira issue originally created by user ambis:

03:14 < beberlei> Ambis, please report a bug on jira with details - this doesnt sound correct

I have a basic "Comment has an author" relation. Because I get each of my users from Redis cache directly using user's ID, the comment tracks both user as relation and also user's id like so:

<field name="userId" column="user_id" type="integer" />
<many-to-one field="user" target-entity="User">
    <join-column name="user_id" referenced-column-name="id" />
</many-to-one>

(both access the same db field)

When I query only comments and access user only by getUserId() which returns the property 'userId' as seen on the mapping, my query logging shows that for each comment author there is a separate query to load it. I do not call getUser() at any point which would access the relation 'user' in the comment. Nothing within the comment entity itself accesses the relation. There seems to be no proxy generated out of the comment entity in my proxies folder.

I've had these problems before but I've always found out what was the reason behind this, but none of those seem to be the case this time. Unfortunately I have too much 2.2 code to roll back to 2.1 and test with that.

Edit:

The query itself is very simple:

CommentRepo->createQueryBuilder('comment')
->where('comment.anotherProperty = :somethingOrOther')
->setParameters(array(...))
->orderBy('comment.id', 'DESC')
->getQuery()
->getResult();

As always, there's lot more stuff to the query, but I've commented it out to that bare minimum when I still get this behavior.

Edit2:

I added to the query
->addSelect(array('comment_user'))
->join('comment.user', 'comment_user')

and of course the extra user queries went away.

I also added a die statement to the getUser() method (yes, above return ;) ) to see that it's really not called and it's not. Also re-checked the comment entity that nothing accesses the ->user property other that constructor and getUser().

[doctrinebot]

Comment created by @beberlei:

Cannot reproduce this. Attaching a test-case with a working scenario.

Can you adapt this to your situation and try to make it fail?

[doctrinebot]

Comment created by ambis:

I think there's a problem with the testcase regarding the SQL logging.

If you take a look at my test, when run, it doesn't show the query to get the user object (but it is being run since getId() returns 1). And since the EM has been cleared, it shouldn't even be managed by UoW.

I'm also testing using QB since that's what my code uses.

(Yes yes not a nice testcase with print_r(), but for this time shows clearly what's wrong.)

[doctrinebot]

Comment created by @FabioBatSilva:

Hello Matti,

Are you sure about this behavior ? I try your test case and can't get this.

if the User has just a id property like in you example, the sql to get that User not will be executed when you are loading a Comment,
because the user_id is loaded from Comment, if that is the only one property of the Use entity makes no sense load that again.

If the User entity has other properties like name, email the User sql used to load that entity will be executed when the getter (getName, getEmail) is called.

[doctrinebot]

Comment created by @FabioBatSilva:

I'm attaching a new test case ..

[doctrinebot]

Comment created by ambis:

Hello Fabio,

I'm unable to reproduce this in the testcase here. I think we should keep to the query builder since that's what I'm using. Find() doesn't seem to be using it so hard to say if it might have any effect.

It seems that calling only $comment->getUser()->getId() won't trigger any extra queries, but getUser()->getName() will (just like in your test case).

I'm 100% I do not have any calls to anything other than $comment->getUserId().

I'll attach a screenshot of Zend Debugger stack trace, where breakpoint is set to the receiving logger when the extra SQL gets run, hope it might give any indication to what is going on.

bq. if the User has just a id property like in you example, the sql to get that User not will be executed when you are loading a Comment, because the user_id is loaded from Comment, if that is the only one property of the Use entity makes no sense load that again.

I know and that's what this issue is all about. I do not touch the user relation in any way and still it is accessed.

[doctrinebot]

Comment created by ambis:

Actually I just realized that it's not only the comment's user, but also a user few relations down from the comment.

A Comment has a one-to-one relation to a Conversation, which has one-to-many relation to Message, which again has a many-to-one relation to a user (just like the Comment).

AFAIK, a unidirectional relation does not specify mapped-by or inversed-by attributes? So there's nothing I can add to the user relation other than what the issue alrady has described?

Now I added both a Message and a Comment from different users, and they both trigger an extra query for their info.

And it's notable that I end up in those extra queries WITHIN the getResult() of the query builder's query. Not after the repository method when processing the Comments.

[doctrinebot]

Comment created by ambis:

Now that we've established that calling $comment->getUser()->getId() shouldn't load the User object, I removed the

from both Comment and Message. I should be able to use getUserFromCacheById($comment->getUser()->getId()) without loading the user assoc. (Right?)

But still, the user assoc gets loaded.

And as a reminder, the query is being executed within the getResult() in the original query for the comments (verified with Zend Debugger).

I've also made sure that all caches (mapping, query & result) are all set to ArrayCache for this.

[doctrinebot]

Comment created by @FabioBatSilva:

Thanks for the details Matti,

Can you attach your entities and mapping files please ?
I'll try to reproduce this into a better test case..

[doctrinebot]

Comment created by @beberlei:

Shouldnt this be a many-to-one association? According to your screenshot a query for a one-to-one association is fired.

[doctrinebot]

Comment created by ambis:

UnitOfWork.php#2422:

                    case ($targetClass->subClasses):
                        // If it might be a subtype, it can not be lazy. There isn't even
                        // a way to solve this with deferred eager loading, which means putting
                        // an entity with subclasses at a *-to-one location is really bad! (performance-wise)
                        $newValue = $this->getEntityPersister($assoc['targetEntity'])->loadOneToOneEntity($assoc, $entity, $associatedId);
                        break;

...my user is subclassed. That's why it eagerly loads it as the one-to-one entity.

I'll add table inheritance to the testcase and confirm this is the case. I replicated everything except that into the testcase (in terms of classes and mapping) and wasn't able to reproduce the problem. I bet adding STI to the user makes this "documented bug" appear.

[doctrinebot]

Comment created by ambis:

Yep. Confirmed.

This can be closed as Not a bug.

I believe this has been explained in the manual somewhere but didn't find anything.

[doctrinebot]

Comment created by ambis:

Confirmed in my codebase also.

Removed STI from User and now the extra queries are gone.

Kind of sad really, because it was so nice just to check if user was either a NormalUser or AnonymousUser... But there's so many relations like the one here that it's just best to keep it without STI.

[doctrinebot]

Comment created by @guilhermeblanco:

As per user's comment.

STI is unable to track STI so it triggers loading of entity when we reach dead ends.

[doctrinebot]

Issue was closed with resolution "Invalid"

[doctrinebot]

Imported 4 attachments from Jira into https://gist.github.com/a288c171db06b147181d

• 11145_DDC1600Test.php
• 11146_DDC1600Test.php
• 11147_DDC1600Test.php
• 11149_DDC1600Test.php