Basic create, read, update, and delete operations (CRUD)

This section provides a list of basic usage examples of the query API. This is just a starting point. Details about the single methods can be found in the following chapters, especially QueryBuilder and Connection.

All examples use dependency injection to provide the ConnectionPool in the classes.

Insert a row

A direct insert into a table:

EXT:my_extension/Classes/Domain/Repository/MyInsertRepository.php
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;

final class MyInsertRepository
{
    private ConnectionPool $connectionPool;

    public function __construct(ConnectionPool $connectionPool)
    {
        $this->connectionPool = $connectionPool;
    }

    public function insertSomeData(): void
    {
        $this->connectionPool
            ->getConnectionForTable('tt_content')
            ->insert(
                'tt_content',
                [
                    'pid' => 42,
                    'bodytext' => 'ipsum',
                ]
            );
    }
}
Copied!

This results in the following SQL statement:

INSERT INTO `tt_content` (`pid`, `bodytext`)
    VALUES ('42', 'ipsum')
Copied!

Select a single row

Fetching a single row directly from the tt_content table:

EXT:my_extension/Classes/Domain/Repository/MySelectRepository.php
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;

final class MySelectRepository
{
    private ConnectionPool $connectionPool;

    public function __construct(ConnectionPool $connectionPool)
    {
        $this->connectionPool = $connectionPool;
    }

    /**
     * @return array|false
     */
    public function selectSomeData()
    {
        $uid = 4;

        return $this->connectionPool
            ->getConnectionForTable('tt_content')
            ->select(
                ['uid', 'pid', 'bodytext'], // fields to select
                'tt_content',               // from
                ['uid' => $uid]             // where
            )
            ->fetchAssociative();
    }
}
Copied!

Result in $row:

array(3 items)
   uid => 4 (integer)
   pid => 35 (integer)
   bodytext => 'some content' (12 chars)
Copied!

The engine encloses field names in quotes, adds default TCA restrictions such as deleted=0, and prepares a query to be executed with this final statement:

SELECT `uid`, `pid`, `bodytext`
    FROM `tt_content`
    WHERE (`uid` = '4')
        AND ((`tt_content`.`deleted` = 0)
        AND (`tt_content`.`hidden` = 0)
        AND (`tt_content`.`starttime` <= 1669838885)
        AND ((`tt_content`.`endtime` = 0) OR (`tt_content`.`endtime` > 1669838885)))
Copied!

Select multiple rows with some "where" magic

Advanced query using the QueryBuilder and manipulating the default restrictions:

EXT:my_extension/Classes/Domain/Repository/MyQueryBuilderSelectRepository.php
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Database\Query\Restriction\DeletedRestriction;
use TYPO3\CMS\Core\Utility\GeneralUtility;

final class MyQueryBuilderSelectRepository
{
    private ConnectionPool $connectionPool;

    public function __construct(ConnectionPool $connectionPool)
    {
        $this->connectionPool = $connectionPool;
    }

    public function selectSomeData(): array
    {
        $uid = 4;

        $queryBuilder = $this->connectionPool
            ->getQueryBuilderForTable('tt_content');

        // Remove all default restrictions (delete, hidden, starttime, stoptime),
        // but add DeletedRestriction again
        $queryBuilder->getRestrictions()
            ->removeAll()
            ->add(GeneralUtility::makeInstance(DeletedRestriction::class));

        // Execute a query with "bodytext=lorem OR uid=4" and proper quoting
        return $queryBuilder
            ->select('uid', 'pid', 'bodytext')
            ->from('tt_content')
            ->where(
                $queryBuilder->expr()->or(
                    $queryBuilder->expr()->eq(
                        'bodytext',
                        $queryBuilder->createNamedParameter('lorem')
                    ),
                    $queryBuilder->expr()->eq(
                        'uid',
                        $queryBuilder->createNamedParameter($uid, Connection::PARAM_INT)
                    )
                )
            )
            ->executeQuery()
            ->fetchAllAssociative();
    }
}
Copied!

Result in $rows:

array(2 items)
    0 => array(3 items)
        uid => 4 (integer)
        pid => 35 (integer)
        bodytext => 'ipsum' (5 chars)
    1 => array(3 items)
        uid => 366 (integer)
        pid => 13 (integer)
        bodytext => 'lorem' (5 chars)
Copied!

The executed query looks like this:

SELECT `uid`, `pid`, `bodytext`
    FROM `tt_content`
    WHERE ((`bodytext` = 'lorem') OR (`uid` = 4))
        AND (`tt_content`.`deleted` = 0)
Copied!

Update multiple rows

EXT:my_extension/Classes/Domain/Repository/MyUpdateRepository.php
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;

final class MyUpdateRepository
{
    private ConnectionPool $connectionPool;

    public function __construct(ConnectionPool $connectionPool)
    {
        $this->connectionPool = $connectionPool;
    }

    public function updateSomeData()
    {
        $this->connectionPool->getConnectionForTable('tt_content')
            ->update(
                'tt_content',
                [ 'bodytext' => 'ipsum' ], // set
                [ 'bodytext' => 'lorem' ]  // where
            );
    }
}
Copied!

The executed query looks like this:

UPDATE `tt_content` SET `bodytext` = 'ipsum'
    WHERE `bodytext` = 'lorem'
Copied!

Delete a row

EXT:my_extension/Classes/Domain/Repository/MyDeleteRepository.php
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;

final class MyDeleteRepository
{
    private ConnectionPool $connectionPool;

    public function __construct(ConnectionPool $connectionPool)
    {
        $this->connectionPool = $connectionPool;
    }

    public function deleteSomeData()
    {
        $this->connectionPool->getConnectionForTable('tt_content')
            ->delete(
                'tt_content', // from
                ['uid' => 4711]  // where
            );
    }
}
Copied!

The executed query looks like this:

DELETE FROM `tt_content`
    WHERE `uid` = '4711'
Copied!