Bug #73666

ORM builds incorrect queries when using relations and sys_language

Added by Eike Starkmann over 3 years ago. Updated 11 months ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
Extbase + l10n
Target version:
-
Start date:
2016-02-25
Due date:
% Done:

0%

TYPO3 Version:
6.2
PHP Version:
5.5
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

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

History

#1 Updated by Eike Starkmann over 3 years ago

  • Target version deleted (6.2.18)

#2 Updated by Tymoteusz Motylewski about 1 year ago

  • Category changed from Extbase to Extbase + l10n
  • Status changed from New to Needs Feedback

Please take a look at the patch https://review.typo3.org/#/c/53974/ whether it solves the issue for you.

#3 Updated by Riccardo De Contardi 11 months ago

  • Status changed from Needs Feedback to Closed

No feedback since the last 90 days => closing this issue.

If you think that this is the wrong decision or experience the issue again and have more information about how to reproduce your problem, please reopen it or open a new issue with a reference to this one.

Thank you and best regards

Also available in: Atom PDF