How to make an regexp request with TYPO3 queryBuilder

Question

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

Although @eliashaeussler and @Jonas gave you solutions which probably works and you should try them in the first step, for performance reasons I'd rather suggest adding a separate, indexed and hidden column in your table that stores first char and search by it. It will be always MUCH faster than searching with `LIKE` or `REGEXP`. Of course, you'll need to add some work to update that column after each insert or update of your record. If you are interested in that solution, leave a comment and I'll explain to you how to. — biesior, Jul 29 '20 at 08:57

score 2 · Answer 1 · answered Jul 29 '20 at 08:39

I would suggest to use LIKE to find strings starting with a given character.

Using LIKE

$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.

Using REGEXP

@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.

eliashaeussler · Accepted Answer · 2020-07-29T09:32:43.230

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:]]'

Reference: https://github.com/TYPO3/TYPO3.CMS/blob/10.4/typo3/sysext/core/Classes/Database/Query/Expression/ExpressionBuilder.php#L97-L100

Thank you for your example, but I wanted every special char and number, not every letter. So i just added NOT before your REDEXP and it worked like a charm — th3r3alpr0, Jul 29 '20 at 09:20

How to make an regexp request with TYPO3 queryBuilder

2 Answers2

Using LIKE

Using REGEXP