We have an internal fork of EAV-Django and have upgraded it to Python 3, so our internal fork is similar to the new repository of django-eav2.
In the application, we need to join the transaction table with 1) the eav table's email records and 2) the customer table. We use Django annotate() function to automatically generate the sample SQL code below, and please refer to the join clauses as:
...
left outer join `eav_value` eav_email on
...
left outer join `app_customer` on
...
The problem is a naming conflict: we get a column named email from the eav_value table, and the customer table also contains a column named email. Therefore, when intending to sort by the EAV's email attribute, the clause order by email asc triggers an error saying "Column 'email' in order clause is ambiguous".
Question:
We could rename the EAV's email to eav_email but the change will trigger other changes on the frontend, because the frontend component relies on the column name email or customer.email.
So, we wonder if there is any way to give a parent annotation to EAV attribute, e.g. in SQL query, we do instead of eav_email.value_text as email, we can do eav_email.value_text as eav.email,. If yes, how to translate this idea into Django context?
If the above thoughts are not working, we will also highly appreciate any hints and suggestions in other directions.
select
`app_transaction`.`id`,
eav_email.`value_text` as `email`,
`app_customer`.`email`
from
`app_transaction`
left outer join `eav_value` eav_email on
(`app_transaction`.`id` = eav_email.`entity_id`
and (eav_email.`entity_ct_id` = 28)
and (eav_email.`attribute_id` = 56))
left outer join `app_customer` on
(`app_transaction`.`id` = `app_customer`.`transaction_id`)
where
`app_transaction`.`product_id` = 709
-- order by
-- `email` asc
--
-- If not commented out, the `order by` clause triggers the below error:
-- SQL Error [1052] [23000]: (conn=14) Column 'email' in order clause is ambiguous
;