In Django, how to make a join query with matching conditions in addition to the foreign key?

Question

We have a hand-written SQL query for proof of concept and hope to implement the function with the Django framework.

Specifically, Django's QuerySet usually implements a join query by matching the foreign key with the primary key of the referred table. However, in the sample SQL below, we need additional matching conditions besides the foreign key, like the eav_phone.attribute_id = 122 in the example snippet below.

...
  left outer join eav_value as eav_postalcode
    on t.id = eav_postalcode.entity_id and eav_phone.attribute_id = 122
...

Questions:

We wonder if there is a way to do it with Python, Django framework, or libraries.

We also wonder if other programming languages have any mature toolkits we can refer to as a design pattern. So we highly appreciate any hints and suggestions.

Backgrounds and Technical Details:

The scenario is a report that consists of transactions with customized columns by Django-EAV. This library implements the eav_value table consisting of columns of different data types, e.g. value_text, value_date, value_float, etc.

We forked an internal repository of Django-EAV and upgraded it to Python 3, so we can use any up-to-date Python features, although we are not using Django-EAV2. As far as we know, the new version, EAV2, follows the same database schema design.

So, the application defines a product with attributes in specific data types, and we referred it as metadata in this question, e.g.:

attribute_id	slug	datatype
122	postalcode	text
123	phone	text
...	...	e.g. date, float, etc. ...

One transaction is one entity, and the eav_value table contains multiple records with the matching entity_id corresponding to the different customized attributes. And we want to build a dynamic QuerySet according to the metadata to assemble the customized columns with left outer join similar to the sample SQL query below.

select 
    t.id, t.create_ts
  , eav_postalcode.value_text as postalcode
  , eav_phone.value_text as phone
from
  (
    select * from transactions
    where product_id = __PRODUCT_ID__
  ) as t
  left outer join eav_value as eav_postalcode
    on t.id = eav_postalcode.entity_id and eav_phone.attribute_id = 122
  left outer join eav_value as eav_phone
    on t.id = eav_phone.entity_id and eav_phone.attribute_id = 123
;

Answer 1

We followed @NickODell's hint on FilteredRelation, and our tentative solution looks like the below snippet:

transaction_eav = transaction.annotate(
  eav_postalcode=FilteredRelation('eav_values', condition=Q(eav_values__attribute_id=22))
)

transaction_eav = transaction_eav.annotate(
    value_postalcode=F('eav_postalcode__value_text)}
)

We are new to Django ORM so please point out if the sample code above contains any low-efficient or non-standard flaws.

Many thanks to all for the great suggestions!