DJango join on two fields

Basically I have three models in django:

Car(models.Model):
   id = models.BigAutoField(primary_key=True)
   company_id = models.ForeignKey(Company, models.DO_NOTHING)
   type_id = models.ForeignKey(Type, models.DO_NOTHING)'

Type(model.Model):
    id = models.BigAutoField(primary_key=True)
    size = models.CharField(max_length=1024, blank=True, null=True)

Vendor(model.Model):
    id = models.BigAutoField(primary_key=True)
    company_id = models.ForeignKey(Company, models.DO_NOTHING)
    type_id = models.ForeignKey(Type, models.DO_NOTHING)
    vendor_priority = models.CharField(max_length=1024, blank=True, null=True)

i also have the code

my_cars = Car.objects.filter(cars_filter)

What I wish to do is to join the information from ventor to my_cars so that I could access the vendor_priority information, I would have to create a join on type_id and company_id, keeping my existing filters. In SQL what I want to do would akin to:

select * from cars left join vendor on cars.company_id = vendor.company_id 
and cars.type_id = vendor.type_id 
where cars_filter = cars_filter;

You can do it like this:

query = Car.objects.filter(cars_filter).annotate(vendor_priority=Subquery(Vendor.objects.filter(company_id=OuterRef('company_id'), type_id=OuterRef('type_id')).values('vendor_priority')[:1])) #ensure only first value is fetched (if any)

Adjust column name if necessary.

Back to Top