1

I have an M2 custom extension which fetches products with its sales data from the 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 are - 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!

1

I do not know the goal of your extension.

But if it is only for the statistic, you can create a new aggregation table with the computed data. You can see how Magento handle this on the reports.

You may need to check if you can query on different database, first link on Google: https://stackoverflow.com/questions/5145637/querying-data-by-joining-two-tables-in-two-database-on-different-servers

If you need to have more information how Magento 2 handle multiple databases, check these modules I think :

  • vendor/magento/framework-foreign-key (manage foreign key with multiple databases)
  • vendor/magento/module-scalable-oms
  • vendor/magento/module-scalable-inventory
  • vendor/magento/module-scalable-checkout
  • all constraints.xml files inside
  • all di.xml files inside

But I think they only handle cascade operation and not data retrieval between multiple databases.

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.