Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multiple fields to the same DB column

We are trying to speed up our app with prefetch_related. It can follow the GenericForeignKey relations, and it can go deeper with __ but unfortunately it will fail if related model do not have such field.

Here is some example of model structure

class ModelA(models.Model):
    event_object = models.ForeignKey(SomeModelA)

class ModelB(models.Model):
    event = models.ForeignKey(SomeModelB)

class ModelC(models.Model):
    content_type = models.ForeignKey(ContentType)
    object_id = models.PositiveIntegerField()
    content_object = generic.GenericForeignKey()

So ModelC instance can point either to ModelA or ModelB. And I can use such queryset to prefetch both A and B models: ModelC.objects.all().prefetch_related('content_object') Unfortunately I also need to select the event object (SomeModelA or SomeModelB)

If I try to run

ModelC.objects.all().prefetch_related('content_object', 'content_object__event_object')

It will work if I have only ModelC instances that points to ModelA, but in ohter case it will fail because ModelB do not have the event_object field and have event instead.

This models are used in many places across the code so it's not a good idea to rename the field. So I wonder if there is a way to create an alias for a field/column.

I was trying to do like this:

class ModelB(models.Model):
    event = models.ForeignKey(SomeModelB)
    event_object = models.ForeignKey(SomeModelB, db_column='event_id', related_name='+')

to make two fields that point to the same column in DB table. However this is not working as it breaks the save method. Django creates an UPDATE SQL query where one column is placed twice and gets a DatabaseError

Is there any way to create such alias? Or maybe there is another solution to make prefetch_related not to throw an exception?

Update: In save method there is an update_fields parameter that can be used to exclude this field. However it was introduced in 1.5 and we are using 1.4. So I continue to search for an answer.

Update #2: @shx2 asked me to provide a traceback. There are 2 possible traceback. 1st - when attribute is missing on the first object:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/igor/workspace/projectname/eggs/Django-1.4.2-py2.7.egg/django/db/models/query.py", line 72, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/home/igor/workspace/projectname/eggs/Django-1.4.2-py2.7.egg/django/db/models/query.py", line 97, in __iter__
    len(self)
  File "/home/igor/workspace/projectname/eggs/Django-1.4.2-py2.7.egg/django/db/models/query.py", line 89, in __len__
    self._prefetch_related_objects()
  File "/home/igor/workspace/projectname/eggs/Django-1.4.2-py2.7.egg/django/db/models/query.py", line 570, in _prefetch_related_objects
    prefetch_related_objects(self._result_cache, self._prefetch_related_lookups)
  File "/home/igor/workspace/projectname/eggs/Django-1.4.2-py2.7.egg/django/db/models/query.py", line 1664, in prefetch_related_objects
    (attr, first_obj.__class__.__name__, lookup))
AttributeError: Cannot find 'event_object' on ModelB object, 'content_object__event_object' is an invalid parameter to prefetch_related()

And if prefetch_related parameters are valid for the first object then I get the 2nd traceback:

Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/home/igor/workspace/projectname/eggs/Django-1.4.2-py2.7.egg/django/db/models/query.py", line 72, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/home/igor/workspace/projectname/eggs/Django-1.4.2-py2.7.egg/django/db/models/query.py", line 97, in __iter__
    len(self)
  File "/home/igor/workspace/projectname/eggs/Django-1.4.2-py2.7.egg/django/db/models/query.py", line 89, in __len__
    self._prefetch_related_objects()
  File "/home/igor/workspace/projectname/eggs/Django-1.4.2-py2.7.egg/django/db/models/query.py", line 570, in _prefetch_related_objects
    prefetch_related_objects(self._result_cache, self._prefetch_related_lookups)
  File "/home/igor/workspace/projectname/eggs/Django-1.4.2-py2.7.egg/django/db/models/query.py", line 1680, in prefetch_related_objects
    obj_list, additional_prl = prefetch_one_level(obj_list, prefetcher, attr)
  File "/home/igor/workspace/projectname/eggs/Django-1.4.2-py2.7.egg/django/db/models/query.py", line 1803, in prefetch_one_level
    qs = getattr(obj, attname).all()
AttributeError: 'ModelB' object has no attribute 'event_object'
like image 281
Igor Avatar asked Mar 13 '13 12:03

Igor


People also ask

Can we have two fields with same name?

According to the standard, multiple fields with the same name are allowed. But this library just ignores every one after the first one. And no, you don't need [] at the end. Any field with any name should represent an array when present multiple times.

Can you SET multiple columns in SQL?

We can update multiple columns by specifying multiple columns after the SET command in the UPDATE statement. The UPDATE statement is always followed by the SET command, it specifies the column where the update is required.


1 Answers

It looks like a bug or oversight in django. As a workaround, you can try defining a custom manager which does a 2-stage prefetching.

from django.db import models
from django.db.models import Q
from django.contrib.contenttypes.models import ContentType

class PrefetchWorkaroundManager(models.Manager):
    def get_queryset(self):
        q = super(PrefetchWorkaroundManager, self).get_queryset()
        content_typeA = ContentType.objects.get_for_model(ModelA)
        content_typeB = ContentType.objects.get_for_model(ModelB)
        return q.filter(content_type__pk = content_typeA.id).prefetch_related('content_object', 'content_object__event_object') | \
               q.filter(content_type__pk = content_typeB.id).prefetch_related('content_object', 'content_object__event')

class ModelC(models.Model):
    ...

    objects_prefetched = PrefetchWorkaroundManager()

Each caller which wants prefetching to take place should access ModelC.objects_prefetched instead of ModelC.objects:

ModelC.objects_prefetched.filter(...)

I admit, I didn't test it, so it probably doesn't work as-is. But I believe this approach is sound.

like image 196
shx2 Avatar answered Oct 31 '22 11:10

shx2