How to get all Products of all Websites/Stores?

Question

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
                    ->addAttributeToSelect('*')
                    ->addStoreFilter($store)
//                    ->addAttributeToFilter('status', Status::STATUS_ENABLED)
//                    ->joinField('stock_item', 'cataloginventory_stock_item', 'qty', 'product_id=entity_id', 'qty=0')
                    ->getItems();
                $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..

->addAttributeToSelect('*')

.. 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

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.