[Shopware6]: How can I add SQL Filter to Criteria?

Question

So, the criteria are already quite powerful. Yet I came across a case I seem to not be able to replicate on the criteria object.

I needed to filter out all entries that were not timely relevant.
In a world, where you'd be able to mix SQL with the field definition, it would look like this:

...->addFilter(
    new RangeFilter('DATEDIFF(NOW(), INTERVAL createdAt DAY)', [RangeFilter::LTE => 1])
)

Unfortunately that doesn't work in our world. When i pass the criteria to a searchfunction, i only get:
"DATEDIFF(NOW(), INTERVAL createdAt DAY)" is not a field on xyz

I tried to do it with ->addExtensions and several other experiments, but i couldn't get it to work. I resorted to using the queryBuilder from Doctrine, using queryParts, but the data i'm getting is not very clean and not assigned to an ORMEntity like it should be.

Is it possible to write a criteria that incooperates native SQL filtering?

Answer 1

The DAL is designed in a way that should explicitly not accept SQL statements as it is a core concept of the abstraction. As the DAL offers extendibility for third party extensions it should be preferred to raw SQL in most cases. I would suggest writing a lightweight query that only fetches the IDs using your SQL query and then use these pre-filtered IDs to fetch complete data sets using the DAL.

$ids = (new QueryBuilder($connection))
    ->select(['LOWER(HEX(id))'])
    ->from('product')
    ->where('...')
    ->execute()
    ->fetchFirstColumn();

$criteria = new Criteria($ids);

This should offer the best of both worlds, the freedom of using raw SQL and the extendibility features of the DAL.

In your specific case you could also just take the current day, remove the amount of days that should have passed and use this threshold date to compare it to the creation date:

$now = new \DateTimeImmutable();
$dateInterval = new \DateInterval('P1D');
$thresholdDate = $now->sub($dateInterval);
// filter to get all with a creation date greater than now -1 day
$filter = new RangeFilter(
    'createdAt',
    [RangeFilter::GTE => $thresholdDate->format(Defaults::STORAGE_DATE_TIME_FORMAT)]
);