I have two tables.
class DibbsSpiderDibbsMatchedProductFieldsDuplicate(models.Model):
nsn = models.TextField()
nsn2 = models.TextField()
cage = models.TextField()
part_number = models.TextField()
company_name = models.TextField(blank=True, null=True)
supplier = models.TextField(db_column='Supplier', blank=True, null=True) # Field name made lowercase.
cost = models.CharField(db_column='Cost', max_length=15, blank=True, null=True) # Field name made lowercase.
list_price = models.CharField(db_column='List_Price', max_length=15, blank=True, null=True) # Field name made lowercase.
gsa_price = models.CharField(db_column='GSA_Price', max_length=15, blank=True, null=True) # Field name made lowercase.
hash = models.TextField()
nomenclature = models.TextField()
technical_documents = models.TextField()
solicitation = models.CharField(max_length=32)
status = models.CharField(max_length=16)
purchase_request = models.TextField()
issued = models.DateField()
return_by = models.DateField()
file = models.TextField()
vendor_part_number = models.TextField()
manufacturer_name = models.TextField(blank=True, null=True)
product_name = models.TextField(blank=True, null=True)
unit = models.CharField(max_length=15, blank=True, null=True)
class Meta:
managed = False
db_table = 'dibbs_spider_dibbs_matched_product_fields_duplicate'
class DibbsSpiderSolicitation(models.Model):
line_items = models.IntegerField()
nsn = models.TextField()
nomenclature = models.TextField()
technical_documents = models.TextField()
purchase_request = models.TextField()
class Meta:
managed = False
db_table = 'dibbs_spider_solicitation'
What will be the equivalent django query for the inner join of two tables on the column nsn
?
My views function will be like
def inner(request,nsn):
u_m = DibbsSpiderDibbsMatchedProductFieldsDuplicate.objects.filter(nsn2__icontains=id)
c_m = DibbsSpiderSolicitation.objects.filter(nsn__icontains=id)
obj = .......................
context = {'obj':obj}
return render(request,,"a.html",context)
the queryset should return the combination of two tables according to the common nsn.
the obj should return the combination of u_m
and c_m
. If u_m
contains only one rows and c_m
contains many rows then the obj must replicate the values of u_m.
You can try some of the options:
3. Using IN query as per the following logic:
DibbsSpiderDibbsMatchedProductFieldsDuplicate.objects.filter(
nsn2__in=DibbsSpiderSolicitation.objects.filter(nsn__icontains='text_to_search').values('origin'))
First, great model names. Let's alias them: DibbsSpiderDibbsMatchedProductFieldsDuplicate is Apples; DibbsSpiderSolicitation is Oranges
inner_qs = Apples.objects.all().extra(
tables=("yourapp_oranges",),
where=("yourapp_apples.nsn=yourapp_oranges.nsn",),
)
The documentation mentions that this api will be deprecated: https://docs.djangoproject.com/en/4.0/ref/models/querysets/#extra
Under the class DibbsSpiderDibbsMatchedProductFieldsDuplicate
add:
fkey = models.ForeignKey('DibbsSpiderSolicitation')
then you can easily access their join:
obj = DibbsSpiderDibbsMatchedProductFieldsDuplicate.Objects.filter(fkey__nsn).select_related()
Now it is your choice what you wish to do with nsn2
obj = DibbsSpiderDibbsMatchedProductFieldsDuplicate.objects.extra(where = ['''SELECT *
FROM DibbsSpiderSolicitation
INNER JOIN DibbsSpiderDibbsMatchedProductFieldsDuplicate
ON DibbsSpiderSolicitation.nsn = DibbsSpiderDibbsMatchedProductFieldsDuplicate.nsn2;'''])
# or
obj = DibbsSpiderDibbsMatchedProductFieldsDuplicate.objects.raw('''SELECT *
FROM DibbsSpiderSolicitation
INNER JOIN DibbsSpiderDibbsMatchedProductFieldsDuplicate
ON DibbsSpiderSolicitation.nsn = DibbsSpiderDibbsMatchedProductFieldsDuplicate.nsn2;''')
obj = DibbsSpiderSolicitation.objects.filter(nsn__in=DibbsSpiderDibbsMatchedProductFieldsDuplicate.objects.nsn2)
Sorry, I was not able to test any.