Issue
I have two models and I need to do a left join operation I tried some solutions, but wihout success yet
Models
class SmdRepairIn(models.Model):
sum_ymd = models.DateField(blank=True, null=True)
line_nm = models.CharField(max_length=20, blank=True, null=True)
model_code = models.CharField(max_length=20, blank=True, null=True)
class SmdRepairOut(models.Model):
repair_in_id = models.CharField(max_length=20, blank=True, null=True)
repairman_out = models.CharField(max_length=30, blank=True, null=True)
SMDRepairIn.id == SmdRepairOut.repair_in_id
I want to retrieve the correspondent query result:
select A.*, B.repairman_out
from SmdRepairIn as A
left join (select repairman_out from SmdRepairOut) B on (A.id = B.repair_in_id )
How to do it in django queryset?
The expected result should be:
id, sum_ymd, line_nm, model_code, repairmain_out
Solution
You should use Foreignkey
already in the model:
class SmdRepairIn(models.Model):
sum_ymd = models.DateField(blank=True, null=True)
line_nm = models.CharField(max_length=20, blank=True, null=True)
model_code = models.CharField(max_length=20, blank=True, null=True)
class SmdRepairOut(models.Model):
repair_in = models.ForeignKey(SmdRepairIn, blank=True, null=True, on_delete=models.SET_NULL)
repairman_out = models.CharField(max_length=30, blank=True, null=True)
(Note: you should rethink whether there is a SmdRepairOut without a SmdRepairIn. If not, the ForeignKey line should be repair_in = models.ForeignKey(SmdRepairIn, on_delete=models.CASCADE)
)
This will allow you to query for the selections you need. Examples see here: https://docs.djangoproject.com/en/3.2/topics/db/examples/many_to_one/
Some examples:
SmdRepairIn.objects.all().values_list('sum_ymd', 'line_nm', 'model_code', 'smdrepairout__repairman_out')
This is close to what you were asking for. It uses a reverse lookup smdrepairout__repairman_out
.
Other way around:
SmdRepairOut.objects.all().values_list('repairman_out', 'repair_in__sum_ymd')
Answered By - Nechoj
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.