Electronics & Programming

develissimo

Open Source electronics development and programming

  • You are not logged in.

#1 June 21, 2010 12:51:14

J.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

outer join in orm


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

--
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 athttp://groups.google.com/group/django-users?hl=en.

Offline

#2 June 21, 2010 13:41:38

Daniel R.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

outer join in orm


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 athttp://groups.google.com/group/django-users?hl=en.

Offline

#3 June 21, 2010 15:00:19

J.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

outer join in orm


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 athttp://groups.google.com/group/django-users?hl=en.

Offline

#4 June 21, 2010 15:14:18

Scott G.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

outer join in orm


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, )
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 athttp://groups.google.com/group/django-users?hl=en.

Offline

#5 June 21, 2010 15:37:45

J.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

outer join in orm


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, )
> 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 athttp://groups.google.com/group/django-users?hl=en.

Offline

#6 June 21, 2010 16:33:12

b.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

outer join in orm


I hope you get an ORM answer, too. I'm a newbie following this thread and
this sounds like the type of problem I have run into a couple of times
already.



On Mon, Jun 21, 2010 at 9:37 AM, 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, )
> > 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<django-users%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
>http://groups.google.com/group/django-users?hl=en.
>
>

--
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 athttp://groups.google.com/group/django-users?hl=en.

Offline

#7 June 21, 2010 16:46:52

e.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

outer join in orm


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, )
> > 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 athttp://groups.google.com/group/django-users?hl=en.

Offline

#8 June 22, 2010 02:01:59

J.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

outer join in orm


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, )
> > > 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 athttp://groups.google.com/group/django-users?hl=en.

Offline

#9 June 22, 2010 10:50:23

e.
Registered: 2009-11-02
Reputation: +  0  -
Profile   Send e-mail  

outer join in orm


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, )
> > > > 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 athttp://groups.google.com/group/django-users?hl=en.

Offline

Board footer

Moderator control

Enjoy the 15th of December
PoweredBy

The Forums are managed by develissimo stuff members, if you find any issues or misplaced content please help us to fix it. Thank you! Tell us via Contact Options
Leave a Message
Welcome to Develissimo Live Support