#175 closed defect (wontfix)
select_related does not work in both directions..
Reported by: | Justin | Owned by: | Adrian Holovaty |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | |
Severity: | normal | Keywords: | select_related |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
comparing the following two statements:
>>> for p in polls.get_list(select_related=True): print p, p.get_choice_list() >>> for c in choices.get_list(select_related=True): print c.get_poll(), c
The first statement runs one query per poll, while the second runs one query no matter how many polls you have.
This seems to be because the information about what models are related to each other does not get added to both ends of the relationship... now this is not 100% true because a poll obviously knows about what choices reference it...
My guess is that there needs to be a OneToMany class that compliments the ManyToOne relationship, letting the Poll class know that select_related should pull in choices.
Change History (3)
comment:1 by , 19 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
comment:2 by , 19 years ago
Resolution: | wontfix |
---|---|
Status: | closed → reopened |
I don't think so.. the SQL should be the same for both cases, just flipped around. What the second line runs is:
SELECT polls_choices.id,polls_choices.poll_id,polls_choices.choice,polls_choices.votes, polls_polls.id,polls_polls.question,polls_polls.pub_date FROM polls_choices,polls_polls WHERE polls_choices.poll_id = polls_polls.id ORDER BY polls_choices.choice ASC
which basically grabs all the data from both tables..
The first line just runs
SELECT polls_polls.id,polls_polls.question,polls_polls.pub_date FROM polls_polls
Which doesn't get any choice data...
a better example may be:
>>> polls.get_object(id__exact=2, select_related=True)
which runs
SELECT polls_polls.id,polls_polls.question,polls_polls.pub_date FROM polls_polls WHERE polls_polls.id = 2
but could just as well be
SELECT polls_polls.id,polls_polls.question,polls_polls.pub_date, polls_choices.id,polls_choices.poll_id,polls_choices.choice,polls_choices.votes FROM polls_polls WHERE polls_polls.id = 2 AND polls_polls.id = polls_choices.poll_id
which is almost exactly the query you get when you run
>>> choices.get_object(id__exact=2, select_related=True)
just that does "polls_choices.id = 2" in place of "polls_polls.id = 2" to get the different object.
so it's not impossible... it just requires the OneToMany thingy in place so the referenced class knows to fetch the things that referenced it.
comment:3 by , 19 years ago
Resolution: | → wontfix |
---|---|
Status: | reopened → closed |
I'm closing this ticket. select_related
isn't intended to populate in the other direction. Think about this:
>>> polls.get_object(id__exact=1, select_related=True)
According to your proposal, it would run this SQL:
SELECT polls_polls.*, polls_choices.* FROM polls_polls, polls_choices WHERE polls_polls.id = polls_choices.poll_id AND polls_polls.id = 1;
That's totally easy and it makes sense. But the problem is that *more than one record will be returned for each poll*. And if you have *multiple* objects that are related ManyToOne with Poll, it'd get quite complex to select all of them and iterate over them in Python to populate the caches. This isn't how {{{select_related}} is intended to work.
This is basically impossible to fix. If you think about the SQL involved, you'll see why: getting a choice's poll is easy since it's basically adding a simple JOIN to the initial query, but looking up all the fields of a poll's is impossible since you can't know the number of rows that will be returned.
Of course, I'm not a database expert, so if you can write the SQL feel free to reopen this ticket.