TYPO3 9.5 repository query to fetch elements with multiple sys_categories

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...

Solution

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