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.

Table of Contents

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
{
    public function __construct(
        private readonly ConnectionPool $connectionPool,
    ) {}

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

This results in the following SQL statement:

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

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
{
    public function __construct(
        private readonly 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();
    }
}

Result in $row:

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

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

Note

The default restrictions deleted, hidden, startime and endtime based on the TCA setting of a table are only applied to select() calls, they are not added for delete() or other query types.

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
{
    public function __construct(
        private readonly 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();
    }
}

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)

The executed query looks like this:

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

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
{
    public function __construct(
        private readonly ConnectionPool $connectionPool,
    ) {}

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

The executed query looks like this:

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

Tip

You can also use the QueryBuilder to create more complex update queries. For examples, see the QueryBuilder chapter.

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
{
    public function __construct(
        private readonly ConnectionPool $connectionPool,
    ) {}

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

The executed query looks like this:

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