Django: implementing JOIN using Django ORM?

Question

I have a Q&A type of site built in Django with the following models:

class Question(models.Model):
    title = models.CharField(max_length=70)
    details = models.TextField()

class Answer(models.Model):
    question_id = IntegerField()
    details = models.TextField()

I need to display a specific question together with its answers. Normally I'd need 2 queries to do that:

Question.objects.get(id=1)
Answer.objects.get(question_id=1)[:10]

I'm hoping to retrieve everything using one query. In MySQL it'd be:

SELECT *
FROM Question JOIN Answer ON Question.id=Answer.question_id
WHERE Question.id=1
LIMIT 10

Is there anyway I could do this through Django's ORM? Would extra() help in this case?

score 63 · Accepted Answer · edited Mar 28 '17 at 04:49

63

This is exactly what select_related() does. The only gotcha is that you have to start with the Answer model, rather than Question, but the result is the same:

answers = Answer.objects.filter(question_id=1).select_related()

Now each answer object has a pre-fetched 'question' attribute, and accessing it won't hit the db again.

edited Mar 28 '17 at 04:49

xssChauhan

2,728
2
25
36

answered Nov 08 '10 at 15:54

Daniel Roseman

588,541
66
880
895

2

Thanks, what if we need name of foreign key on related table, suppose table A is related to B and B is related to C. (all tables only have "name" and "id"). we want join that every entry of A shown side by related C table object. – Bheid Sep 07 '16 at 08:49
28

How can this work if `question_id` is just an integer, and `Answer` does not provide a `models.ForeignKey` field to `Question`? – Kurt Bourbaki Oct 09 '18 at 10:24

score 48 · Answer 2 · edited Feb 15 '19 at 20:40

48

Consider using models.ForeignKey(Question) instead of question_id = IntegerField().

This is the optimal (more relational) way to express the relationship between Questions and Answers you are trying to portray.

This way you can simply call Answers.objects.filter(question_id=<id>) and get exactly what you're looking for.

edited Feb 15 '19 at 20:40

David Maness

91
12

answered Nov 08 '10 at 15:47

baklarz2048

10,699
2
31
37

Using a `ForeignKey` is indeed the right way to go, but the query call offered in this answer only returns `Answer` objects, while the question was how to get the `Question` and `Answer` objects in one query. – talz Dec 11 '20 at 09:33

score 15 · Answer 3 · answered Dec 29 '15 at 09:15

15

    class Question(models.Model):
      title = models.CharField(max_length=70)
      details = models.TextField()

    class Answer(models.Model):
      question = models.ForeignKey('Question')
      details = models.TextField()

    id = <whatever_id>    
    answers = Question.objects.get(id=id).answer_set.all()

answered Dec 29 '15 at 09:15

Saurav Biswas

299
2
3

`id` is a built in function now, may not have been in 2015 – Shmack Sep 19 '22 at 18:24

dilip kumar · Answer 4 · 2020-07-21T16:10:01.720

#Consider A Foreign Key Relationship Between Books And Publisher

class Publisher(models.Model):
 name = models.CharField(max_length=100)

eclass Book(models.Model):
 publisher = models.ForeignKey(Publisher, on_delete=models.CASCADE)

#Fetch Publisher Name For A Book

book = Book.objects.select_related('publisher').get(id=1)
book.publisher.name

#Fetch books which have specific publisher

publisher = Publisher.objects.prefetch_related('book_set').get(id=1)
books = publisher.book_set.all()

for more https://kwikl3arn.com/django/JOINS

Django: implementing JOIN using Django ORM?

4 Answers4

Linked

Related