Opened 19 years ago

Closed 19 years ago

Last modified 18 years ago

#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 Jacob, 19 years ago

Resolution: wontfix
Status: newclosed

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.

comment:2 by Justin, 19 years ago

Resolution: wontfix
Status: closedreopened

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 Adrian Holovaty, 19 years ago

Resolution: wontfix
Status: reopenedclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top