TYPO3 Extbase: Query and Repository Overview

Standard-Repository-Methods


// add
$myRepository->add($myModel);
 
// remove
$myRepository->remove($myModel);
 
// removeAll
$myRepository->removeAll();
 
// replace
$myRepository->replace($myModel, $myOtherModel);
 
// update
$myRepository->update($myModifiedModel);
 
// count ALL
$myRepository->countAll(); // get the amount of all objects
 
// count constraint
$myRepository->countBy[myProperty]($thisMustMatchMyProperty);
 
// UID
$myRepository->findByUid($theUid); // returns ONE Model-Object (...if found)
 
// all
$myRepository->findAll(); // only constraint by enablecolumns, language and storagePid
 
// findBy...
$myRepository->findBy[myProperty]($thisMustMatchMyProperty); // returns a Storage-object
 
// findOneBy...
$myRepository->findOneBy[myProperty]($thisMustMatchMyProperty); // returns ONE Model-Object (...if found)



Query-Object in Repository-Method



// INITialize query-object
$query = $this->createQuery();
 
// AND / OR / NOT
$query->logicalAnd($constraints); // commaseparated list of constraints or array
$query->logicalOr($constraints); // commaseparated list of constraints or array
$query->logicalNot($constraints); // commaseparated list of constraints or array
 
// A direct SQL-Statement
$query->statement("SELECT * FROM my_table WHERE 1 " . $GLOBALS['TSFE']->sys_page->enableFields("my_table")); // string
 
// ORDER BY
$query->setOrderings(array("field" => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_ASCENDING)); // array
$query->setOrderings(array("field" => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING)); // array
 
// LIMIT
$query->setLimit($limit); // integer
 
// OFFSET
$query->setOffset($offset); // integer
 
// only COUNT()
$query->count(); // execute query and get amount of matches
 
// execute query
$query->execute();
 
// only get the first result (LIMIT 1)
$query->getFirst();
 
/*
 * typical method
 * in() searches a property for an ARRAY of values
 * contains() searches a property-array for ONE value
 */
public function findSomethingSpecial() {
  //$uids = '1,2,3';
  $uids = explode(',',$uids);
  $query = $this->createQuery();
  $query->matching(
    $query->logicalAnd(
      $query->equals("mySpecialProperty", "isMySpecialValue"),
      $query->equals("someOtherProperty", "isAnotherValue"),
      $query->in("uid", $uids)
    )
  );
 
  $query->setOrderings(array("crdate" => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_ASCENDING))
    ->setOffset(10)
    ->setLimit(20);
 
  return $query->execute();
}

public function testFunc() { 
  $query = $this->createQuery();
  $query->getQuerySettings()->setReturnRawQueryResult(TRUE);
  $query->statement('SELECT tx_myext_domain_model_table.* FROM tx_myext_domain_model_table ');
  //Query Debug
  echo $query->getStatement()->getStatement();
  var_dump($query->getStatement()->getBoundVariables()); 

 //OR for typo3 V8

   $queryParser = $this->objectManager->get(\TYPO3\CMS\Extbase\Persistence\Generic\Storage\Typo3DbQueryParser::class);
   \TYPO3\CMS\Extbase\Utility\DebuggerUtility::var_dump($queryParser->convertQueryToDoctrineQueryBuilder($query)->getSQL());
   \TYPO3\CMS\Extbase\Utility\DebuggerUtility::var_dump($queryParser->convertQueryToDoctrineQueryBuilder($query)->getParameters());

  return $query->execute();
}


"Matching"-Methods for constraints

// = 
$query->equals($myProperty, $equalsThis, $caseSensitive = true);
 
// <
$query->lessThan($myProperty, $isLessThanThis);
 
// >
$query->greaterThan($myProperty, $isGreaterThanThis);
 
// <=
$query->lessThanOrEqual($myProperty, $isLessThanOrEqualThis);
 
// >=
$query->greaterThanOrEqual($myProperty, $isGreaterThanOrEqualThis);
 
// %search%
$query->like($myProperty, $theLikeString);
 
// contains
$query->contains($myProperty, $hasThisValue);
 
// in
$query->in($myProperty, $isInThisObjectOrArray);



Query-Settings



// METHOD 1: Presets for Repository
public function initializeObject() {
  // get the settings
  $querySettings = $this->objectManager->get('TYPO3\\CMS\\Extbase\\Persistence\\Generic\\Typo3QuerySettings');
 
  // modify the settings
  $querySettings->setSomething();
 
  // store the settings as default-values
  $this->setDefaultQuerySettings($querySettings);
}
 
// METHOD 2: Set only in Repo-Method
public function findSomething() {
  $query = $this->createQuery();
  $query->getQuerySettings()->setSomething();
  // ...
  return $query->execute();
}
 
// HERE are the settings:
$querySettings->setRespectStoragePage(FALSE); // ignore the storagePid
$querySettings->setStoragePageIds(array(1, 2, 3)); // set some special storagePids
$querySettings->setRespectEnableFields(FALSE); // ignore enableFields (…is deprecated)
$querySettings->setIgnoreEnableFields(TRUE); // ignore the fields which are defined in TCA in key "enablecolumns"
$querySettings->setEnableFieldsToBeIgnored(array('disabled', 'starttime')); // only ignore single enableFields
$querySettings->setIncludeDeleted(TRUE); // also find the deleted rows
$querySettings->setRespectSysLanguage(FALSE); // ignore the sys_language
$querySettings->setSysLanguageUid(2); // set a special sys_language



Force the repository to do the database stuff




/*
 * Action-Method in Controller...
 */
// create new Model-Object
$newItem = new \VENDOR\MyExt\Domain\Model\Item();
$newItem->setSomeStuff();
$myItemRepository->add($newItem);
 
// now persist all to have the possibility to use the new ITEM-UID p.e. in view...
$persistenceManager = $this->objectManager->get('TYPO3\\CMS\\Extbase\\Persistence\\Generic\\PersistenceManager');
$persistenceManager->persistAll();
 
$this->view->assign("newItem", $newItem);
 
/*
 * Fluid-View
 */
<f:link.action controller="Item" action="show" arguments="{item:newItem}">show new item</f:link.action>

Special thanks to lbrmedia.net