phpmyadmin Question posted in
The official documentation can be found here.

TYPO3 9.5 repository query to fetch elements with multiple sys_categories – Phpmyadmin

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…

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    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 second sys_category-restrictions.

    Repository

        $query->matching(
        $query->logicalAnd([

            $query->in('categories.uid', $categories),
            $query->logicalOr(
                [
                    $query->equals('is_pinned', 0),
                    $query->lessThan('pinned_until', time())
                ]
            ),
        ])
    );

    Controller

    public function getRestrictedNews($news, $countryCategories) {
    $newNews = array();

    foreach ($news as $newsItem) {
        $newsCategories = $newsItem->getCategories();
        $shouldKeep = false;
        foreach ($newsCategories as $categoryItem) {
            if (in_array($categoryItem->getUid(), $countryCategories)) {
                $shouldKeep = true;
            }
        }

        if ($shouldKeep) {
            array_push($newNews, $newsItem);
        }
    }

    return $newNews;
}

    It may not be the best solution, but it's one that works. :-)


  2. 0

    You could build your own custom Query with the QueryBuilder. Something like this:

    use TYPO3CMSCoreDatabaseConnectionPool;
use TYPO3CMSCoreUtilityGeneralUtility;
use TYPO3CMSExtbaseUtilityDebuggerUtility;


$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('table_to_select_from');

$result = $queryBuilder->select('*')
        ->from('table_to_select_from')
        ->where($queryBuilder->expr()->in('field', ['1','2','3'])
        ->execute()
        ->fetchAll();

DebuggerUtility::var_dump($result);

    Here’s the documentation:
    https://docs.typo3.org/m/typo3/reference-coreapi/master/en-us/ApiOverview/Database/QueryBuilder/Index.html

    Login or Signup to reply.
  3. 0

    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):

    SELECT *
FROM news
LEFT JOIN sys_category_record_mm mm
  ON (news.uid = mm.uid_foreign) /* AND (...) */
LEFT JOIN sys_category
  ON mm.uid_local = sys_category.uid
WHERE
  sys_category.uid IN (1,2,3)
  AND sys_category.uid IN (4,5,6)

    You might have one news entry, that is in category 1 and in category 4. But the result set would be two distinct rows:

    news.uid | sys_category.uid
1        | 1
1        | 4

    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.

    $result = $query->statement('SELECT news.* FROM news');

    https://docs.typo3.org/m/typo3/book-extbasefluid/master/en-us/6-Persistence/3-implement-individual-database-queries.html

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search