filtering by calculated (extra()) fields

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.ForeignK开发者_运维知识库ey(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'

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

The WHERE clause is a combination of two conditions: list_item_optional.list_optional_id=1 and list_item_optional.list_item_id = list_item.id. Can you not apply the filter conditions directly rather then selecting them as extra and then filtering? I am writing this answer without seeing the models in question. Providing the source code for the models would help.

Try this:

_list_items = ListItem.objects.filter(list=1).extra(select={'type':
    '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.extra(where="`type` = 'A'")

The trick is to use the extra()'s where argument, but in a subsequent application of extra() so the constructed query already has the new field.