How can I join multiple tables in Django 1.9?

Question

I need some help. My problem is that I'm trying to join multiple tables/models ((Question, Answer, User)) in Django 1.9 to get how many answers have a set of question made by a certain user.

I already have a sql query of what I want:

 SELECT q.id, q.title, COUNT(a.id) AS total_answers 
 FROM review_question AS q
 JOIN review_answer AS a ON q.id = a.question_id
 JOIN users_user AS u ON q.user_id = u.id
 WHERE q.user_id = 1
 GROUP BY q.id, q.title;

And here are my models:

review/models.py

[Question]

class Question(models.Model):
    user = models.ForeignKey(User, db_index=True, null=True, blank=True)
    tag = models.ManyToManyField(Tag)
    title = models.CharField(max_length=200)

[Answer]

class Answer(models.Model):
    question = models.ForeignKey(Question)
    user = models.ForeignKey(User, db_index=True, null=True, blank=True)

users/models.py

[User]

class User(models.Model):
    username = models.CharField(max_length=100, unique=True)

By the way, in my file users/views.py I have the next:

class UserDetailView(DetailView):
    model = User

    def get_context_data(self, **kwargs):
        # calling the get_context_data parent here
        questions = Question.objects.filter(user = context['object']).order_by('created')

but, I just get all the questions that the user has made.

I've been trying for a while looking for a way how to translate the query of above into the django-orm but, I still cannot accomplish that. Any help would be appreciate.

You're on the half way. Now that you have the questions `Answer.objects.filter(question__in=questions)`. Check this: https://docs.djangoproject.com/es/1.9/ref/models/querysets/#in — MikeVelazco, Apr 27 '16 at 21:41
How about `Answer.objects.filter(question__user=user)`? EDIT: I hadn't noticed the GROUP BY in your query, my suggestion won't do that. — Paulo Almeida, Apr 27 '16 at 21:47
@MikeVelazco Thanks, I'll check it out and see what can i do ;) — Carlos Parra, Apr 27 '16 at 21:50
@PauloAlmeida That's right. Your suggestion would work if I would need to know how many answer has made a certain user, but what I need is how many answer on each questions. Anyway, Thanks. — Carlos Parra, Apr 27 '16 at 21:52
Did you look into aggregation? http://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django — Paulo Almeida, Apr 27 '16 at 21:57
For instance, something like `Question.objects.annotate(num_answers=Count('answer'))`. — Paulo Almeida, Apr 27 '16 at 22:00
@PauloAlmeida with this code `Answer.objects.annotate(num_answers=Count('question'))` It's kind of working, but I'm still needing filter the answer by question. When I apply `.count()` to the previous code, it returns all the answers made by that user. *I'm almost close :)* — Carlos Parra, Apr 27 '16 at 22:15
And my suggestion didn't work? To be honest, I don't really know how it is supposed to get the 'answer', since it's not a field of the model, but I guessed it would follow the reverse relation. I took it from here: https://docs.djangoproject.com/en/1.9/topics/db/aggregation/#cheat-sheet — Paulo Almeida, Apr 27 '16 at 22:21
Exactly, it's explained here: https://docs.djangoproject.com/en/1.9/topics/db/aggregation/#following-relationships-backwards — Paulo Almeida, Apr 27 '16 at 22:23
@PauloAlmeida Finally I did. Thanks for your help. This is the final piece of code: `Answer.objects.filter(question__id=9).annotate(num_answers=Count('question'))`. That returns what I wanted to. Thanks again. :) — Carlos Parra, Apr 27 '16 at 22:40
If you already have an sql query of what you want why all this effort? Just use raw query. You avoid the overhead of the django query builder too. — e4c5, Apr 28 '16 at 02:41

score 0 · Accepted Answer · answered Apr 29 '16 at 01:08

Finally I could help my problem.

What I did was the next:

in my file users/views.py

class UserDetailView(DetailView):
    model = User

    def get_context_data(self, **kwargs):
        # Calling the get_context_data parent
        questions = Question.objects.filter(user = context['object']).order_by('created')

        tags = [ question.tag.all() for question in questions ]

        total_answers = self.get_total(questions) # Calling the function that return the total answer by question of a specific user
        context['question_tags'] = zip(questions, tags, total_answers) #Zipping the lists with results to use it in the template

        return context

    def get_total(self, questions):
        #Return the total answers that a question has (Here is the trick!)
        return [
            Answer.objects.filter(question__id=question.id).annotate(num_answers=Count('question')).count() for question in questions]

Tha was all I did. Finally, I would like to especially thanks to @PauloAlmeida and @MikeVelazco for the help!

If you're going to use a list comprehension, you could probably just do `return [question.answer_set.count() for question in questions]`. — Paulo Almeida, Apr 29 '16 at 10:18
Yes, you're right. Looks better now ;) But, could you explain me about that `.answer_set.count()` I don't get it very well. — Carlos Parra, Apr 29 '16 at 16:09
It's ok, I already found it in the docs. Looks like its a better way of doing so. Thank you again. — Carlos Parra, Apr 29 '16 at 16:44

How can I join multiple tables in Django 1.9?

1 Answers1