Mar 05, 2015 Tag: Extbase

TYPO3 Extbase Query Methods

A condensed list of Extbase query methods, based on Marcel Briefs’ original blogpost. Thank you, Marcel! Will be updated and continued as needed.

Updated on Nov 14, 2015

Navigate this page:

See also:

Standard Repository Methods

Overview

// add
$myRepository->add($myModel);

// remove
$myRepository->remove($myModel);

// removeAll
//       are you sure?
$myRepository->removeAll();

// replace
$myRepository->replace($myModel, $myOtherModel);

// update
$myRepository->update($myModifiedModel);

// count ALL
//       get number of objects
$myRepository->countAll();

// count constraint
$myRepository->countBy[myProperty]($thisMustMatchMyProperty);

// UID
//       returns ONE model object if it exists
$myRepository->findByUid($theUid);

// all
//       get all objects, but take enablecolumns,
//       language and storagePid into account
$myRepository->findAll();

// findBy...
//       return storage object(s)
$myRepository->findBy[myProperty]($thisMustMatchMyProperty);

// findOneBy...
//        returns ONE model object - if found.
$myRepository->findOneBy[myProperty]($thisMustMatchMyProperty);

Examples

Get domain object from query result

Class QueryResult

// TYPO3\CMS\Extbase\Persistence\Generic\QueryResult  $result
$result = $this->offerRepository->findByParole($parole);

if ($result->count() > 0) {
   // Vendor\Extkey\Domain\Model\Offer  $offer
   $offer = $result[0];
}

// Vendor\Extkey\Domain\Model\Offer  $offer
foreach ($result as $offer) {
  'do something';
}

Get one domain object directly

// Vendor\Extkey\Domain\Model\Offer  $offer
$offer = $this->offerRepository->findOneByParole($parole);
if (!is_null($offer)) {
   $offer = $offer; // do something with domain model object
}

Query-Object in Repository-Methods

// 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:

public function findSomethingSpecial() {
  $query = $this->createQuery();
  $query->matching(
    $query->logicalAnd(
      $query->equals("mySpecialProperty", "isMySpecialValue"),
      $query->equals("someOtherProperty", "isAnotherValue")
    )
  );

  $query->setOrderings(array("crdate" => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_ASCENDING))
    ->setOffset(10)
    ->setLimit(20);

  return $query->execute();
}

Typical:

$offers = $this->offerRepository
   ->createQuery()
   ->setOrderings(array("crdate" => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING))
   ->execute();

“Matching”-Methods for constraints

<?php

// =
$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

Ignore “enablefields”

class SomeRepository extends \TYPO3\CMS\Extbase\Persistence\Repository {

   public function findHiddenByUid($uid) {
       $query = $this->createQuery();
       $query->getQuerySettings()->setRespectEnableFields(FALSE);
       $query->matching($query->equals('uid', $uid));
       return $query->execute()->getFirst();
   }
}

See also https://wiki.typo3.org/Default_Orderings_and_Query_Settings_in_Repository

Other

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

Example Class with Debugging Code

class ExternalOfferRepository extends \TYPO3\CMS\Extbase\Persistence\Repository
{
    //public function findByCategory(Tx_Bwexternaloffers_Domain_Model_Category $category)
    public function findByCategoryUidList($uidListOfCategories)
    {
        if (0) { // save
            $saved = $GLOBALS['TYPO3_DB']->store_lastBuiltQuery;
            $TYPO3_CONF_VARS['SYS']['caching']['cacheConfigurations']['extbase_object']['backend'] = 'TYPO3\\CMS\\Core\\Cache\\Backend\\NullBackend';
            $TYPO3_CONF_VARS['SYS']['caching']['cacheConfigurations']['extbase_object']['frontend'] =  'TYPO3\\CMS\\Core\\Cache\\Backend\\NullBackend';
            $TYPO3_CONF_VARS['SYS']['caching']['cacheConfigurations']['extbase_reflection']['backend'] =  'TYPO3\\CMS\\Core\\Cache\\Backend\\NullBackend';
            $TYPO3_CONF_VARS['SYS']['caching']['cacheConfigurations']['extbase_reflection']['frontend'] =  'TYPO3\\CMS\\Core\\Cache\\Backend\\NullBackend';
        }
        // find category ids from category uids
        $categoryIds = array();
        if (1) {
            $table = 'tx_bwexternaloffers_domain_model_category';
            $where = 'uid in (' . $uidListOfCategories . ')';
            $res = $GLOBALS['TYPO3_DB']->exec_SELECTquery('uid,id', $table, $where);
            while (($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res))) {
                $categoryIds[] = $row['id'];
            }
            $GLOBALS['TYPO3_DB']->sql_free_result($res);
        }
        $orderings = array(
            'date' => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING,
            'title' => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_ASCENDING,
        );
        $query = $this->createQuery();
        $query->matching($query->in('categories.id', $categoryIds));
        $query->setOrderings($orderings);

        if (0) {
            /** @var Typo3DbQueryParser $queryParser */
            $queryParser = \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance('TYPO3\\CMS\\Extbase\\Persistence\\Generic\\Storage\\Typo3DbQueryParser');
            \TYPO3\CMS\Extbase\Utility\DebuggerUtility::var_dump($queryParser->parseQuery($query));
            die();
        }

        $result = $query->execute();

        if (0) {
            echo (serialize($result));
            die();
        }
        if (0) {
            echo '<pre>';
            print_r($GLOBALS['TYPO3_DB']->debug_lastBuiltQuery);
            echo '</pre>';
            die();
        }
        if (0) {
            echo ('hi');
            \TYPO3\CMS\Extbase\Utility\DebuggerUtility::var_dump($result);
            die();
        }
        if (0) { // restore
            $GLOBALS['TYPO3_DB']->store_lastBuiltQuery = $saved;
            $TYPO3_CONF_VARS['SYS']['caching']['cacheConfigurations']['extbase_object']['backend'] = 'TYPO3\\CMS\\Core\\Cache\\Backend\\Typo3DatabaseBackend';
            $TYPO3_CONF_VARS['SYS']['caching']['cacheConfigurations']['extbase_object']['frontend'] = 'TYPO3\\CMS\\Core\\Cache\\Frontend\\VariableFrontend';
            $TYPO3_CONF_VARS['SYS']['caching']['cacheConfigurations']['extbase_reflection']['backend'] = 'TYPO3\\CMS\\Core\\Cache\\Backend\\Typo3DatabaseBackend';
            $TYPO3_CONF_VARS['SYS']['caching']['cacheConfigurations']['extbase_reflection']['frontend'] = 'TYPO3\\CMS\\Core\\Cache\\Frontend\\VariableFrontend';
        }
        return $result;
    }

    public function findAllFromAllPages()
    {
        $query = $this->createQuery();
        $query->getQuerySettings()->setRespectStoragePage(FALSE);
        $result = $query->execute();
        return $result;
    }

}

Comments

comments powered by Disqus

Previous topic

PHP Syntax Highlighting in Sphinx

Next topic

Extbase API: Main Focus: The Controller

Tags

Archives

Languages

Recent Posts

This Page