Is there any way to use mysql function in extbase querying?

Question

What can i do if i want to something like following using extbase model query (Using Object Relational Model).

SELECT uid FROM table WHERE fIND_IN_SET('4',column_name);

OR something like

SELECT SUM(column_name) FROM table WHERE 1

Note : I don't want to use custom query using statement() method

Answer 1

If you specified why you don't "want" to use a custom statement you would realize that you are mistaken. Your situation is the exact reason why statement() exists in the first place. Trying to avoid using it at almost any cost is unreasonable as the SQL abilities of extbase are far from covering all use cases. So in summary: use extbase API where logical and an obvious good choice but don't shy away from statement() where it's use is the obvious best choice just because usign extbase API "looks nicer" or it seems like with the API you're "using the framework to it's full potential".

Answer 2

As of now, there is neither an equivalent for the FIND_IN_SET function, nor for aggregate functions.

You will only get around this only with a custom statement or by writing your own extension of the TYPO3\CMS\Extbase\Persistence\Generic\Query class. There are, of course, security considerations which you'll have to mind with a custom statement(). However, the same security implications apply with the class extension.

To be more specific on the FIND_IN_SET function: You can't just use the like() method. If you're searching for id 1, for example, you would find it in a set that consists of any of 10,11,12,13 and so on. Furthermore can't get around that problem either because the like() method only accepts property names and doesn't let you wrap column names in functions.

Answer 3

According to your question: I don't think there is any way to use mysql functions in extbase without using statement().

According to your example: You can try $query->like('columnName', "%{4}%").

Answer 4

This is possible but needs a bit effort. I have done that in my news extension. The code looks like that

    $query = // your $query->execute();

    $queryParser = $this->objectManager->get(Typo3DbQueryParser::class);
    list($hash, $parameters) = $queryParser->preparseQuery($query);
    $statementParts = $queryParser->parseQuery($query);

    // Limit and offset are not cached to allow caching of pagebrowser queries.
    $statementParts['limit'] = ((int)$query->getLimit() ?: null);
    $statementParts['offset'] = ((int)$query->getOffset() ?: null);

    $tableNameForEscape = (reset($statementParts['tables']) ?: 'foo');
    foreach ($parameters as $parameterPlaceholder => $parameter) {
        if ($parameter instanceof LazyLoadingProxy) {
            $parameter = $parameter->_loadRealInstance();
        }

        if ($parameter instanceof \DateTime) {
            $parameter = $parameter->format('U');
        } elseif ($parameter instanceof DomainObjectInterface) {
            $parameter = (int)$parameter->getUid();
        } elseif (is_array($parameter)) {
            $subParameters = [];
            foreach ($parameter as $subParameter) {
                $subParameters[] = $GLOBALS['TYPO3_DB']->fullQuoteStr($subParameter, $tableNameForEscape);
            }
            $parameter = implode(',', $subParameters);
        } elseif ($parameter === null) {
            $parameter = 'NULL';
        } elseif (is_bool($parameter)) {
            return ($parameter === true ? 1 : 0);
        } else {
            $parameter = $GLOBALS['TYPO3_DB']->fullQuoteStr((string)$parameter, $tableNameForEscape);
        }

        $statementParts['where'] = str_replace($parameterPlaceholder, $parameter, $statementParts['where']);
    }

    $statementParts = [
        'selectFields' => implode(' ', $statementParts['keywords']) . ' ' . implode(',', $statementParts['fields']),
        'fromTable' => implode(' ', $statementParts['tables']) . ' ' . implode(' ', $statementParts['unions']),
        'whereClause' => (!empty($statementParts['where']) ? implode('', $statementParts['where']) : '1')
            . (!empty($statementParts['additionalWhereClause'])
                ? ' AND ' . implode(' AND ', $statementParts['additionalWhereClause'])
                : ''
            ),
        'orderBy' => (!empty($statementParts['orderings']) ? implode(', ', $statementParts['orderings']) : ''),
        'limit' => ($statementParts['offset'] ? $statementParts['offset'] . ', ' : '')
            . ($statementParts['limit'] ? $statementParts['limit'] : '')
    ];

    $sql = $GLOBALS['TYPO3_DB']->SELECTquery(
        $statementParts['selectFields'],
        $statementParts['fromTable'],
        $statementParts['whereClause'],
        '',
        $statementParts['orderBy'],
        $statementParts['limit']
    );

    return $sql;

By using the DataMapper you can map the raw result back to models if you need that as well. The code for that looks like that

$dataMapper = $objectManager->get(DataMapper::class);
$records = $dataMapper->map($className, $rows);