Django左加入一对多

Question

我有以下型号：

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)

我想查询图片模型中isDefault=True处的所有员工。但并不是所有的员工都有照片。所以基本上我想查询所有没有图片的员工。在

我的问题是这样的：

^{pr2}$

在序列化程序.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'
            )

这个查询给了我正确的结果。。问题在于如何在Django中执行。。在

假设我有员工模型的记录

=============================================================================
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          | ...             |
-----------------------------------------------------------------------------

现在在图片模型

================================================
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*
------------------------------------------------

现在，当我尝试以下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)

这给了我一个结果：

=========================================================================
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       |
-------------------------------------------------------------------------

如何在Django中实现这一点？在

请谁帮帮我。。蒂娅。。在

Answer 1

Django为您提供了ORM，因此您不必编写SQL。 检查这个：Django queryset API reference。在

另外，如果您想检查Django查询在SQL中的外观。您可以在django shell终端中这样尝试：

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