I'm using the following to get a list of bestselling products:

$collection = $this->_resourceFactory->create('Magento\Sales\Model\ResourceModel\Report\Bestsellers\Collection');

However, this includes products with a visibility of Not Visible Individually. I've tried to filter them out by using the following:

$collection->join('catalog_category_product_index', 'catalog_category_product_index.product_id = entity_id', '')->addFieldToFilter('visibility', array('neq' => Visibility::VISIBILITY_NOT_VISIBLE));

But this gives a Column 'store_id' in where clause is ambiguous error due to the (store_id IN(0)) AND (store_id IN(0)) condition added by Magento\Reports\Model\ResourceModel\Report\Collection\AbstractCollection::_applyStoresFilterToSelect().

Could anyone explain how I can add the table name to the store_id column in that filter – or a better way for me to filter out Not Visible Individually products from the bestsellers list?

  • See this answer.
    – oscarpas
    Nov 7, 2016 at 16:00
  • setVisibility is an undefined method (as is addAttributeToFilter).
    – Michael
    Nov 7, 2016 at 16:08

1 Answer 1


I found the solution. I had to copy the


class into


and make the following changes:

namespace Vendor\MagentoSales\Model\ResourceModel\Report\Bestsellers;

 * @SuppressWarnings(PHPMD.CouplingBetweenObjects)
class Collection extends \Magento\Sales\Model\ResourceModel\Report\Collection\AbstractCollection

   * Apply stores filter to select object
   * @param \Magento\Framework\DB\Select $select
   * @return $this
  protected function _applyStoresFilterToSelect(\Magento\Framework\DB\Select $select)
      $nullCheck = false;
      $storeIds = $this->_storesIds;

      if (!is_array($storeIds)) {
          $storeIds = [$storeIds];

      $storeIds = array_unique($storeIds);

      if ($index = array_search(null, $storeIds)) {
          $nullCheck = true;

      if ($this->_from || $this->_to) {
        $mainTable = $this->getTable($this->getTableByAggregationPeriod('daily'));
      } else {
        $mainTable = $this->getTable($this->getTableByAggregationPeriod('yearly'));

      if ($nullCheck) {
          $select->where("$mainTable.store_id IN(?) OR $mainTable.store_id IS NULL", $storeIds);
      } else {
          $select->where("$mainTable.store_id IN(?)", $storeIds);

      return $this;

  // Rest of class...


And then set the preference in code/Vendor/MagentoSales/etc/di.xml (along with the relevant module.xml, composer.json, and registration.php files).

<?xml version="1.0"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
  <preference for="Magento\Sales\Model\ResourceModel\Report\Bestsellers\Collection" type="Vendor\MagentoSales\Model\ResourceModel\Report\Bestsellers\Collection" />

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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