How to Left Join Model (or) Table in DRF

Question

I have two models Employee and AccessDr.

Employee Model=>

class Employee(models.Model):
    empid = models.CharField(max_length=20, unique=True)
    empname = models.CharField(max_length=50)
    phone = models.CharField(max_length=20, blank=True)
    token = models.ForeignKey(
        Company, to_field='token', on_delete=models.CASCADE)

    def __str__(self):
        return "%s" % (self.empid)

    class Meta:
        managed = True
        db_table = 'employee'

AccessDr Model=>

class AccessDr(models.Model):
    empid = models.ForeignKey(
        Employee, to_field='empid', on_delete=models.CASCADE)
    _date = models.DateField()
    _time = models.IntegerField()
    device = models.CharField(max_length=5)
    takey = models.CharField(max_length=3, default='00')
    token = models.ForeignKey(
        Company, to_field='token', on_delete=models.CASCADE)

    def __str__(self):
        return "%s %s" % (self.empid, self._date)

    class Meta:
        ordering = ['_date']
        managed = True
        db_table = 'tstrdoor'

I would like to return object when the request to AccessDr like SQL left join, Example json=>

{
empid:'',
empname:'',  <=this one from employee model
phone:'',  <=this one from employee model
_date:'',
_time:'',
.
.


}

How can I achieve that one?

Answer 1

have a look at this LEFT JOIN Django ORM

read this as well https://docs.djangoproject.com/en/2.2/topics/db/queries/#spanning-multi-valued-relationships

you can print query to look at how it translates to SQL by

Employes=AccessDr.objects.values('employee__empname','employee__phone')
print(Employes.query)

Are you using serializers in the views? in this case, you could create a serializer field which will put agreement data as an array like this

class CustomerSerializer(serializers.ModelSerializer):
agreements = AgreementSerializer(many=True, read_only=True)


class Meta:
    model = Customer
    fields = [
        'id',
        'username',
        'mail',
        'fName',
        'lName',
        'fNameEng',
        'lNameEng',
        'personalId',
        'phone',
        'crmId',  # "ID": "20995",
        'agreements',
    ]
    depth = 1

Are you using serializers in the views? in this case, you could create serializer field which will put agreement data as an array like this

{
  "id": 985,
  "username": null,
  "mail": "undefined",
  "fName": "Merab",
  "lName": "Dasv",
  "fNameEng": "Merab",
  "lNameEng": "Dasv",
  "personalId": "01022342346003629",
  "phone": "5912324234282331",
  "crmId": 1439,
  "agreements": [
    {
      "id": 884,
      "signDate": "2015-04-16",
      "accountDate": "2015-05-01",
      "amount": 0,
      "comBalance": -1445.0,
      "status": 1,
      "details": [
        {
          "square": 32.38,
          "amount": 35.0,
          "object": {
            "id": 578,
            "object": 2,
            "block": 1,
            "floor": 19,
            "flat": "7",
            "cadastre": "05.24.04.055.01.563",
            "square": 32.38,
            "pCounter": 25915123146,
            "wCounter": 104412312435,
            "accountDate": "2015-04-01T00:00:00",
            "comBalance": -1445.0,
            "comDeptAmount": 1895.0,
            "rentDate": null,
            "active": 1,
            "finaAmount": 0,
            "crmId": 0
          }
        },

      ]
    },

  ]
}