There are several ways to query the database. A brief introduction to when to use which.

Extbase Repositories

Available methods: https://docs.typo3.org/m/typo3/book-extbasefluid/main/en-us/6-Persistence/2a-creating-the-repositories.html 

Custom queries inside a repsoitory: https://docs.typo3.org/m/typo3/book-extbasefluid/main/en-us/6-Persistence/3-implement-individual-database-queries.html

Example

<?php
public function findByActiveCfp()
{
	$query = $this->createQuery();
	$query->matching($query->logicalAnd([
		$query->lessThanOrEqual('cfpStart', new DateTime()),
        $query->greaterThanOrEqual('cfpEnd', new DateTime())
	]));
	return $query->execute();
}

Query Builder (Doctrine DBAL)

For more complex queries: https://docs.typo3.org/m/typo3/reference-coreapi/main/en-us/ApiOverview/Database/QueryBuilder/Index.html

Example

<?php
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
			->select('a.*')
			->from('tx_zwbisdreiconference_domain_model_attendance', 'a')
            ->join('a','tx_zwbisdreiconference_domain_model_attendancebooking','b','b.attendance=a.uid and b.status="booked"')
			->where(
				$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();
    $query->statement($res);
    return $query->execute();
}

Simple cases with Query Builder

This is used to have a lightweight call for simple query cases. It is fast but quite limited: https://docs.typo3.org/m/typo3/reference-coreapi/main/en-us/ApiOverview/Database/Connection/Index.html

Example

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

Plain SQL statements

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

Example

<?php
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
    		union
    		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);
    $query->execute();
    return $query->fetchAllAssociative();
}

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

v9: https://docs.typo3.org/m/typo3/reference-coreapi/9.5/en-us/ApiOverview/Database/Statement/Index.html

v11: https://docs.typo3.org/m/typo3/reference-coreapi/11.5/en-us/ApiOverview/Database/Statement/Index.html

Nice to know

Debug Queries: 
https://docs.typo3.org/m/typo3/reference-coreapi/main/en-us/ApiOverview/Database/TipsAndTricks/Index.html
http://www.typo3-snippets.de/snippets/extbase-und-fluid/extbase-sql-queries-debuggen/

Migrate old database queries: https://docs.typo3.org/m/typo3/reference-coreapi/main/en-us/ApiOverview/Database/Migration/Index.html