Shopware DAL will not return inactive products?

I have been unable to return inactive products using Shopware DAL, so I came up with a simple SQL solution instead.

96 views

By. Jacob

Edited: 2023-01-28 13:00

It appears the Shopware DAL refuses to return an inactive product when requested by ID, but problem is, sometimes you may want to have inactive products returned. It makes no sense that it automatically decides to only return active products.

E.g. In this example I try to return a single product based on the product number. Afaik. We need to do a criteria search even when returning single product. It is a bit illogical, because what is otherwise a simple select * from product where id 0x[productId] now becomes this huge mess instead:

$context = Context::createDefaultContext();
$criteria = (new Criteria())->addFilter(
  new EqualsAnyFilter(
    'productNumber',
    [$productNumber]
    )
  );
$criteria->getAssociation('seoUrls')->addFilter(new EqualsFilter('languageId', $languageId));

$productResult = $this->productRepository->search($criteria, $context);

var_dump($productResult->first()); // "NULL"
exit();

I think this is just something you have to get used to. This is the Shopware way of doing things. And often it actually is the best way to do it. Still, I personally prefer SQL, because I know what it does. With the DAL, sometimes strange and unexpected things happen, like the fact that it does not return inactive products.

Of course, you should always try to use the DAL when possible, but you should also know that for specific things that does not involve language or sales channel, using SQL may be better and faster.

I was unable to make it return the inactive product in this instance, so instead I decided to make an exception and use the database directly. To do that you just need to include the connection from Shopware core. E.g. In your constructor:

public function __construct() {
  $this->connection = \Shopware\Core\Kernel::getConnection();
}

And here is a method to return the parent_id field of a specific product:

/**
 * Returns the parentId of a product by the product ID
 * Note. This method was necessary because DAL returns NULL when a product is inactive. E.g. the active field is "0" in the product table.
 * @param string $productNumber 
 * @return string|null 
 * @throws Exception 
 */
private function getParentId(string $productNumber): string | null {
  $result = $this->connection->executeQuery("select hex(parent_id) as parent_id from product where id = 0x" . $productNumber);
  if (false === ($productFields = $result->fetchAssociative())) {
    return null;
  }
  return $productFields['parent_id'];
}

In this instance I just needed to obtain the parent id of a specific product, so it should be totally fine to query the database directly. Now, let us just hope that the "product" table does not change name, because then I will have to modify the hardcoded table name in my query – something you also avoid if using the DAL :-p

I am sure what I want to do is also possible using the DAL, I just have not figured out the right way to do it yet. However, SQL is still more efficient anyway.

print_r($criteria->getFilters());exit();