Bug in handling select_related with limit and all() because of many2many relation about ormar HOT 6 CLOSED

Ok, so this one of the reasons why i.e. django don't allow select_related on M2M fields and reverse FK.

If you issue a select_related query a one (potentially huge) joined query is constructed and all typical SQL clauses are applied on the whole joined query -> so offset, limit, where etc. are applied at the end. Since it's a join from multiple tables in raw sql response you will have duplicated values for parent, when you apply limit on this it applies on the SQL rows (that's how sql limit works), that's why you get first 5 rows of data meaning 2 first PrimaryModels as they have 5 children, so consumed all in a limit in raw sql rows.

Now in order to limit this to 5 rows of primary model I would have to know in advance either how many children the parent's have (and children of children of children if it's a multiple join query) or extract ids of those parents first. Both are possible, but require additional query against the database.

I could do it in python but not knowing any of the two in advance I would always have to fetch all data from join and limit number of parent models in the result list (wasting the rest of fetched data)

I don't know if I will implement it cause it might be a huge effort or/and can slow down everything by quite a lot with that additional query and select_releted is specifically designed to be quick one db call query.

BUT - worry not :)

That's one of the reasons why prefetch_related was introduced.
Yours solution is as simple as changing the select_releted to prefetch_related in your query and it will pass.

The reason is that prefetch_releted grabs the related models in consecutive queries after the initial one is completed.
And limit/offset applies to the first query issued.

So it grabs 5 rows from primary model and then fetches the child models for only those 5 models already fetched.
It should be better documented, that's for sure :)

Let me know if that solves your issue.

from ormar.

@collerek Now that I'm really awake again, I'm not entirely sure how that would be done after all. You are correct, there must be some processing also on the Python side.

from ormar.

Come to think about this, i can also probably try to play with replacing first table with subquery if there is a limit, have to check it. But then what about the situation when you actually want only 10 rows from db (some flag like raw_sql=False in limit?), have to think about this 🤔

from ormar.

@collerek Great, thanks! Had totally missed the prefetch function. That fixed the issue.

What came to my mind, is it possible to do the grouping in the query when selecting? Now the grouping is done in Python.

from ormar.

@soderluk What exactly would you like to group?

Cause in SQL you can group only with aggregation functions (sum/ avg/ count etc.).
And even then if you group by more than one column you get duplicates of the first grouping etc.

I.e. when you group by size and color you will get:

Size XL, Yellow, Sum of something;
Size XL, Green, Sum of something;
Size XL, Black, Sum of something;
Size L, Yellow, Sum of something
Size L, Green, Sum of something

So if you want one object per size (this is ormar.Model in this sample) you still need to aggregate/combine it in python (somewhere else than sql itself) cause raw response have duplicates.

from ormar.

Ok i followed the subquery approach and now you can also use select_related with limit and offset and it will affect number of main models only. To actually limit the raw sql rows numbers you can use limit_raw_sql flag.

from ormar.