I’m trying to figure out how to write the following query to fetch some elements which have multiple categories.
$query->matching(
$query->logicalAnd(
[
// the following 4 lines are the problem lines
$query->logicalAnd(
$query->in('categories.uid', $categories),
$query->in('categories.uid', $countryCategories)
),
// $query->in('categories.uid', $categories),
// $query->in('categories.uid', $countryCategories),
$query->logicalOr(
[
$query->equals('is_pinned', 0),
$query->lessThan('pinned_until', time())
]
),
]
)
);
The idea is to fetch the elements where categories.uid
match at least one uid in $categories
and at least one in $countryCategories
. Both $categories
and $countryCategories
are arrays filled with category uids.
The query worked fine until the second line $query->in('categories.uid' [...]
was inserted. As soon as the second line is inserted the query result is empty. It’s probably an error in the query, but neither me nor my colleague could find a working solution.
While searching I found the sql UNION
, which I’ve never been working with before but I guessed it would be the way to go if I had to write the statement instead of building the query.
What I would like to know is if it is possible to fetch the elements with the “query builder” or if it is really necessairy to write a statement? If there is a solution with the query builder could you point it out for me? If not how would I build the query with UNION
to fetch the elements as required?
If something is unclear, please do not hesitate to ask, I will try to specify further. Thanks.
EDIT
We’ve debugged the query too and I executed it in phpmyadmin directly. It was working without “AND (sys_category.uid IN ( 41, 2 ))
” but with it the result is empty. The follwoing was the debugged query:
SELECT `tx_gijakobnews_domain_model_news`.*
FROM `tx_gijakobnews_domain_model_news` `tx_gijakobnews_domain_model_news`
LEFT JOIN `sys_category_record_mm` `sys_category_record_mm` ON ( `tx_gijakobnews_domain_model_news`.`uid` = `sys_category_record_mm`.`uid_foreign`) AND (( `sys_category_record_mm`.`tablenames` = 'tx_gijakobnews_domain_model_news') AND ( `sys_category_record_mm`.`fieldname` = 'categories'))
LEFT JOIN `sys_category` `sys_category` ON `sys_category_record_mm`.`uid_local` = `sys_category`.`uid`
WHERE ((
(`sys_category`.`uid` IN ( 15, 17, 10, 11, 12, 16, 13, 14 ))
////// this following line is where the problem begins
AND (`sys_category`.`uid` IN ( 41, 2 ))
)
/////////// the following lines are additional restrictions
/////////// which have no influence on the problem
AND ((`tx_gijakobnews_domain_model_news`.`is_pinned` = 0) OR ( `tx_gijakobnews_domain_model_news`.`pinned_until` < 1560867383))
)
AND ( `tx_gijakobnews_domain_model_news`.`sys_language_uid` IN ( 0, -1) )
AND ( `tx_gijakobnews_domain_model_news`.`pid` = 31)
AND ( ( `tx_gijakobnews_domain_model_news`.`deleted` = 0)
AND ( `tx_gijakobnews_domain_model_news`.`t3ver_state` <= 0)
AND ( `tx_gijakobnews_domain_model_news`.`pid` <> -1)
AND ( `tx_gijakobnews_domain_model_news`.`hidden` = 0)
AND ( `tx_gijakobnews_domain_model_news`.`starttime` <= 1560867360)
AND ( ( `tx_gijakobnews_domain_model_news`.`endtime` = 0)
OR ( `tx_gijakobnews_domain_model_news`.`endtime` > 1560867360) ) )
AND ( ( ( `sys_category`.`deleted` = 0)
AND ( `sys_category`.`t3ver_state` <= 0)
AND ( `sys_category`.`pid` <> -1)
AND ( `sys_category`.`hidden` = 0)
AND ( `sys_category`.`starttime` <= 1560867360)
AND ( ( `sys_category`.`endtime` = 0)
OR ( `sys_category`.`endtime` > 1560867360) ) )
OR ( `sys_category`.`uid`
IS NULL) )
ORDER BY `tx_gijakobnews_domain_model_news`.`publish_date` DESC
If there’s a missing bracket I problably removed it accidentally while formatting…
3
Answers
I did it way simpler in the end.
Instead of adding both restrictions by the query, I looped through the results restricted by the first
sys_category
-condition and then removed those which didn't meet the secondsys_category
-restrictions.Repository
Controller
It may not be the best solution, but it's one that works. :-)
You could build your own custom Query with the QueryBuilder. Something like this:
Here’s the documentation:
https://docs.typo3.org/m/typo3/reference-coreapi/master/en-us/ApiOverview/Database/QueryBuilder/Index.html
I believe the problem is that the where clause is applied on a “per row” basis.
Meaning If you have a query like the following (based off of your query):
You might have one news entry, that is in category 1 and in category 4. But the result set would be two distinct rows:
and the WHERE clause filters both of them out, because the sys_category.uid is not both in (1, 2, 3) and in (4, 5, 6) for each row individually.
The way to do that on an SQL level, would probably be, to do two joins to sys_category. But I do not believe it’s possible with the (rather simple) extbase query builder.
Edit:
As a solution, you could use the
$query->statement()
method, that allows to use custom sql queries.https://docs.typo3.org/m/typo3/book-extbasefluid/master/en-us/6-Persistence/3-implement-individual-database-queries.html