select *
from sample
join process
on sample.processid = process.id
where (processid) in (
    select max(processid) as processid
    from main_sample
    group by serialnumber
) 
ORDER BY sample.create_at desc;

models.py

class Sample(models.Model):
    processid = models.IntegerField(default=0) 
    serialnumber = models.CharField(max_length=256)  ##
    create_at = models.DateTimeField(null=True)

class Process(models.Model):
    sample = models.ForeignKey(Sample, blank=False, null=True, on_delete=models.SET_NULL)

Hi I have two models and I need to change this SQL query to Django ORM, Python code.

I need to retrieve the latest Sample(by processid) per unique serial number.

for example,

=> after RUN query

How can I change the SQL query to ORM code? how can i change the subquery to ORM?

Thanks for reading.

CodePudding user response：

EDIT: To also order by a column that is not one of the distinct or retrieved columns you can fall-back on subqueries. To filter by a single row from a subquery you can use the syntax described in the docs here

from django.db.models import Subquery, OuterRef
subquery = Subquery(Sample.objects.filter(
    serialnumber=OuterRef('serialnumber')
).order_by(
    '-processid'
).values(
    'processid'
)[:1])
results = Sample.objects.filter(
    processid=subquery
).order_by(
    'create_at'
)

When using PostgreSQL you can pass fields to distinct to get a single result per a certain column, this returns the first result so combined with ordering will do what you need

Sample.objects.order_by('serialnumber', '-processid').distinct('serialnumber')

If you don't use PostgreSQL. Use a values query of the column that should be unique and then annotate the queryset with the condition that should group the values, Max in this case

from django.db.models import Max
Sample.objects.order_by(
    'serialnumber'
).values(
    'serialnumber'
).annotate(
    max_processid=Max('processid')
)

CodePudding user response：

I think this is what you need:

1. If want multiple related objects

samples = Sample.objects.prefetch_related('process').group_by('serialinumber')

2. If you want related objects for only one object

samples = Sample.objects.filter(id=1).select_related('process').group_by('serialinumber')