What is the Magento ORM equivalent of “SELECT DISTINCT attribute FROM products”?

Question

I need to retrieve a list of all values which are used for a specific product attribute, in (pseudo) SQL:

SELECT DISTINCT attribute FROM products;

How would I use the Magento ORM to generate an equivalent query? I've tried the distinct() function but it doesn't perform as I'd expect:

// Returns an array of NULL with a length equal to all products in the catalog
Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect('attribute')
            ->distinct(true)
            ->getColumnValues('attribute');

What I'm working to get would be an array of attribute values, without duplicates

array('some value', 'some other value', 'a really common value', 'etc...');

Answer 1

Thanks to kalpesh, this is already blogged:

http://ka.lpe.sh/2011/06/06/magento-get-all-the-values-of-a-magento-eav-for-a-particular-attribute-code/

$attribute = Mage::getModel('eav/config')->getAttribute('catalog_product', 'color'); //here, "color" is the attribute_code
$allOptions = $attribute->getSource()->getAllOptions(true, true);
foreach ($allOptions as $instance) {
    $myArray[$instance['value']] = $instance['label'];
}
Mage::log($myArray);

Here is another solution: https://stackoverflow.com/a/15509714/1480397

But I'm not sure, whether this works on non-select attributes.

Answer 2

You can submit any SQL statements straight through the connection to acquire any data that is not accessible through magentos' api.

$db_resource = Mage::getSingleton('core/resource');
$db_connection = $db_resource->getConnection('core_write');
$sql = sprintf("SELECT DISTINCT attribute FROM `%s`", $db_resource->getTableName('product'));
$dataset = $db_connection->fetchAll($sql);

The query functions are fetchRow and fetchAll and are structured:

fetchAll($structured_sql, $bind_filters = array(), $fetchMode = null)