I concur about the weakness in Django, when it is, as you say, a relatively simple SQL statement to pull in that data.
I'd go for the list comp idea if you've got a small data set. Alternatively you could select all the answers and then use itertools.groupby to group by question. That's also efficient as it won't use a lot of memory to do the transformation. Euan On Jun 22, 2:01 am, JeffH <holtzma...@gmail.com> wrote: > Doing a nested list comp has to be less efficient than an outer join > done at the db level. For my issue, it's a relatively small data set, > so I'd rather be more pythonic than eg running raw sql. Nonetheless, I > view this as a weakness in Django's ORM, and would plead with TPTB to > provide a solution, not that it would affect my current problem. > > On Jun 21, 11:46 am, "euan.godd...@googlemail.com" > > <euan.godd...@gmail.com> wrote: > > AFAIK there is no direct way to do this sort of thing in a single > > query. select_related will only get foreign key relations into the > > query so the ORM will always do an SQL query for each row in your many > > to many. In these types of situations I tend to select everything I > > need and then run some sort of pre-processing to put the two sets > > together, thus ensuring only 2 queries rather than n+1 (where n is the > > number of items in your original queryset). > > > Scott Gould's answer is about as good as you'll get I reckon. > > > On Jun 21, 3:37 pm, JeffH <holtzma...@gmail.com> wrote: > > > > That looks reasonable... but I wonder if the ORM can do it directly > > > somehow. Anyone? > > > > On Jun 21, 10:14 am, Scott Gould <zinck...@gmail.com> wrote: > > > > > There may well be a better way to do this, especially since it's been > > > > a good year since I was struggling with this myself. (Very similar > > > > case to yours, different subject matter of course.) > > > > > The way I ended up doing it was to use a template tag and some list > > > > comprehensions to whittle things down. E.g.: > > > > > questions = Questions.objects.all() > > > > answers = Answers.objects.filter(candidate=my_candidate) > > > > > questions_and_answers = [(q, [a for a in answers if a.question = q]) > > > > for q in questions] > > > > > ...which should give you a list of (question, <list of answers>) > > > > tuples. > > > > > On Jun 21, 10:00 am, JeffH <holtzma...@gmail.com> wrote: > > > > > > To clarify: Each race has a set of questions. The candidate may have > > > > > responded to none, some, or all. The answers are linked to the > > > > > candidate (and to the question). For each candidate, I want to display > > > > > all the questions, with or without answer. The way it works currently, > > > > > only the questions with answers get displayed. > > > > > > On Jun 21, 8:41 am, Daniel Roseman <dan...@roseman.org.uk> wrote: > > > > > > > On Jun 21, 12:51 pm, JeffH <holtzma...@gmail.com> wrote: > > > > > > > > I have some models that (simplified) look like the following. > > > > > > > > class Answer(models.Model): > > > > > > > id = models.CharField(max_length=32, primary_key=True) > > > > > > > text = models.TextField(blank=False) > > > > > > > question = models.ForeignKey(Question) > > > > > > > candidate = models.ForeignKey(Candidate) > > > > > > > > class Question(models.Model): > > > > > > > id = models.CharField(max_length=32, primary_key=True) > > > > > > > text = models.TextField(blank=False) > > > > > > > > class Candidate(models.Model): > > > > > > > id = models.CharField(max_length=32, primary_key=True) > > > > > > > name = models.CharField(max_length=32, blank=False) > > > > > > > > class Race(models.Model): > > > > > > > id = models.CharField(max_length=32, primary_key=True) > > > > > > > name = models.CharField(max_length=128, blank=False) > > > > > > > questions = models.ManyToManyField(Question) > > > > > > > candidates = models.ManyToManyField(Candidate) > > > > > > > > So, a Race has Candidates and Questions, and a Candidate has > > > > > > > Answers. > > > > > > > Each answer is associated with a Question and a Candidate. > > > > > > > Displaying > > > > > > > the question associated with an answer is easy: > > > > > > > > # context variable in view > > > > > > > answers = Answer.objects.filter(candidate=candidate) > > > > > > > > # template code > > > > > > > <table> > > > > > > > {% for answer in answers %} > > > > > > > <tr> > > > > > > > <td>{{answer.question.text}}</td> > > > > > > > <td>{{answer.text}}</td> > > > > > > > </tr> > > > > > > > {% endfor %} > > > > > > > </table> > > > > > > > > From the point of view of the Candidate, I need to display all the > > > > > > > questions, including the ones without Answers. I know how to to do > > > > > > > this using raw sql and an outer join. How to do it in the orm? > > > > > > > > Thanks in advance for any ideas. > > > > > > > > --Jeff > > > > > > > Not quite enough information here to answer. What are you wanting to > > > > > > join? If you just want to display all the questions, why do you > > > > > > need a > > > > > > join at all? > > > > > > -- > > > > > > DR. -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-us...@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.