FuelPHP Forums

Can you dump the query (or use the profiler to get it) so we can see what goes wrong?

Interesting. I wonder why that sub query is inserted to deal with that WHERE clause. I have to pass this over to Jelmer... What version of FuelPHP are you running? A release version, or 1.1/develop?

It's a known limitation of the subquery usage. In short: without the subquery it's impossible to query one->many relations with limits. Using the subquery has the disadvantage this might happen. Search the forums and Github for a fuller explanation as I've gone over this many times already.

I'm open to any solution, but every solution has limitations and this one has the least number of downsides as I see it.

The only way to fix this is to work on the way the queries are constructed when using limit. We had this discussion before, and this implementation still doesn't make sense to me. There is a distinct difference between "Give me the first 10 results of Parents with Children" and "Give me the first 10 children with their parent info". As a developer, I know what output I want, and in most cases, it's the first one (typically used in paged lists), and not the second one. Which is the only one implemented at the moment. So I think we need to have the choice which query is generated, which could be done by allowing a limit to be defined on the relation (which means a subquery) or on the parent table (which means a normal join). Having said that, I know that for a lot of cases it doesn't really make sense to query a one-to-many relationship with a limit, since you will probably end up with incomplete information. And I guess that is the reason it is implemented like this. @arnoldgamboa, Can you give a use-case in which this behaviour is desired? So a case in which you want to include child records in a has_many relation, yet you don't care if the information is complete or not?

Harro Verton wrote on Tuesday 31st of January 2012:

We had this discussion before, and this implementation still doesn't make sense to me. There is a distinct difference between "Give me the first 10 results of Parents with Children" and "Give me the first 10 children with their parent info". As a developer, I know what output I want, and in most cases, it's the first one (typically used in paged lists), and not the second one. Which is the only one implemented at the moment.

I think you have this backwards: the whole reason for the subquery is to fetch 10 of the parents with the children, which is distinctly not what the non-subqueried query would do. For the latter option there's the rows_offset() and rows_limit() methods available in the ORM query builder. The subquery makes sure you get the number of objects from the parent before a limit is imposed. Instead of the more common implementation where the limit is imposed on the joined result and the number of parents is pretty much random when a one->many relation is involved.
This has the known limitation that querying on relations with limits might get the wrong result, but that's a smaller limitation than the aforementioned. [edit]There is a way to solve this: add a bit of complexity by allowing people to change this behavior, it shouldn't be hard as the decission whether or not to use a subquery is abstracted into a method inside Orm\Query. This might be done by adding a switch that says $disable_subquery along with a method ->disable_subquery($bool). That would allow for queries like yours and preserve the current implementation.
But I don't have the time to implement or test this anytime soon, neither would it be on the top of my list if I had the time.

Still, I can imagine it is strange for people (used to writing joins by hand or using a QB) in a case where you have 5 parents, each with 3 children, a limit of 10 would result in having 15 records returned (as the subquery with the limit is on the parents, which with a limit of 10 will return all 5 of them).

The use case when you know exactly the number of children will be a pretty rare one to say the least, and would be doable with rows_limit() and rows_offset().

If (using this case as an example) you wanted a paginated list of volunteers with info about the team's there in, you can't make neat lists of 10 entries per page.

That's exactly what it is that this makes possible. The problem only arises when your where condition on the relation takes out some of the parents that were already limited.

Do you have an example of how you implement that? I guess that would help people struggling with this issue.

The decission whether or not to use a subquery is made in the following function: https://github.com/fuel/orm/blob/1.1/develop/classes/query.php#L832
Adding the code I suggested earlier would allow for other usages probably.

No. And if the ORM was a query builder, your opinion would be right.

But it isn't, its an ORM, an object Relation Manager, and it will try it's best to make sure relations stay complete and together.

If you want QB behavior, you can force the ORM to do so by using rows_limit() and rows_offset(), which would place them om the outer query.

This may be handy for pagination for example, but know that the results might be incomplete (if you start processing related records) or unpredicable (most RDBMS'ses doesn't guarantee the same order of related records in a join, unless you explicitly order them).