There are different ways to query the database. A short introduction when to use which one.

Extbase Repositories

Available methods: 

Custom queries inside a repsoitory:


public function findByActiveCfp()
	$query = $this->createQuery();
		$query->lessThanOrEqual('cfpStart', new DateTime()),
        $query->greaterThanOrEqual('cfpEnd', new DateTime())
	return $query->execute();

Query Builder (Doctrine DBAL)

For more complex queries:


public function findBookedByExhibitorAndConference(Exhibitor $exhibitor, Conference $conference): array
	/** @var \TYPO3\CMS\Core\Database\Query\QueryBuilder $qb */
	$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_zwbisdreiconference_domain_model_attendance');
	/** @var \Doctrine\DBAL\Statement $stmt */
	$res = $queryBuilder
			->from('tx_zwbisdreiconference_domain_model_attendance', 'a')
            ->join('a','tx_zwbisdreiconference_domain_model_attendancebooking','b','b.attendance=a.uid and b.status="booked"')
				$queryBuilder->expr()->eq('a.conference', $conference->getUid()),
				$queryBuilder->expr()->eq('a.exhibitor', $exhibitor->getUid()),
				$queryBuilder->expr()->eq('a.gesperrt', 0)

	// Use this to get an simple array
    $result = $res->execute();
	return $result->fetch();

	// Use this to get an rich extbase object instead (only works in repository)
	$query = $this->createQuery();
    return $query->execute();

Simple cases with Query Builder

This is used to have a light weight call for simple query cases. It is fast but quite limited:


public function getExhibitor(int $exhibitoruid): array
	$connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('tx_zwbisdreiconference_domain_model_exhibitor');
	$exhibitor = $connection->select(
		[ '*' ],
		['uid' => $exhibitoruid]

Plain SQL statements

Sometime you need to go down to plain sql to use special mysql functions like UNION.


public function findCombinedUids(Conference $conference): array
	$connection = GeneralUtility::makeInstance(ConnectionPool::class)->getConnectionForTable('tx_zwbisdreiconference_domain_model_contribution');

	$sql = sprintf('
		select uid,type from (
			select contribution.type, contribution.uid from tx_zwbisdreiconference_domain_model_contribution contribution
    		select 0 as type, presentation.uid as uid from tx_zwbisdreitoolpresentation_domain_model_presentation presentation
		) t1 where deleted=0 and hidden=0 and conference = %s
	', $conference->getUid());

 	$query = $connection->prepare($sql);
    return $query->fetchAllAssociative();

Note: The function "fetchAllAssociative()" is only available from version 11. Before that "fetchAll()" can be used. 



Nice to know

Debug Queries:

Migrate old database queries: