Let's Talk

Compare two fields with each other in Shopware DAL

March 11, 2024 - Reading time: 5 minutes

Cover Image

This is a solution to add a filter with the Shopware DAL where you compare 2 fields with each other like this in shopware SELECT * FROM `product` WHERE `stock` > `available_stock`

Create a CustomFilter.php file in your custom plugin and add the following code:

<?php declare(strict_types=1);

namespace [YOUR_PLUGIN_NAME]\Core\Framework\DataAbstractionLayer\Search\Filter;

use Shopware\Core\Framework\Log\Package;

class CustomFilter extends SingleFieldFilter
{
    public function __construct(
        protected ?string $resolved
    ) {
    }

    public function getResolved(): ?string
    {
        return $this->resolved;
    }

    public function setResolved(string $resolved): void
    {
        $this->resolved = $resolved;
    }

    public function getFields(): array
    {
        return [];
    }

    public function getField(): string
    {
        return '';
    }
}

From the CustomFilter, you can add the query you want to the criteria like the following

$singleFieldFilter = new CustomFilter("`product`.`stock` > `product`.`available_stock`");
$criteria->addFilter($singleFieldFilter);

Or you can add a complex filter like

WHERE abc IN (SELECT id FROM product WHERE stock < 1);