Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

Mysql hasMany relationships spawns a subquery? #436

Closed
marcghorayeb opened this Issue Apr 23, 2012 · 11 comments

Comments

Projects
None yet
4 participants
Member

marcghorayeb commented Apr 23, 2012

Hi,

Why do hasMany relationships spawn 2 queries, one to get IDs, and the second to get all the data? Is there a way to disable this behavior? I found the lines to comment so that the subquery isn't spawned but I don't know why it's there in the first place?

Contributor

jails commented May 7, 2012

It makes limit working correctly with JOIN.

Member

Howard3 commented May 19, 2012

When you perform a hasMany with a limit the id's must be fetched with the limit, then the joined query with the fetched id's.

When MySQL runs a query with a join, each additional member of that join is a returned result which counts against limit. So if you fetch with a limit of three, but the first result's hasMany has 10 joined records, you will only be fetching three of those, and none of the other primary table records.

@Howard3 Howard3 closed this May 19, 2012

Member

marcghorayeb commented May 19, 2012

okay i understand the reasoning now, thank you :)

Contributor

hans-d commented Jun 15, 2012

@Howard3... That is only true when the model is actually using the hasMany during that query. When doing a find without the hasMany, the group by should not be needed (but currently still is).

Member

Howard3 commented Jun 15, 2012

@hans-d

If this is doing this with all queries (not just ones w/ hasmany + limit) then something is wrong and needs to be investigated. Can you show a query where it's doing the group by on a non-hasMany query?

Best Regards,
Howard

Contributor

hans-d commented Jun 16, 2012

It does this with all queries:

  • where the model has defined an $hasMany relationship
  • the defined relationships are not used in the query (thus only 1 table is selected from)
  • the query uses a limit, eg ::all(array('limit' => 200))
Member

Howard3 commented Jun 17, 2012

@hans-d if you can make a test for this I'd be glad to look at it.

Member

marcghorayeb commented Jun 18, 2012

I think by the looks of it, that line 271 in Database.php is the culprit.
It only checks if the model has a hasMany relationship, not if the query actually uses it.

Member

Howard3 commented Jun 18, 2012

@marcghorayeb Indeed. I'll try to get a test/patch in for that tonight. Thanks!

Contributor

hans-d commented Jun 18, 2012

yup - I was looking at the same suspect. Had no time yet to make a testcase for this one that can be used here. I have it using my test setup, but that uses a mock sql backend that is not in li3 (yet?)

Contributor

hans-d commented Jul 21, 2012

@Howard3 can this issue be re-opened or should we create a new issue (issue: unneeded subqueries when $hasMany defined in the model but not used for a query).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment