IN and NOT IN query in TYPO3 extbase extension

When we works with extbase extension development in TYPO3, we have to works with queries.
TYPO3 have their own query builder and it convents the result into object. So whatever we need to user using JOIN query  we can do it just by their object without manual query.

But when I was searching for different queries like IN, NOT IN etc, I was found it difficult to find so I am writing this post.

You can use IN query as per below :

$query = $this->createQuery();
       $query->matching(
               $query->logicalAnd(
                         $query->in('uid', $contrains),
               )
       );
 $query->execute();

You can user NOT IN query as per below :

$query = $this->createQuery();
       $query->matching(
               $query->logicalAnd(
                          $query->logicalNot(
                                   $query->in('uid', $contrains),
                          ) 
               )
       );
 $query->execute();

You can add additional condition of logical AND and logical OR along with above IN and NOT IN query.

extbse query, IN query, NOT IN query 9:59 PM 1 comment

Extbase Queries : NOT IN query and Conditional sorting not there

When I was working with extbase extension development, I was wondered about the TYPO3 query builders.
In TYPO3, the query builder create a object and using that object we can get all required related values without any manual join query. But you will only have such object when you use TYPO3 extbase Queries. I found a great blog post on https://lbrmedia.net/codebase/Eintrag/extbase-query-methods/.

1. NOT IN query not there in extbase queries :

When I was working with that, I was in need of a NOT IN Query as we do in Mysql.
Like :

SELECT * FROM 'table1' WHERE  uid NOT IN (SELECT ID FROM table1 WHERE lang = 'FR');

You can use IN Query in TYPO3 like :

$query->in($myProperty, $isInThisObjectOrArray);

But there is no query for NOT IN in TYPO extbase Query. I was in such a need that I want to return values in object only, so stuck in that situation. At the end, I used manual query instead.

2. ORDER BY with specific conditions liks NULL and 0 values not possible in extbase query :
In  TYPO3 extbase queries, you can do sorting by extbase queries as per below :

$query->setOrderings(array("field" => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_ASCENDING));
$query->setOrderings(array("field" => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING)

But in case of we need conditional sorting like :

SELECT * FROM my_table WHERE myattr=1 ORDER BY (CASE WHEN districts.id IS NULL then 1 ELSE 0 END),districts.name, schools.name;

Here, I want a conditional sorting in such case not extbase query helped and again, I need to do all using manual query.

Please comment below if you have better solution for the same. :)

extbse query, I hate about TYPO3, NOT IN query in TYPO3 12:10 AM 2 comments