Django ORM: getting rows based on maximum column value
I have a Marketorders class that contains information about individual market orders and they are collected in market snapshots (class snapshot presented). Each order can be displayed in multiple images, with the last line being of course relevant.
class Marketorders(models.Model):
id = models.AutoField(primary_key=True)
snapid = models.IntegerField()
orderid = models.IntegerField()
reportedtime = models.DateTimeField(null=True, blank=True)
...
class Snapshot(models.Model):
id = models.IntegerField(primary_key=True)
...
What I am doing is getting all orders across multiple snapshots for processing, but I only want to include the very last row for each order. In SQL, I would just do:
SELECT m1.* FROM marketorders m1 WHERE reportedtime = (SELECT max(reportedtime)
FROM marketorders m2 WHERE m2.orderid=m1.orderid);
or better yet with a join:
SELECT m1.* FROM marketorders m1 LEFT JOIN marketorders m2 ON
m1.orderid=m2.orderid AND m1.reportedtime < m2.reportedtime
WHERE m2.orderid IS NULL;
However, I just can't figure out how to do this with the Django ORM. Is there a way to do this without raw SQL?
EDIT: just to clarify the problem. Let's say we have the following markets (not counting anything nonessential and using only orderid, reported times):
1, 09:00:00
1, 10:00:00
1, 12:00:00
2, 09:00:00
2, 10:00:00
How do I get the next set with ORM?
1, 12:00:00
2, 10:00:00
If I understand correctly, you need a list of Marketorder objects, which contains each Marketorder with the most messages per order
Something like this should work (disclaimer: didn't test it directly):
m_orders = Marketorders.objects.filter(id__in=(
Marketorders.objects
.values('orderid')
.annotate(Max('reportedtime'))
.values_list('id', flat=True)
))
To check the documentation:
http://docs.djangoproject.com/en/dev/topics/db/aggregation/
Edit: This should get the single Marketorder with the largest message for a specific order.
order = (
Marketorders.objects
.filter(orderid=the_orderid)
.filter(reportedtime=(
Marketorders.objects
.filter(orderid=the_orderid)
.aggregate(Max('reportedtime'))
['reportedtime__max']
))
)
source to share
You have a good reason why you are not using ForeignKey or (in your case better) ManyToManyField . These fields represent the relational structure of ur models.
Also, there is no need to declare the pk field id. if there is no pk, django adds an id.
In the code below, you can specify orm requests, for example:
m1 = Marketorder()
m1.save() # important: primary key will be added in db
s1 = Snapshot()
s2 = Snapshot()
s1.save()
s2.save()
m1.snapshots.add(s1)
m1.snapshots.add(s2)
m1.snapshots.all()
m1.snapshots.order_by("id") # snapshots in relations with m1
# ordered by id, that is added automatically
s1.marketorder_set.all() # reverse
so for your request:
snapshot = Snapshot.objects.order_by('-id')[0] # order desc, pick first
marketorders = snapshot.marketorder_set.all() # all marketorders in snapshot
or compact:
marketorders = Snapshot.objects.order_by('-id')[0].marketorder_set.all()
models.py:
class Snapshot(models.Model):
name = models.CharField(max_length=100)
class Marketorder(models.Model):
snapshots = models.ManyToManyField(Snapshot)
reportedtime = models.DateTimeField(auto_now= True)
By convention, all model class names are unique. Django makes it plural in different places automatically.
more on queries (filtering, sorting, complex search queries) . be sure to read.
source to share