Group collection by first letter

Question

I am trying to return a collection of brands in Magento that are grouped by the first letter of the title attribute. I have this but it results in a 500 error on the page in question:

public function getBrandGroupedCollection()
    {

        $brandsCollection = Mage::getModel('shopbybrand/brand')->getCollection();
        $brandsCollection
            ->addStatusFilter()
            ->setOrder('title', 'asc')
        ;
        $brandsCollection->getSelect()->group(substr('title',0,1)); 
        return $brandsCollection;
    }

How can I group the results by the first letter of the "title" attribute?

Answer 1

I think you'd have to perform an expression on the query into a new column that you may then aggregate on. The way you're doing it in the sample above would try to group by the results by the value of a column named t which does not exist.

Try this:

public function getBrandGroupedCollection()
{

    $brandsCollection = Mage::getModel('shopbybrand/brand')->getCollection();

    $brandsCollection
        ->addStatusFilter()
        ->setOrder('title', 'asc')
    ;

    $brandsCollection->getSelect()
        ->columns(new Zend_Db_Expr('SUBSTR(title, 1, 1) AS first_letter'))
        ->group('first_letter');

    // Optional debug output
    //echo $brandsCollection->getSelect()->assemble(); exit;

    return $brandsCollection;
}

So we rely on this dynamic column first_letter to be generated using MySQL's built-in SUBSTR [ref]. Notice that its index is 1-based (not zero). Now you can group by this column.

Note: Not sure if it will make a difference, but you could base your ordering then on first_letter instead of title