Sorting Attribute (with custom source model) by dropdown label

Question

I have a custom attribute called collection (in retrospect, a horrible name all things considered,) with a custom attribute source model. The important part is here:

public function getAllOptions() {
    if (is_null($this->_options)) {
        $this->_options = array();

        $rootCatId = Mage::app()->getStore(1)->getRootCategoryId();
        $rootCat = Mage::getModel('catalog/category')->load($rootCatId);

        $categories = Mage::getModel('catalog/category')->getCollection()
            ->addAttributeToSelect('name')
            ->addFieldToFilter('level', array('eq'=>3))
            ->addFieldToFilter('parent_id', array('in'=>explode(',', $rootCat->getChildren())));

        foreach ($categories as $category) {
            /** @var Mage_Catalog_Model_Category $category */
            $children = $category->getChildrenCategories();

            if ($children) {
                $subOptionArray = array();

                foreach ($children as $child) {
                    $subOptionArray[ $child->getId() ] = array(
                        'label' => trim($child->getName()),
                        'value' => $child->getId()
                    );
                }

                if (!empty($subOptionArray)) {
                    $this->_options[ $category->getId() ] = array(
                        'label' => trim($category->getName()),
                        'value' => $subOptionArray
                    );
                }
            }
        }
    }

    return $this->_options;
}

This gets a list of all level 4 categories, and lists them in <optgroup>'s under their parent category's name. Like so:

A
    1 => A1
    2 => A2
    3 => A3
B
    4 => B1
    5 => B2
    6 => B3

This works great in the Admin when selecting values for this attribute. The problem comes with sorting a collection by this attribute's value.

I'm using the following snippet to try and sort by collection:

->addAttributeToSelect('*')
->addAttributeToSort('collection', 'asc')

The problem is that this ends up sorting by the attribute's value, and not it's label. (The value here being the category IDs.) I need to be able to sort by the attribute's label, without having to convert the collection to an array and usort, or some post-load reflection sorting.

Any ideas what I'm missing? I feel like I'm missing something simple.

Answer 1

With $items being a collection, I created this to solve my problem:

// This builds the join that we need to join the category table based on the "collection" value
$items->addAttributeToSort('collection', 'asc');

$attrName = Mage::getModel('eav/entity_attribute')->loadByCode('catalog_category', 'name');
$attrNameId = $attrName->getId();
$attrNameTable = $attrName->getBackendTable();

$items->getSelect()
    // Join the category table so we can sort by name
    ->joinLeft(
        "{$attrNameTable} as at_category_name",
        "`at_category_name`.`entity_id` = `at_collection`.`value` AND `at_category_name`.`attribute_id` = '{$attrNameId}'",
        array('at_category_name.value as collection_name')
    )
    // Erase the "collection" attribute sorting
    ->setPart('order', '')
    // Order by the category name
    ->order(array(new Zend_Db_Expr('`at_category_name`.`value` ASC')));

I ended up joining to the category table since that's where my attribute sources it's data anyway.

I'm still hoping that there is a trick that I don't know to sort by the label of an attribute option, and not just the attribute option value, when the options are dynamic via custom source mode.