I tried getting all products of all websites via the ProductCollection like this:

        $stores = $this->storeManager->getStores();

        foreach ($stores as $store) {

            try {
                $productCollection = $this->productCollectionFactory->create();
                $storeEnabledOutOfStockProducts = $productCollection
//                    ->addAttributeToFilter('status', Status::STATUS_ENABLED)
//                    ->joinField('stock_item', 'cataloginventory_stock_item', 'qty', 'product_id=entity_id', 'qty=0')
                $enabledOutOfStockProducts += $storeEnabledOutOfStockProducts;

            } catch (LocalizedException $e) {

The sql that i got with ->getSelect()->assemble() looked like this and contained all the products. The Array of productItems however is incomplete.

SELECT `e`.* FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_website` AS `product_website` ON product_website.product_id = e.entity_id AND product_website.website_id = 1

Also I noticed that without this line..


.. the sql looks like this and returns all 6 products in the resultset. But I get only 5 ProductItems.

SELECT `e`.* FROM `catalog_product_entity` AS `e`

I also tried getting all products via ProductRepository and then filtering manually, but there I can not filter the Products by Website/Store. See Magento 2: Can you Filter By store_id Using a Product Repository

1 Answer 1


nice name.

You need to load() the collection first. You don't need assemble, instead what you can do is:

print $collection->load()->getSelectSql(true);

to get the loaded SQL Query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.