如何使用 TYPO3 queryBuilder 发出正则表达式请求

2021-09-12 00:00:00 regex mysql typo3 query-builder typo3-10.x

我目前在我的 TYPO3 Ext 中有一个很大的条目列表，这导致站点加载时间很短，所以我想按字母顺序拆分条目列表.为此，我使用 queryBuilder 仅选择那些以 a、b、c 等开头的条目.但是当涉及到数字和特殊字符时，我现在卡住了.我发现了这个:MySQL - 如果它以数字开头或特殊字符，这正是我所需要的.但我不知道如何在 queryBuilder 中创建 REGEXP.有人可以帮忙吗?

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 版本为 10.4.6

TYPO3 version is 10.4.6

提前致谢

推荐答案

也许这样的事情适合你:

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

这会创建一个这样的查询(假设 $tableName = 'foo' 和 $fieldName = 'baz'):

This creates a query like this (assuming $tableName = 'foo' and $fieldName = 'baz'):

SELECT `foo`.*
FROM `foo`
WHERE `foo`.`baz` NOT REGEXP '^[[:alpha:]]'

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

相关文章