I'm trying to create an Django ORM query to replace a really messy raw SQL query i've written in the past but i'm not sure if Django ORM can let me do it. I have three tables:
What I'm hoping to be able to do with the ORM is create an output as if i've queryied one single table like this:
Is such a thing possible with the ORM?
EDIT:
The models are:
class Contact(models.Model):
company = models.ForeignKey(Company, on_delete=models.PROTECT, null=True, blank=True)
class CustomField(models.Model):
name = models.CharField(max_length=100)
company = models.ForeignKey(Company, on_delete=models.PROTECT, null=False, blank=False)
class ContactCustomFieldValue(models.Model):
custom_field = models.ForeignKey(CustomField, on_delete=models.PROTECT, related_name='contact_values')
contact = models.ForeignKey(Contact, on_delete=models.PROTECT, related_name='custom_field_values', null=True)
value = models.TextField(null=True)
Solved this one in large part thanks to Ken from the Django forums.
The solution he provided looked like this:
subquery1 = Subquery(ContactCustomFieldValue.objects.filter(contact=OuterRef('id'), custom_field_id=1).values_list('value'))
subquery2 = ...
subquery3 = ...
subquery4 = ...
contact_list = Contact.objects.filter(...).annotate(field1=subquery1, ...)
I've built on it to fit my needs but as a starting point this was perfect