Magento 2: Collection getSelect does not load the results

Question

I have this method

  public function getCarBrands() {
    return $this->_objectManager->create('Scandimedia\Wheels\Model\CarModels')->getCollection()->getSelect()->group('brand');
}

But when I execute it and try to foreach the results with:

foreach($this->getCarBrands() as $model) {
echo $model->getData();

}

It does not do anything. But when I echo it returns:

SELECT `main_table`.* FROM `scandimedia_wheels_car_models` AS `main_table` GROUP BY `brand`

If I do get_class, it gives me this result: Magento\Framework\DB\Select

How can I actually load the results from the group and get the data without running the string as a query?

Answer 1

You need to add addFieldToSelect('*') to your collection.

You need to modify:

public function getCarBrands() {

    $collection =  $this->_objectManager->create('Scandimedia\Wheels\Model\CarModels')->getCollection();
    $collection->addFieldToSelect('*')
    $collection->getSelect()->group('main_table.brand');
    return $collection;
}

Answer 2

strong textWorking example with CustomerFactory

add in class properties protected $_customerFactory;

inject in constructor \Magento\Customer\Model\ResourceModel\Customer\CollectionFactory $customerFactory

Initialize $this->_customerFactory = $customerFactory;

$whereDobExpression = "Any custom expression" "e" can be used as alias for the main table example:

$whereDobExpression = "e.is_active = 1"

!IMPORTANT you need to add $customerCollection->addFieldToSelect('*'); it will not work without it

Answer 3

Yes sure, getSelect() will never return data. It is just Select Object, and there is no return data.

So, usually I use getSelect() as like query builder, to return a raw query use __toString() after getSelect() method, and than run the raw query.

Answer 4

You should use load() method

 $collection = $this->_orderCollectionFactory->create()->addFieldToSelect(
            new \Zend_Db_Expr("status ,COUNT(*) AS `num` ")
        );
 $collection->getSelect()->group('main_table.status');
 $orderData = $collection->load();