how can i change this SQL to Django ORM code?

Question

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.

Can you explain exactly what you want as a result and share your models? It looks like you want to retrieve the latest Sample (by processid) per unique serialnumber? Do you need the full result(s) or just some individual values/columns? — Iain Shelvington
– Iain Shelvington, Commented Mar 11, 2022 at 4:31
Thanks! I need to retrieve the latest Sample(by processid) per unique serial number!! I added the example. — Shale
– Shale, Commented Mar 11, 2022 at 4:54

Iain Shelvington · Accepted Answer · 2022-03-11 06:39:39Z

1

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')
)

edited Mar 11, 2022 at 6:39

answered Mar 11, 2022 at 5:23

Iain Shelvington

32.5k3 gold badges36 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shale Over a year ago

Really thanks for replying! I tried your code so I can get the filtered result ! but how can i get the sorted elements by 'create_at' ?? I missed the question for the sorting field. If I add the order_by('create_at') option after the ORM, it returns all the Sample elements, not filtered elements...

Shale Over a year ago

and can I ask that if i can get the other fields (create_date etc)? this ORM can only get 'serial_number' and max process id.

Iain Shelvington Over a year ago

@Shale Added an update to the answer at the top, using a subquery what you want should be possible. The update should get you the actual model instances/rows instead of just values which I think is what you want

Shale Over a year ago

Really really really thanks! you saved my life !!!! @Iain

Rajat Jog · Accepted Answer · 2022-03-11 04:30:11Z

0

I think this is what you need:

If want multiple related objects

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

If you want related objects for only one object

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

answered Mar 11, 2022 at 4:30

Rajat Jog

3271 gold badge2 silver badges11 bronze badges

2 Comments

Shale Over a year ago

Thanks for replying! But How can i change the subquery (where (processid) in ( select max(processid) ... ) ) to ORM? I need the MAX processid by each serialnumber

Rajat Jog Over a year ago

Need to write another query on first query: say samples.objects.latest(process__id) latest will give the last process__id

Collectives™ on Stack Overflow

how can i change this SQL to Django ORM code?

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related