Code

Opened 9 years ago

Closed 9 years ago

Last modified 7 years ago

#175 closed defect (wontfix)

select_related does not work in both directions..

Reported by: Justin Owned by: adrian
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: UI/UX:

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.

Attachments (0)

Change History (3)

comment:1 Changed 9 years ago by jacob

  • Resolution set to wontfix
  • Status changed from new to closed

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 Changed 9 years ago by Justin

  • Resolution wontfix deleted
  • Status changed from closed to 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 Changed 9 years ago by adrian

  • Resolution set to wontfix
  • Status changed from reopened to 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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.