Django ORM query with user defined fields

Using Django Using the ORM
1

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

id company_id
1 1
2 2

custom_fields

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

custom_field_data

id contact_id custom_field_id field_value
1 1 1 John
2 1 2 john@email.com
3 1 3 123456
4 2 4 Jane
5 2 5 jane@email.com
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 john@email.com 123456
2 Jane jane@email.com 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?

2

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

3

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:

4

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

5

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

6

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)

7

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.

8

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

9

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
10

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