CODEWITHSUNDEEP
pythondjango
Akhil Sahu
Akhil Sahu

Reputation: 637

Perform JOIN in tables django

Perform JOIN on in django fetching related date in reverse relationship. There are three models.

Following is code for models 

class Question(models.Model):
	title = models.CharField(max_length=255 )
	description = models.TextField(max_length=300)
  

class Quiz(models.Model):
	name = models.CharField(max_length=225,blank=False )
	quiz_type =models.IntegerField(choices=QUIZ_TYPE,default=0)
    questions = models.ManyToManyField( Question, through='QuestionQuiz', related_name="quiz_question") 
	categories= models.ManyToManyField(Category,through='CategoryQuiz',related_name='quiz_category')		
 

class QuestionQuiz(models.Model):
	quiz = models.ForeignKey(Quiz,on_delete=models.CASCADE)
	question = models.ForeignKey(Question,on_delete=models.CASCADE)	
	correct =models.DecimalField(max_digits=4, decimal_places=3)
    incorrect= models.DecimalField(max_digits=4, decimal_places=3) 

	class Meta:
		unique_together = ('quiz','question')

In this the questions are added to the quiz using model Question Quiz.

Here , QuizQuestion has foreign key relationship with the question. I need to fetch all from question JOIN and records from QuestionQuiz with a particular quiz_id.

Suppose quiz_id =3 Then I will fetch all questions with correct and incorrect. if that quiz id is added to the question then it will display correct incorrect else these would be blank.

question_id | title|description|correct|incorrect|quesquizid
1 | Q1 |Q1desc |2 | -2 | 1 2 | Q2 |Q2desc | | |

ques_id =1 added to quiz_id=3 .ques_id=2 not added to quiz_id=3.So, correct incorrect are blank.

I tried following but it fetches all question and related quizes scores irrespective of their occurrence in current quiz :

Question.objects.prefetch_related('questionquiz_set').all()

The result should be similar to following query

Select * from question as qs LEFT JOIN questionquiz as qq on (qq.question_id = qs.id AND qq.id=3)

Please check the result of the query:

enter image description here

Upvotes: 1

Views: 50

Answers (1)

Lucky
Lucky

Reputation: 575

I think prefetch_related along with Prefetch may get you the desired result.

q = Question.objects.prefetch_related( Prefetch('questionquiz_set', queryset=QuestionQuiz.objects.filter(quiz=3)) ) print(q)

This may retrieve all related data along with null if exist.

Upvotes: 6

Related Questions