How to implement `left outer join` with additional matching condition, with `annotate()` or something else?

Question

The transaction (entity) records have customized attributes in EAV format, so we are implementing a design pattern that assembles EAV data with the entities by a series of left outer join operations in SQL query, briefly as the following:

• First, we have retrieved the metadata, e.g. postalcode corresponds to attribute_id of 22, phone corresponds to 23, etc.
• Then, we follow the metadata and construct a dynamic QuerySet by adding annotate() method calls.
• Like the below SQL query, the system behaviour is to repeat left outer join on the same eav_value table; however, besides the foreign key, the matching condition also requires a specific attribute_id. So, each join assembles one attribute.

Our Question:

We tried to assemble the first attribute appending an annotate() with filter to the existing QuerySet like:

transaction.annotate(
  postalcode=F('eav_values__value_text'), 
  filter=Q(eav_values__attribute_id__exact=22)
)

The test got an error saying AttributeError: 'WhereNode' object has no attribute 'select_format'. And we believe the culprit is in the filter part because if we remove the argument, the error disappears.

So, how can we fix the issue and make the prototype run? And we are also OK to use something other than annotate() within the Django framework, not raw query.

We are new to this area of Django ORM, so we appreciate any hints and suggestions.

Technical Details:

1. SQL query for assembling EAV data with entities:

select t.`id`, t.`create_ts`
  , `eav_postalcode`.`value_text` as `postalcode`
  , `eav_phone`.`value_text` as `phone`
  -- , ... to assemble more attributes
from
    (
      -- entity: transaction, in a toy example of page size 2
      select * from `ebackendapp_transaction` where (`product_id` = __PRODUCT_ID__)
      order by `id` desc
      limit 2 offset 27000
    )  as t
    left outer join `eav_value` as `eav_postalcode` 
        on t.`id` = `eav_postalcode`.`entity_id` and `eav_postalcode`.`attribute_id` = 22
    left outer join `eav_value` as `eav_phone` 
        on t.`id` = `eav_phone`.`entity_id` and `eav_phone`.`attribute_id` = 23
    -- ... to assemble more attributes
;

2. Test steps:

transaction = Transaction.active_objects.filter(product_id=__PRODUCT_ID__).order_by('-id').all()[27000:27002].values('id', 'create_ts')
print(transaction)
# OK

transaction_eav = transaction.annotate(postalcode=F('eav_values__value_text'), filter=Q(eav_values__attribute_id__exact=22))
# transaction_eav is OK, however:
print(transaction_eav)
# got an error saying "AttributeError: 'WhereNode' object has no attribute 'select_format'"

3. Model definitions:

class Transaction(models. Model):
    transaction_id = models.CharField(max_length=100)
    product = models.ForeignKey(Product)
    ...

# From the open-source Django EAV library
# imported as `eav_models`
# 
class Value(models. Model):
    '''
    Putting the **V** in *EAV*.
    ...
    '''
    ...
    entity_id = models.IntegerField()
    entity = GenericForeignKey(ct_field='entity_ct',
                                       fk_field='entity_id')

    value_text = models.TextField(blank=True, null=True)
    value_float = models.FloatField(blank=True, null=True)
    value_int = models.IntegerField(blank=True, null=True)
    value_date = models.DateTimeField(blank=True, null=True)
    value_basicdate = models.DateField(blank=True, null=True)
    value_bool = models.NullBooleanField(blank=True, null=True)
    ...
    attribute = models.ForeignKey(Attribute, db_index=True,
                                  verbose_name=_(u"attribute"),
                                  on_delete=models.DO_NOTHING)
    ...
    

Answer 1

Tentatively, we made a working prototype with FilteredRelation(), see more in this post.

The document link targets Django v4.1 and we are currently on v3.1, though.