django - get the latest one if duplicate, if not, take it

By : doniyor
Source: Stackoverflow.com
Question!

I am trying to achieve this:

I have 2 tables: Location and Rate. Location can have multiple rates. Now what I want is, look up in the database and take the rate if no duplicate foreignkey, but if duplicate, then take the latest rate.

Say, I search for rate 3, my look up goes into db and sees that one location is having only rate 3, it takes it. But another location is having two different rates, 3 and 5. then evaluate the last one, the 5 and don't take it since 3!=5

locations = Location.objects.filter(???)???? <----- help

How can I write this in query?

By : doniyor


Answers
You can use raw queries for this Suppose your models are:

class Location(models.Model):
    name = models.CharField(max_length=20)

class Rate(models.Model):
    value = models.IntegerField()
    location = models.ForeignKey('Location')

Then use this raw query (hope I did not confused with the SQL, but you can play more with yourself)

query = '''
select L1.* from core_rate as R1,core_location as L1 where L1.id = R1.location_id and value=%s and not exists (select * from core_rate as R2 where R2.location_id = R1.location_id and R2.id > R1.id)
'''
value = 3 # or anything you want
Location.raw(query,value)
By : eran


This video can help you solving your question :)
By: admin