Bug #73666
closedORM builds incorrect queries when using relations and sys_language
0%
Description
I stumbled over the following problem:
I have a data model (event) which uses sys_category.
I implemented a frontend filter with full text search. The filter uses sys_category and the full text search searches title and the title of the sys_category, if there is one.
When i do the full text search "Test" and the data "Test" has a related sys_category it can be found otherwise not. Although it is named "Test" and should be found.
Here is the relevant extbase constraint:
$searchFields = array(0 => 'title', 1 => 'category.title') foreach ($searchFields as $field) { $searchWordConstraint[] = $query->like($field, '%' . $searchWord . '%'); }
I did some debugging and found out that the language queries are build incorrect when making JOINS (
I shortened the query the the relevant part):
SELECT DISTINCT tx_eventmgmt_domain_model_event.*, sys_category.* FROM tx_eventmgmt_domain_model_event LEFT JOIN tx_eventmgmt_event_category_mm ON tx_eventmgmt_domain_model_event.uid=tx_eventmgmt_event_category_mm.uid_local LEFT JOIN sys_category ON tx_eventmgmt_event_category_mm.uid_foreign=sys_category.uid WHERE ((tx_eventmgmt_domain_model_event.uid IN (SELECT uid_local FROM tx_eventmgmt_event_calendar_mm WHERE uid_foreign=1) AND tx_eventmgmt_domain_model_event.pid IN ('37')) AND ((tx_eventmgmt_domain_model_event.title LIKE '%Hallo%' OR sys_category.title LIKE '%Hallo%') AND (sys_category.sys_language_uid IN (0,-1))
Now if the event has no category this will result in a table where all sys_category fields are NULL and the last part "AND (sys_category.sys_language_uid IN (0,-1))" will not match, so no data will be found.
| event | sys_category | |uid|pid|... |uid |pid |...|sys_language_uid| | 1 | 2 |... |NULL|NULL|...|NULL |
In my opinion this part belongs to the JOIN
... LEFT JOIN tx_eventmgmt_event_category_mm ON tx_eventmgmt_domain_model_event.uid=tx_eventmgmt_event_category_mm.uid_local LEFT JOIN sys_category ON tx_eventmgmt_event_category_mm.uid_foreign=sys_category.uid AND (sys_category.sys_language_uid IN (0,-1)) ...
Then the searched data will be found.
Regards, Eike Starkmann