[Answer]-Django LEFT JOIN on not native database

by

1👍

✅

Firstly, the models need to be changed to mention the ForeignKey relationships. Next, the id fields are automatically added by Django and are redundant. Finally, it is recommended to use singular names for models. I would design them in this way:

class Platform(models.Model):
    name = models.CharField(max_length=64)
    prefix = models.CharField(max_length=2)

    class Meta:
        managed = False
        db_table = 'lu_platform'

    def __unicode__(self):
        return self.name

class Source(models.Model):
    name = models.CharField(max_length=32)

    class Meta:
        managed = False
        db_table = 'lu_sourcetype'

    def __unicode__(self):
        return self.name

class Event(models.Model):
    host_id = models.IntegerField()
    platform = models.ForeignKey(Platform)
    sourcetype = models.ForeignKey(Source)
    event_datetime = models.DateTimeField(auto_now=True)
    data = models.CharField(max_length=2048)

    class Meta:
        managed = False
        db_table = 'event'

Finally, the query would look something like this:

Event.objects.filter(platform__id=8, event_datetime__range=["2013-11-23", "2013-12-28"],sourcetype_id=1).values(
      "event_datetime", "platform__name", "host_id", "data")

I am on a train so I cannot verify the above code but it should work.

👤arocks

Leave a comment