How can i change this query to ORM?

Question

Hi i have two models like this,

class Sample(models.Model):
    name = models.CharField(max_length=256)  ##
    processid = models.IntegerField(default=0)  # 


class Process(models.Model):
    sample = models.ForeignKey(Sample, blank=False, null=True, on_delete=models.SET_NULL, related_name="process_set")
    end_at = models.DateTimeField(null=True, blank=True) 

and I want to join Sample and Process model. Because Sample is related to process and I want to get process information with sample .

SELECT sample.id, sample.name, process.endstat
FROM sample
INNER JOIN process 
ON sample.processid = process.id 
AND process.endstat = 1;

(i'm using SQLite)

I used

sample_list = sample_list.filter(process_set__endstat=1))

but it returned

SELECT sample.id, sample.name
FROM sample
INNER JOIN process 
ON (sample.id = process.sample_id) 
AND process.endstat = 1)

This is NOT what I want.

How can i solve the problem?

Answer 1

This should work for you

Process.objects.filter(end_at=1).values('sample__id','sample__name','end_at') 

.values() method returns selective table fields.

Answer 2

I'm assuming sample_list = Sample.objects.

When you are filtering a model, only the fields defined in the model are selected. In your example, id and processid. If you want to retrieve values from related models as a single record you need to use values or values_list. To get the desired query you have to do this

sample_list = sample_list.filter(process_set__endstat=1).values('id', 'name', 'process__endstat')

Btw, Django does JOIN on the foreign key field. So, you can't get ON sample.processid = process.id since processid is not a ForeignKey field.

Reference: https://docs.djangoproject.com/en/4.0/ref/models/querysets/#values

Answer 3

I found JOIN not on foreign key field in django.

sample_list = sample_list.filter(processid__in=Process.objects.filter(endstat=1)

I used the medthod of Django-queryset join without foreignkey