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:


id company_id
1 1
2 2


id field_name
1 name
2 email
3 phone
4 name
5 email
6 phone


id contact_id custom_field_id field_value
1 1 1 John
2 1 2
3 1 3 123456
4 2 4 Jane
5 2 5
6 2 6 234567

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:

id name email phone
1 John 123456
2 Jane 234567

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)

Is such a thing possible with the ORM?

Hey there!
You want that the goes as the column header of the results in the ContactCustomFieldValue for all companies that will match a id?

Yes, that’s right. I realise the final output I didn’t include the custom field names as the column names but I have updated this to be more clear :slight_smile:

I’ve never done such thing neither in SQL or Django ORM.
But maybe someone can help!

You’re not going to be able to produce a dynamically stable single query to generate that output because of the many-to-many relationship established by the ContactCustomFieldValue model.

The problem here is that each company can have a different and non-overlapping set of CustomFields. How do you want to handle it if Company “A” has a completely different set of fields than Company “B”?

(Side note: If your response to this question is that it can’t happen - that each Company is restricted to the same set of fields, then my response to that is that your models are improperly designed and you need to rework your models.)

Hi Ken,

You’re correct, each company/contact can and does have different fields. But for this particular use case we only need to collect the 4 fields that every company/contact has by default… So while they may have others we don’t need to be entirely concerned if they do/don’t exist (for this particular question/use case)

So in this case you’re only looking to retrieve 4 specific, known, entries for every row? You’re not looking to include values outside that 4? That makes this a significantly easer problem to solve.

Yes, exactly. 4 known fields that every contact will have by default. Anything else they have doesn’t matter for this, we just want those 4

I’m thinking of something like this - there may be a mistake or ten here since I’m winging it -

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, ...)

(You can get a lot fancier/sophisticated than this if you want - I’d consider this a starting point and not an ending point.)

1 Like

Oh brilliant, I started playing with this yesterday and it’s already a VAST improvement over what I had before. Thanks so much Ken!