CODEWITHSUNDEEP
typo3query-builder
David Green
David Green

Reputation: 113

How to create a dynamic 'WHERE' SQL clause using TYPO3 Querybuilder

How can I dynamically add extra conditions to 'WHERE' clauses in TYPO3 database queries please? The new TYPO3 Version 8 docs say how to make fixed queries, but not variable ones.

In the past, I could create a SQL statement and modify it dynamically like this:

if (condition) {  
    $strWhere = 'some SQL';  
} else {  
    $strWhere = 'same SQL with extra bits'; 
}

$dbRes = $GLOBALS['TYPO3_DB']->exec_SELECTgetRows(
    "*",            // SELECT ...
    "tableName",    // FROM ...
    $strWhere ,     // WHERE...
    etc.

I can't see how to do something like this using Querybuilder. What I want to achieve is an expression that does something like this

if (condition) { 
    ->where($queryBuilder->expr()->eq(... ))   
    }
else {
    ->where($queryBuilder->expr()->eq(... ))
    ->andWhere($queryBuilder->expr()->eq(... ))
}

Any hints would be much appreciated. Thanks.

Upvotes: 1

Views: 2678

Answers (2)

metaxos
metaxos

Reputation: 161

$queryBuilder = GeneralUtility::makeInstance('TYPO3\\CMS\\Core\\Database\\ConnectionPool')->getQueryBuilderForTable('tx_igldapssoauth_config');

$expressionBuilder = $queryBuilder->expr();

$conditions = $expressionBuilder->andX();
$conditions->add(
    $expressionBuilder->eq('somefield', 1)
);
$conditions->add(
    $expressionBuilder->eq('someotherfield', 2)
);

$rows = $queryBuilder->select('*')
->from('tx_igldapssoauth_config')
->where(
    $queryBuilder->expr()->eq('uid', 1)
)
->andWhere($conditions)
->execute()->fetchAll();

Upvotes: 2

David Green
David Green

Reputation: 113

Solved. My mistake was in thinking that the various parts of a Query builder statement HAVE to come together - they don't.

So this: 

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

$queryBuilder
   ->select('uid', 'header', 'bodytext')
   ->from('tt_content')
   ->where(
      $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('klaus')),
      $queryBuilder->expr()->eq('header', $queryBuilder->createNamedParameter('a name'))
   )
   ->execute();

can also be split into parts, such as:

switch ($scope) {
     case 'limitfields':
       $queryBuilder->select('uid','header','bodytext');
       break;

     default:
       $queryBuilder->select('*');
       break;
   }

$queryBuilder
    ->from('tt_content')
    ->where(
       $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('klaus')),
       $queryBuilder->expr()->eq('header', $queryBuilder->createNamedParameter('a name'))
    )
    ->execute();

Upvotes: 4

Related Questions