CODEWITHSUNDEEP
pythondjangodjango-queryset
Shoaib Ijaz
Shoaib Ijaz

Reputation: 5587

How to create complex Left JOIN in Django

I am trying to create multi Model Left join using django

Here is my model

class LookupTiming(models.Model):
    day = models.CharField(max_length=7)
    time_1 = models.TimeField()
    time_2 = models.TimeField()

    class Meta:
        db_table = u'lookup_timing'

class Streets(models.Model):
    name = models.CharField(max_length=50)
    point = models.GeometryField(null=True, blank=True)
    objects = models.GeoManager()

    class Meta:
        db_table = u'streets'

    def __unicode__(self):
        return '%s' % self.name


class StreetTimings(models.Model):
    street= models.ForeignKey(Streets)
    lookuptiming = models.ForeignKey(LookupTiming)
    class Meta:
        db_table = u'street_timings'

This is query that i have created 

Streets.objects.filter(streettimings__isnull=True).filter(streettimings__lookuptiming__isnull=True).values('id')

This is sql output of above query

SELECT "streets"."id" FROM "streets"
LEFT OUTER JOIN "street_timings" ON ( "streets"."id" = "street_timings"."street_id" ) 
LEFT OUTER JOIN "street_timings" T3 ON ( "streets"."id" = T3."street_id" )
WHERE ("street_timings"."id" IS NULL AND T3."lookuptiming" IS NULL)

But i want following query

select st.id from streets st
LEFT JOIN street_timings timing on timing.street_id = st.id 
LEFT JOIN lookup_timing lt on lt.id = timing.lookuptiming_id

So problem is that, the django query total ignore lookuptiming Model. I want to create following joins

Streets(id) = StreetTimings (street_id)

LookupTiming (id) = StreetTimings (lookuptiming_id)

How can i create left join for multiple models?

Thank you

Upvotes: 2

Views: 1045

Answers (1)

Mp0int
Mp0int

Reputation: 18727

Your problem is about your Django Query

Streets.objects.filter(streettimings_isnull=True).filter(streettimings_lookuptiming__isnull=True).values('id')

streettimings_isnull=True will return Streets which do not have a StreetTiming relation. So, using streettimings_lookuptiming__isnull=True is meaningless since filtered Street objects do not have a related StreetTiming relation. Try following

LookupTiming.object.values_list("streettimings__street__id", Flat=True)

If it do not create expected query, yuou may try following

LookupTiming.object.filter(streettimings__street__isnull=False).values_list("streettimings__street__id", Flat=True)

That will return you all Street.Id values. You have to use reverse relations to create the query you wanted.

Update: You must use the relations in fitlering (or some other parts o the Query Api) so Django will create joins. If you do not provide any filtering data, django will not create joins since it would not require them.

You can try following, but even it will create a WHERE clause.

Streets.objects.filter(streettimings_lookuptiming__id__gt=0).values('id')

Upvotes: 3

Related Questions