doctrine-dbal example

For this blog I needed the 10 most used tags (tag) with a count of the entries (entry) assigned to each tag. I used the chance to make myself familiar with doctrine-dbal, since with the upcoming TYPO3 9 the good old TYPO3_DB will no longer be available. 

First the definition of the classes we’re going to need:

use \TYPO3\CMS\Core\Utility\GeneralUtility;
use \TYPO3\CMS\Core\Database\ConnectionPool;

Then there is the function findMostUsed, which fetches the X most used tags, including their entrycount: 

public function findMostUsed($limit){
    $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable('tx_ophiblog_domain_model_tag');
    $queryBuilder->select('tx_ophiblog_domain_model_tag.uid', 'tx_ophiblog_domain_model_tag.name')
        ->addSelectLiteral(
            $queryBuilder->expr()->count('tx_ophiblog_domain_model_tag.uid', 'count')
        )
        ->from('tx_ophiblog_domain_model_tag')
        ->leftJoin('tx_ophiblog_domain_model_tag', 'tx_ophiblog_entry_tag_mm', 'm',
            $queryBuilder->expr()->eq(
                'm.uid_foreign',
                'tx_ophiblog_domain_model_tag.uid'
            ))
        ->where('1')
        ->groupBy('tx_ophiblog_domain_model_tag.uid')
        ->setMaxResults($limit)
        ->orderBy('count', 'desc');

    $statement = $queryBuilder->execute();
    $data = array();
    while($row = $statement->fetch()){
        $data[] = $row;
    }
    return $data;
}

I’m still unsure if the iteration over $statement is truly necessary, but I’ll see. So far it worked for me.

Whoever else has problems migrating TYPO3_DB, best start with this page. Its only covering the basics, but it’s a start.