Extbase TYPO3 - Expand query with custom WHERE parts

Question

I have a model ("offers") that has the attributes lat/lng among many other meta attributes. Inside a repository method I am able to fetch offers according to matching metadata like:

$where[] = $query->equals('special', 1);
$where[] = $query->equals('region', 7);
$where[] = $query->greaterThan('crdate', $minTime);

Now I need to be able to to a radius search depending on the lat/lng attributes. In a usual SQL query this would be something like:

WHERE acos(sin(:lat)*sin(radians(lat)) + cos(:lat)*cos(radians(lat))*cos(radians(lng)-:lng)) * :R < :rad

However since this contains mathematic values, I don't see a possibility to add a $query counterpart. How cann I pass a WHERE portion like this to an extbase query?

What I would like to avoid:

• Using a raw admin_query() with $GLOBALS['TYPO3_DB'] with all the same $where conditions that I gathered before the execute() method of the proper Query, to gather the UIDs of the available records, and append the $query with a condition that only returns those matching the UID of the second query

• Rewriting the whole repository method to use a raw sql method and iterate that to generate the propery ER-Model of Extbase.

So ideally, I'd like to have something like:

$query->customSQL('blablabla > bla');

which would get appended to the SQL WHERE part later on. Maybe I could first create a placeholder like

$query->equals('placeholder', 1);

and then somehow get the statement before execution, do a

str_replace('placeholder = 1', '...my real radius statement...')

But I only see a getStatement() not a setStatement() in the QueryInterface...

Answer 1

The function you were looking for is called \TYPO3\CMS\Extbase\Persistence\Generic\Query(), it‘s not defined in the interface. You‘ll have to write the whole query by yourself - it‘s not possible to mix extbase query builder and plain SQL code here.

You can (and should) use prepared queries (\TYPO3\CMS\Core\Database\PreparedStatement) here, though.

I did something similar in a project and paste my code below, maybe it helps you:

/**
 * Creates a prepared statement
 *
 * @return \TYPO3\CMS\Core\Database\PreparedStatement
 */
protected function getPreparedStatement()
{
    if (is_null($this->statement)) {
        $this->statement = $this->getDatabaseConnection()->prepare_SELECTquery(
            '*, ( :meanradius * acos(LEAST(1, cos( radians( :latitude ) ) * cos( radians( latitude ) )' .
            ' * cos( radians( longitude ) - radians( :longitude ) )' .
            ' + sin( radians( :latitude ) ) * sin( radians( latitude ) ) ) )' .
            ' ) AS distance',
            $this->tableName,
            'HAVING distance < :radius',
            '',
            'distance',
            ':limit '
        );
    }
    return $this->statement;
}

and later, in my find* method:

    $query = $this->createQuery();
    $statement = $this->getPreparedStatement();
    $statement->bindValues([
        ':meanradius' => CoordinateInterface::EARTH_MEAN_RADIUS,
        ':latitude' => $latitude,
        ':longitude' => $longitude,
        ':radius' => $radius,
        ':limit' => $limit,
    ]);
    $query->statement(($statement));
    return $query->execute();