Django LEFT JOIN - One to Many

Question

I have the following models:

class Employee(models.Model):

    def getFullname(self):
        return '{}, {} {}'.format(self.empLastname,self.empFirstname, self.empMiddlename)

    empID = models.CharField(primary_key=True, max_length= 50, null=False)
    empLastname = models.CharField(max_length=50)
    empFirstname = models.CharField(max_length=50)
    empMiddlename = models.CharField(max_length=50, null=True)
    fullname = property(getFullname)

    def __str__(self):
       return self.fullname

class Picture(models.Model):
    empID = models.ForeignKey(Employee, related_name="pictures", to_field='empID', on_delete = models.CASCADE)
    picture = models.ImageField('Uploaded image')
    isDef = models.BooleanField(default=False)

I want to query for all employee where isDefault=True in the Picture model. But not all Employee had pictures. So basically I want to query all Employee which isDefault=True and all Employee that have no picture(s).

My query is look like this:

SELECT manager_employee.`*`, manager_picture.*
FROM manager_employee
LEFT JOIN manager_picture ON (manager_employee.empID = manager_picture.empID_id
AND manager_picture.isDef)

serializer.py

class PictureSerializer(serializers.ModelSerializer):
      class Meta:
            model = Picture
            fields = (
                  'empID',
                  'picture',
                  'isDef'
            )

class EmployeeListSerializer(serializers.ModelSerializer):
      pictures = PictureSerializer(many = True, read_only = True)

      class Meta:
            model = Employee
            fields = (
                  'empID',
                  'empLastname',
                  'empFirstname',
                  'empMiddlename',
                  'fullname',
                  'pictures'
            )

This query gave me the correct result.. The problem is on how to do it in Django..

Suppose I have a record of Employee model

=============================================================================
empID    | empLastname    | empFirstname  | empMiddlename | fullname        |
=============================================================================
  1      | Doe            | John          | Smith         | Doe, John Smith |
-----------------------------------------------------------------------------
  2      | Potter         | Harry         | Moon          | Potter, Harry ..|
-----------------------------------------------------------------------------
  3      | Aaaa           | Abbb          | Accc          | ...             |
-----------------------------------------------------------------------------
  4      | Baaaa          | Bbbb          | Bccc          | ...             |
-----------------------------------------------------------------------------

now in Picture model

================================================
empID     | Picture                   | isDef  |
================================================
  1       | Pic1.jpg                  | 0      |
------------------------------------------------
  1       | Pic2.jpg                  | 0      |
------------------------------------------------
  1       | Pic3.jpg                  | 1      | *which makes this as default*
------------------------------------------------
  2       | emp2Pic1.jpg              | 1      | *which makes this as default*
------------------------------------------------
  2       | emp2Pic2.jpg              | 0      |
------------------------------------------------
  4       | emp4Pic1.jpg              | 0      |
------------------------------------------------
  4       | emp4Pic2.jpg              | 1      | *which makes this as default*
------------------------------------------------

now when i try this SQL:

SELECT manager_employee.`*`, manager_picture.*
    FROM manager_employee
    LEFT JOIN manager_picture ON (manager_employee.empID = manager_picture.empID_id
    AND manager_picture.isDefault)

this give me a result:

=========================================================================
empID | empLastname | empFirstname | empMiddle | Picture      | isDef   |
 1    | Doe         | John         | Smith     | Pic2.jpg     | 1       |
 2    | Potter      | Harry        | Moon      | emp2Pic1.jpg | 1       |
 3    | Aaaa        | Abbb         | Accc      | (null)       | (null)  |
 4    | Baaaa       | Bbbb         | Bccc      | emp4Pic2.jpg | 1       |
-------------------------------------------------------------------------

How to I implement this in Django?

Please somebody help me.. TIA..

Answer 1

Django provides you the ORM so you dont have to write your SQL. Check this : Django queryset API reference .

Also if you want to check how your Django query looks in SQL. you can try like this in django shell terminal:

>>> queryset = MyModel.objects.all()
>>> print queryset.query
SELECT "myapp_mymodel"."id", ... FROM "myapp_mymodel"