Django ORM Query to join tables and then analyze

Question

class Answer(models.Model):
    question = models.ForeignKey(Question)
    answer = models.CharField(max_length=30)


class Response(models.Model):
    answer = models.ForeignKey(Answer)
    user = models.ForeignKey(User)
    time = models.DateTimeField(auto_now_add=True)


class Profile(models.Model):
    GENDER_CHOICES = (
        ('M', 'Male'),
        ('F', 'Female'),
    )
    user = models.OneToOneField("auth.User")
    age = models.IntegerField(max_length=2)
    gender = models.CharField(max_length=1, choices=GENDER_CHOICES)
    zipcode = models.IntegerField(max_length=5)
    state = models.CharField(max_length=15)

I have a Response model that is answers to a question and a Profile model for each user and was wondering how to go about the query to get a list of each users answer (they are unique for each question for each user) and their profile info. In the end I want to say what percentage of men answered one way vs another, how states voted, etc. I am able to make a query to see what percentage of users voted for each question, but havent been able to break it down farther. How would I make a query to find something like what percentage of an answer each gender voted for?

If i could join the Profile and Response tables on User I would be able to make a numpy array and analyze it like that, which I think would be helpful as well.

OozeMeister · Accepted Answer · 2014-01-07 05:58:28Z

3

Many-To-One Magic

Django provides convenience methods when any model has a models.ForeignKey field. See Many-To-One Relationships for more details. Check out this example to find what percentage of an answer each gender voted for?

Given a particular Answer as answer:

>>> answer.response_set.filter(user__profile__gender='M')
[<Repsonse 1>, <Repsonse 2>, <Repsonse 3>, ... ]

That will give you all of the responses where the user who answered was a male.

If you only want to know just the statistics and don't care about the actual Response objects returned, you can simply use the .count() method like this:

>>> queryset = answer.response_set.all()
>>> total_responses_for_answer = queryset.count()
>>> number_of_males = queryset.filter(user__profile__gender='M').count()
>>> print 'Percent', (number_of_males / float(total_responses_for_answer)) * 100
98.1

Let me try to explain what's going on.

response_set.filter()

When model B has a models.ForeignKey field that points to model A, Django uses the related_name keyword argument to create a special method on model A that gets all relations in model B that point to the model A instance.

If the related_name keyword isn't specified, Django defaults the method name to <relationship>_set.

In this case, the answer field on the Response model did not specify the related_name keyword argument, so Django added the convenience method response_set on to the Answer model.

To see all Responses that chose answer, use the simple query:

>>> answer.response_set.all()

If you wanted, you could specify the related_name to create your own relationship method. For example:

class Response(models.Model):
    ...
    answer = models.ForeignKey(Answer, related_name='responses')
    ...

# and use the custom method name like so:
>>> answer = Answer.objects.get(pk=pk)
>>> answer.responses.all()

filter(user_profile_gender='M')

The second bit of magic happening is the JOINs that you're looking for. The response_set returns a queryset of Response objects. The Response model has a field called user. The Profile model has a relationship to User via the models.OneToOne field user and the Profile model also contains that user's gender (and other fun stuff). The fancy double underscore part user__profile__gender='M' is simply saying:

Find all Response instances WHERE the Response's user's __ profile's __ gender equals 'M'

answered Jan 7, 2014 at 5:58

OozeMeister

4,9091 gold badge25 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TH22 Over a year ago

So then would there be a way to get an output that would specify # of responses for M and # of responses for female for each answer? I am looking to do this for every answer within a question, so in the end I want to get something like: Question: Coke or Pepsi --Answer:Coke (100 responses, 60M, 40F), Answer:Pepsi(50 responses,20M,30F). I am trying to not do this by selecting the gender specifically(for this example), because I am also trying to do the same thing for states, zipcodes, age.

OozeMeister Over a year ago

You'll want to take a look at Django's Aggregation API: docs.djangoproject.com/en/dev/topics/db/aggregation. There you can issue queries that will retrieve the statistical information that you desire.

Collectives™ on Stack Overflow

Django ORM Query to join tables and then analyze

1 Answer 1

Many-To-One Magic

response_set.filter()

filter(user_profile_gender='M')

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Many-To-One Magic

response_set.filter()

filter(user_profile_gender='M')

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related