[Django]-Filtering by calculated (extra()) fields

by

0👍

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.

👤Manoj Govindan

0👍

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.

👤rewritten

Leave a comment