Search code examples
shopware6

Shopware 6 Data Abstraction Layer: Compare two order fields/properties with each other

The goal of the code is to get all order entities that have the same value in two different fields/properties, in this case createdAt and updatedAt.

So far, it was not possible to find a solution for this use case, the only result was https://forum.shopware.com/t/dal-range-filter-mit-spalte/72770, which was sadly not solved.

I have tried this code, but was not successful:

// excerpt from a class
use Shopware\Core\Framework\Context;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Criteria;
use Shopware\Core\Framework\DataAbstractionLayer\Search\Filter\EqualsFilter;

$criteria = new Criteria();
$criteria->addFilter(
    new EqualsFilter('updatedAt', 'createdAt')
);

// $this->orderRepository is set in the constructor
$result = $this->orderRepository->search($criteria, Context::createDefaultContext())->getEntities();

$result should include all orders in an EntityCollection but in this case the code throws an exception which is expected since 'createdAt' is treated as a value, not a field path:

In PDOStatement.php line 117:

[PDOException (HY000)] SQLSTATE[HY000]: General error: 1525 Incorrect DATETIME value: 'createdAt'

How is it possible to compare fields/properties for entities inside the DAL?

Solution

  • At this time, you can't do this with just Shopware's own data abstraction layer. You could inject Doctrine\DBAL\Connection and use plain SQL to pre-filter ids and then use the abstraction layer to fetch full data sets.

    $ids = $this->connection->fetchFirstColumn(
    'SELECT LOWER(HEX(`id`)) FROM `order` WHERE `created_at` = `updated_at`'
);

$criteria = new Criteria($ids);
$orders = $this->orderRepository->search($criteria, $context)->getEntities();