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);

Chat with Hung on