Custom Queries in ExtBase

One major problem with object-relational mapping (ORM) is that there will always be edge cases that just don’t work. Relational databases and SQL are far too complex to be able to cover every tiny feature in a few PHP files.

This is especially true for the ORM layer I’m currently using: The one that comes with ExtBase, TYPO3’s extension framework. ExtBase clearly has improved since the old TYPO3 4.5 days, but its APIs can be very frustrating sometimes.

So how do you handle those situations when the ORM fails you?

Domain-driven design

TYPO3 always had some kind of abstraction for sending queries to the database. It was recommended to make use of functions like exec_SELECTquery() or exec_UPDATEquery() when accessing the database from a piBase extension.

With ExtBase it all changed. Now we are using Domain-driven design to select data from and modify data in the database. For each table there is a model class that later represents one database record from that table. There are also repositories which are responsible for fetching datasets from the database. For that purpose there are some built-in methods like findAll(), findByUid($uid) or findByMyFieldName($fieldValue).

The repository is also the place where things get complicated and where you reach the limits of ExtBase’s ORM. The painful solution: Go back to plain SQL queries.

Custom Queries

The ExtBase framework provides some rudimentary methods to use custom SQL queries within your repository:

// Create query object
$query = $this->createQuery();

// Set plain SQL statement
$sql = 'SELECT * FROM table';
$query->statement($sql);

// Execute query
$result = $query->execute();

I think this code is quite self-explanatory. It is also possible (and advisable for security reasons) to use prepared statements with ? placeholders:

// Define variables to be used in SQL
$uidToFind = 1337;
$nameToFind = "Insecure' name";

// Set plain SQL statement
$sql = 'SELECT *
    FROM table
    WHERE uid = ?
        OR name = ?';
$query->statement($sql, array($uidToFind, $nameToFind));

So far so good.

Reproducing ExtBase queries

But what if you want to make use of some basic TYPO3 functionality? For starters there is enableFields() which makes sure that the frontend user only gets to view records he is allowed to view. Well, as far as I know there is no better way than to do this:

$tableName = 'table';
if (TYPO3_MODE === 'FE') {  
    // Use enableFields in frontend mode
    $enableFields = $GLOBALS['TSFE']->sys_page->enableFields($tableName);
} else {
    // Use enableFields in backend mode
    $enableFields = \TYPO3\CMS\Backend\Utility\BackendUtility::deleteClause($tableName);
    $enableFields .= \TYPO3\CMS\Backend\Utility\BackendUtility::BEenableFields($tableName);
}

// Set plain SQL statement
$sql = 'SELECT *
    FROM table
    WHERE (uid = ?
        OR name = ?)
        ' . $enableFields;

Make sure to use 1 = 1 as first WHERE statement if enableFields() is your only other statement as it starts with AND:

// Set plain SQL statement
$sql = 'SELECT *
    FROM table
    WHERE 1 = 1
        ' . $enableFields;

Another thing to remember is that in most cases you should add a storagePid check to your queries, especially in multi-site environments. Otherwise all records will be shown on all sites.

// Get allowed storage pids defined in TypoScript
$storagePids = $query->getQuerySettings()->getStoragePageIds();
// Sanitize them (just to be sure)
$storagePids = array_map('intval', $storagePids);

// Set plain SQL statement
$sql = 'SELECT *
    FROM table
    WHERE (uid = ?
        OR name = ?)
        AND pid IN (' . implode(', ', $storagePids) . ')';

Especially painful is the ordering part of the query as there seems to be no way to access the default orderings usually specified in the repository class. In case of a custom query you have to specify them again:

// Set plain SQL statement
$sql = 'SELECT *
    FROM table
    WHERE (uid = ?
        OR name = ?)
    ORDER BY name, tstamp DESC';

Another problem is escaping. Mostly you should be covered by prepared statements (see above). However there are some cases where you just can’t use them, especially if you don’t want TYPO3 to add those single quotes for you:

// Set plain SQL statement
$sql = "SELECT *
    FROM table
    WHERE (uid = ?
        OR name LIKE '" . $GLOBALS['TYPO3_DB']->quoteStr($name, 'table') . "%'";

As you can see I’m utilizing the oldschool database layer here, and to be honest I’m feeling pretty bad for doing it.

Prettify this, please!

I’m leaving you with the following snippet which extends the repository class so that you can use those functions more easily:

Know something I don’t?

As we all know TYPO3 documentation is far from perfect, which is one of the reasons why I started this blog. However I’m not perfect as well. If you know something about this topic I don’t or if there’s something wrong with what I wrote, feel free to contact me via email or twitter.