Now the real question I need to create custom reports based on conditions.

  1. Sales Report
  2. Marketing Report
  3. Customer Report
  4. Product Report

. Example:


  1. How many products or list of products which sold more than 200 times.

  2. I need to show it graphically in Charts (comparision,pie) e.g https://google-developers.appspot.com/chart/

I have already develop extension how can I use google charts ?


raw sql to get all product ids which were sold more than 200 times:

SELECT e.entity_id FROM magento2test.catalog_product_entity as e INNER JOIN magento2test.sales_order_item as soi 
ON e.entity_id = soi.product_id GROUP BY e.entity_id HAVING COUNT(soi.product_id) > 200;

Magento code to add needed changes to the product collection's select:

$reportCountVal = 200;
$collection = $this->productCollectionFactory->create();
                ['soi' => $collection->getResource()->getTable('sales_order_item')],
                'e.entity_id = soi.product_id'
            ->having('COUNT(soi.product_id) > ?', $reportCountVal);

Put into $this->productCollectionFactory product collection's factory auto generated magento 2 factory instance using automatic DI feature

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.