Performing a right join in django

Question

Here are my models

class Student:
    user  = ForeignKey(User)
    department = IntegerField()
    semester = IntegerField()
    ...

class Attendance:
    student = ForeignKey(Student)
    subject = ForeignKey(Subject)
    month = IntegerField()
    year = IntergerField()
    present = IntegerField() 
    total = IntegerField()

students = Student.objects.filter(semester=semester)

How can I perform a right join between Student and Attendance models, so that I can get a queryset with all of the students and attendances` if exists for a student, else null?

The documentation mentions left joins but not right joins.

Do you mean to say fetch all students who have an attendance record? — karthikr, Dec 16 '14 at 17:18
no, fetch all students. fetch attendance for a student if it exists, else null. — rjv, Dec 16 '14 at 17:20
well,, then it is a simple `Student.objects.select_related('attendance')` - unless I am missing something — karthikr, Dec 16 '14 at 17:21

score 12 · Answer 1 · answered Aug 22 '18 at 13:24

12

change left join for table subject

queryset.query.alias_map['subject'].join_type = "RIGHT OUTER JOIN"

answered Aug 22 '18 at 13:24

madjardi

5,649
2
37
37

Gill Bates · Accepted Answer · 2014-12-16T17:31:09.280

You can use such query:

queryset = Student.objects.all().select_related('attendance_set')
student = queryset[0]
# all attendances for the student
attendances = student.attendance_set.all()

With select_related you JOIN'ing Attendance table. Django does not have explicit join ORM method, but it does JOIN internally when you call select_related. Resulting queryset will contain all Student's with joined attendances, and when you will call attencande_set.all() on each student - no additional queries will be performed. Check the docs for _set feature.

If you want to query only those students who has at least one attendance, you can use such query:

from django.models import Count
(Student.objects.all()
                .select_related('attendance_set')
                .annotate(n_attendances=Count('attendance_set'))
                .filter(n_attendances__gt=0))

Watch out as this won't work - you can't select_related on a related manager (`attendance_set`) and the field name will actually be `attendance` for the `Count`. You need to use `prefetch_related` instead: ```Student.objects.all().prefetch_related('attendance_set').annotate(n_attendances=Count('attendance')).filter(n_attendances__gt=0)``` — Ben, Feb 17 '17 at 02:27

Ricardo Garcia Saez · Answer 3 · 2023-03-22T02:57:03.880

0

The right way is using fk_set__isnull=false as show below:

class Father(Model):
    name = ...

class Child(Model):
    father = ForeignKey('Father', on_delete=..., related_name='childs')

>>> fathers_with_childs = Father.objects.filter(childs__isnull=False).all()

this will gave you a query like:

"SELECT * FROM father INNER JOIN child ON (father.id = child.father_id) WHERE child.id IS NOT NULL"

edited Mar 22 '23 at 02:57

answered Mar 22 '23 at 02:49

Ricardo Garcia Saez

11
3

Performing a right join in django

3 Answers3

Linked