I have a M2 custom extension which fetches products with its sales data from database.
Current code:
/** @var $products \Magento\Catalog\Model\ResourceModel\Product\Collection $productCollection */
$products = $this->objectManager->create('Magento\Catalog\Model\ResourceModel\Product\Collection');
// ... adding some filters to $collection
// Enrich collection with sales data
/** @var \Magento\Framework\App\ResourceConnection $resource */
$resource = $objectManager->create('\Magento\Framework\App\ResourceConnection');
$ordersTableName = $resource->getTableName('sales_order_item');
$superTableName = $resource->getTableName('catalog_product_super_link');
if ($this->productHelper->isAttributeEnabled($additionalAttributes, 'ordered_qty')) {
$collection->getSelect()->columns('(SELECT SUM(qty_ordered) FROM ' . $ordersTableName . ' AS o LEFT JOIN ' . $superTableName . ' AS l ON l.product_id = o.product_id WHERE o.product_id = e.entity_id OR l.parent_id = e.entity_id) as ordered_qty');
}
// Process products
/** @var Product $product */
foreach ($collection as $product) {
// do something
}It works pretty well on both CE and EE. However when I turn on "split database" feature of M2 EE it breaks as sales data are located in a different database than products.
Error:
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magento21ee.sales_order_item' doesn't exist, query was: ...
The questions is - what is the best practice to handle that? Should I remove the sales data from the collection and perform a separate query to fetch sales data for each product? It seems to be really not effective.
Should I fetch all products, all sales data and then pair it which processing? It'll add additional complexity to the code, but will be much more effective than querying DB for each product.
Or is there any hidden Magento feature how to fetch products with sales data?
Thank a bunch for any help!
