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?

xssChauhan · Accepted Answer · 2017-03-28 04:49:05Z

66

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, 2017 at 4:49

xssChauhan

2,8583 gold badges27 silver badges37 bronze badges

answered Nov 8, 2010 at 15:54

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

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

2 Comments

Bheid Over a year ago

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.

Kurt Bourbaki Over a year ago

How can this work if question_id is just an integer, and Answer does not provide a models.ForeignKey field to Question?

David Maness · Accepted Answer · 2019-02-15 20:40:52Z

49

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, 2019 at 20:40

David Maness

9112 bronze badges

answered Nov 8, 2010 at 15:47

baklarz2048

11k2 gold badges33 silver badges38 bronze badges

1 Comment

talz Over a year ago

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.

Saurav Biswas · Accepted Answer · 2015-12-29 09:15:44Z

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, 2015 at 9:15

Saurav Biswas

2992 silver badges3 bronze badges

1 Comment

Shmack Over a year ago

id is a built in function now, may not have been in 2015

dilip kumar · Accepted Answer · 2020-07-21 16:10:01Z

4

#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

edited Jul 21, 2020 at 16:10

answered Jul 21, 2020 at 16:04

dilip kumar

593 bronze badges

Collectives™ on Stack Overflow

Django: implementing JOIN using Django ORM?

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related