TYPO3 extbase database query

2
votes

I have an extbase database query like below.

$query = $this->createQuery();        
$result = $query->statement("Select * FROM table1 WHERE hidden = 0 AND deleted = 0 AND (".$PublicationYears.") AND logo != '' ORDER BY uid ASC LIMIT 0, ".$iLimit." ")->execute();             
return $result;


$PublicationYears = "ttra = '12' or ttra = '13' or ttra = '14'";

I converted this query as follows,

$query = $this->createQuery();

$query->getQuerySettings()->setRespectStoragePage(FALSE);

$query->matching( $query->logicalAnd(
       $query->equals('deleted', 0),
       $query->equals('hidden', 0)
    ));

$query->matching($query->logicalAnd($PublicationYears));

$query->matching($query->logicalNot(
     $query->equals('logo', '')
 ));

$query->setOrderings(array('uid' => Tx_Extbase_Persistence_QueryInterface::ORDER_ASCENDING));

$query->setLimit((integer)$iLimit);
$Result = $query->execute();

return $Result;

But the resultant query does not contain the query part related to,

$query->matching($query->logicalAnd($PublicationYears));

I think there are also other mistakes in the above query.

Please help me to create correct query.

Thanks in advance.

2
typo3extbasetypo3-6.2.xqueryinterface

2 Answers

9
votes

first of all you dont need this

$query->matching( $query->logicalAnd(
   $query->equals('deleted', 0),
   $query->equals('hidden', 0)
));

disable fields are included in query from default according to setting of table in ext_tables.php

multiple matching will not work cause your third matching

$query->matching($query->logicalNot(
 $query->equals('logo', '')
 ));

is overwriting the matching() used before it logicalAnd on string ? it won't work either

in your case you just need to put everything in logicalAnd do it like this

$constraints = array();
$subConstraints = array();
$subConstraints[] = $query->equals('ttra', 12);
$subConstraints[] = $query->equals('ttra', 13); 
$subConstraints[] = $query->equals('ttra', 14);
$constraints[] = $query->logicalOr($subConstraints);
$constraints[] = $query->logicalNot(
     $query->equals('logo', '')
 ));

$query->matching($query->logicalAnd($constraints));
0
votes

For your PublicationYears values you have to make or query in matching like below: I think it might work only for PublicationYears variable.

 $query->matching($query->logicalAnd(
            $query->logicalOr(
                $query->equals('ttra', 12)           
            ),
            $query->logicalOr(
                $query->equals('ttra', 13)           
            ),
            $query->logicalOr(
                $query->equals('ttra', 14)           
            ),
        ));