Magento product title char_length limit

Question

I am trying to retrieve a collection of products where the title (name) of the product is more than N characters. So far I am trying to do this:

 $collection = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSelect('name');

 $collection->getSelect()->where("CHAR_LENGTH(`e.name`) > 70");

The query it would output is:

SELECT `e`.* FROM `catalog_product_entity` AS `e` WHERE (CHAR_LENGTH(`e.name`) > 70)

It currently throws an error as it does not understand what the name column actually is

Column not found: 1054 Unknown column 'e.name' in 'where clause'

I could loop through all the products checking the char length, but it would not be efficient. I am trying to find a purely MySQL solution to this without writing out the ->query(...).

Answer 1

It turned out to be a bit more trickier based on the joins that I had to make on my own. I knew the SQL that I had to run, it looks like this:

SELECT value, CHAR_LENGTH( value ) AS 'length'
FROM catalog_product_entity_varchar
WHERE entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code =    'catalog_product')
AND attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND     entity_type_id = (SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'))
AND CHAR_LENGTH(value) > 70;

The magento way:

    //calculate attribute id of title
    $attr = Mage::getSingleton("eav/config")->getAttribute('catalog_product', 'entity_type_id')->getEntityTypeId();
    $col = Mage::getModel('eav/entity_attribute')
               ->getCollection()
               ->addFieldToFilter('attribute_code', 'name')
               ->addFieldToFilter('entity_type_id', $attr);
    $attr_id = $col->getFirstItem()->getAttributeId();

    //get products
    $collection = Mage::getModel('catalog/product')->getCollection();
    $collection->getSelect()
               ->join( array('cat_varchar'=> 'catalog_product_entity_varchar'), 'cat_varchar.entity_id = e.entity_id', array('cat_varchar.value'))
               ->where("`cat_varchar`.`attribute_id` = {$attr_id}")
               ->where("CHAR_LENGTH(`cat_varchar`.`value`) > 70");