Search code examples
python-3.xdjangodjango-ormpython-3.8

Django ORM query with user defined fields


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:

contacts
enter image description here

custom_fields
enter image description here

custom_field_data
enter image description here

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:
enter image description here

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)  

Solution

  • 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