I currently have a big list of entries in my TYPO3 Ext that causes the Site to have pretty poor loading times, so i want to split up the Entry list alphabetically. For that I'm using the queryBuilder to select only those entries, that start either with an a, b, c and so on. But im stuck now when it comes to numbers and special chars. I found this: MySQL - If It Starts With A Number Or Special Character which is exactly what I need. But I have no clue how to make an REGEXP in the queryBuilder. Can someone help?
TYPO3 version is 10.4.6
Thanks in advance
I would suggest to use LIKE to find strings starting with a given character.
$firstCharacter = 'A';
$q = GeneralUtility::makeInstance(ConnectionPool::class)
->getQueryBuilderForTable('mytable');
$q->select('field')
->from('mytable')
->where(
$q->expr()->orX(
$q->expr()->like(
'field',
$q->quote(strtolower($firstCharacter) . '%')
),
$q->expr()->like(
'field',
$q->quote(strtoupper($firstCharacter) . '%')
);
upper/lower case could be left out of course if the DBMS is case insensitive.
@eliashaeussler gave a good example for that.
While LIKE is supported by Doctrine dbal, REGEXP is not. You would lose cross-DBMS compatiblity. This is probably fine if you target a specific site but should not be used in public extensions of course.
Maybe something like this works for you:
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)->getQueryBuilderForTable($tableName);
$result = $queryBuilder->select('*')
->from($tableName)
->where(
$queryBuilder->expr()->comparison(
$queryBuilder->quoteIdentifier($fieldName),
'NOT REGEXP',
$queryBuilder->createNamedParameter('^[[:alpha:]]')
)
)
->execute()
->fetchAll();
This creates a query like this (assuming $tableName = 'foo' and $fieldName = 'baz'):
SELECT `foo`.*
FROM `foo`
WHERE `foo`.`baz` NOT REGEXP '^[[:alpha:]]'