Hi,
does anyone know a way of filtering a QuerySet by a field that was added through the extra() method?
For example, this is what I would like to do
_list_items = ListItem.objects.filter(list=1).extra(select=
'SELECT value AS "type" FROM list_item_optional
WHERE list_item_optional.list_optional_id=1
AND list_item_optional.list_item_id = list_item.id')
_list_items = _list_items.filter(type='A')
I know the above situation is not allowed, but what would be the workaround in Django?
In the same way, the raw SQL way of doing this is not allowed as well, e.g.:
SELECT fieldA, fieldB, (SELECT blahblah from tableY WHERE id=1) AS "bla" FROM tableX WHERE "bla" = 'x'
Filtering by a calculated field just is not allowed in Postgres... workarounds anyone? The problem gets larger because by SQL statement (QuerySet retrieves) are dynamic and only defined at runtime.
Cheers
Attaching the models:
class List(models.Model):
account = models.ForeignKey(Account)
created_date = models.DateTimeField(auto_now_add=True)
created_by = models.ForeignKey(AccountUser, related_name='x10')
updated_date = models.DateTimeField(null=True, blank=True)
updated_by = models.ForeignKey(AccountUser, null=True, blank=True, related_name='x11')
is_deleted = models.BooleanField(default=False)
name = models.CharField(max_length=50)
objects_active = DeletedManager()
class Meta:
db_table = u'list'
class ListItem(models.Model):
list = models.ForeignKey(List, db_index=True)
key = models.CharField(max_length=50)
name = models.CharField(max_length=100)
class Meta:
db_table = u'list_item'
unique_together = ("list", "key")
class ListOptional(models.Model):
list = models.ForeignKey(List, db_index=True)
name = models.CharField(max_length=50)
is_searchable = models.BooleanField()
class Meta:
db_table = u'list_optional'
class ListItemOptional(models.Model):
list_item = models.ForeignKey(ListItem, null=True, db_index=True)
list_optional = models.ForeignKey(ListOptional, null=True)
value = models.CharField(max_length=100)
class Meta:
db_table = u'list_item_optional'