Consent to the use of Personal Data and Cookies

This website needs your consent to use cookies in order to customize ads and content.

If you give us your consent, data may be shared with Google.

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.


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(
$criteria->getAssociation('seoUrls')->addFilter(new EqualsFilter('languageId', $languageId));

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

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

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.

Why it might happen

The ProductAvailableFilter class may have something to do with it. The class contains hardcoded values that seem to cause the search to only return active products. You can see that it is being loaded if you do this:



  1. inactive products should be displayed -
  2. Data Abstraction Layer -

Tell us what you think:

  1. Obtaining the host name from Shopware storefront and administration related code. E.g. From .twig and vue.js components.
  2. How SEO URLs work in Shopware, and how to customize them.
  3. Exclude redundant messages from your Shopware log files, exclude specific exceptions, and limit exception logging with error log levels using Monolog.
  4. This error is usually nothing to worry about. It tends to happen when losing internet connectivity.

More in: Shopware