Search code examples
mysqlmagentohaving

Magento unknown column in having clause

I need to use having clause in Magento custom collection grid, I try to do this:

$store = Mage::app()->getStore($this->getStore());
$collection =   Mage::getResourceModel('catalog/product_collection')
                ->addAttributeToSelect('sku')
                ->addAttributeToSelect('name')
                ->addAttributeToSelect('small_image')
                ->addAttributeToFilter('status',1);
 $collection->joinTable(array('table_url' => 'thebot_url'),"table_url.product_id = entity_id", 
        array(
            "urls"              => new Zend_Db_Expr('group_concat(table_url.url)'), 
            "competitor_price"  => new Zend_Db_Expr('group_concat(table_url.competitor_price)'), 
            'rdy_shipping' => 'rdy_shipping', 
            'competitor_shipping_price' => 'competitor_shipping_price',
            'min_sell_price'    => new Zend_Db_Expr('min(ROUND((table_url.competitor_price + table_url.competitor_shipping_price),2))'),
            'rdy_price'     =>  new Zend_Db_Expr('round(((IF(at_price.value_id > 0, at_price.value, at_price_default.value)) + min(table_url.rdy_shipping)),2)')
            ), null,'left');
if ($store->getId())
{
$collection->addStoreFilter($store);
$collection->joinAttribute('custom_name', 'catalog_product/name',   'entity_id', null, 'inner', $store->getId());
$collection->joinAttribute('status', 'catalog_product/status', 'entity_id', null, 'inner', $store->getId());
$collection->joinAttribute('visibility', 'catalog_product/visibility', 'entity_id', null, 'inner', $store->getId());
$collection->joinAttribute('price', 'catalog_product/price', 'entity_id', null, 'left', $store->getId());
$collection->joinAttribute('manufacturer', 'catalog_product/manufacturer', 'entity_id', null, 'left', $store->getId());
}
else
{
$collection->addAttributeToSelect('price');
$collection->addAttributeToSelect('status');
$collection->addAttributeToSelect('manufacturer');
$collection->addAttributeToSelect('visibility');
}
$collection->addAttributeToFilter('visibility', array("neq" => 1));
$collection->getSelect()->group("e.entity_id");
$collection->getSelect()->having("rdy_price > min_sell_price AND min_sell_price > 0");

this error shows up:

Column not found: 1054 Unknown column 'rdy_price' in 'having clause'

The sql query is:

SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '80') AND (`at_status`.`store_id` = 0)
 INNER JOIN `catalog_product_website` AS `product_website` ON product_website.product_id = e.entity_id AND product_website.website_id = '10'
 INNER JOIN `catalog_product_entity_varchar` AS `at_custom_name_default` ON (`at_custom_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_custom_name_default`.`attribute_id` = '56') AND `at_custom_name_default`.`store_id` = 0
 INNER JOIN `catalog_product_entity_int` AS `at_visibility_default` ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = '85') AND `at_visibility_default`.`store_id` = 0
 LEFT JOIN `catalog_product_entity_int` AS `at_visibility` ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = '85') AND (`at_visibility`.`store_id` = '16') WHERE (at_status.value = '1') AND (IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) != 1) HAVING (rdy_price > min_sell_price AND min_sell_price > 0)

So why magento doesn't join the table "table_url" into this request (which is use I think to count results)?

Solution

  • UPDATE

    Short answer

    Use this code:

    $min_sell_price = 'min(ROUND((table_url.competitor_price + table_url.competitor_shipping_price),2))';
$rdy_price = 'round(((IF(at_price.value_id > 0, at_price.value, at_price_default.value)) + min(table_url.rdy_shipping)),2)';

$collection->getSelect()->having("{$rdy_price} > {$min_sell_price} AND {$min_sell_price} > 0");

    Explanation

    Finding the answer... By following the code

    First of all reason why your query fails: There is no GROUP BY, but there is a HAVING in the query.

    Very suspicious.

    At first I thought your code should more like the following, whilst the $collection->getSelect() part looks to be overwritten.

    $collection->getSelect()->group("e.entity_id")->having("rdy_price > min_sell_price AND min_sell_price > 0");

    Actually, a little more research learned that not the query you are providing is failing you, but Magento's built-in getSelectCountSql().

    Finding the other guy

    The guy on this post about problems with GROUP BY HAVING on a Magento Collection appears to have the same sort of problems.

    The solution for him was extending the getSelectCountSql()

    I have created an extended getSelectCountSql() in the custom collection class that adds back in the missing column required for the having statement.

    public function getSelectCountSql()
{
    $countSelect = parent::getSelectCountSql();  
    // Adding some custom features
    [...]
}

    Back to the source

    We have to track the method: Varien_Db_Select::getSelectCountSql(). Luckily for us, someone already did this for us.

    Loading a collection with HAVING works fine, but if you are using this in grid context, Magento might call getSelectCountSql() on the collection to get a modified query to retrieve the number of results.

    I don't know why this is not included in the default implementation, but essentially it means you cannot use HAVING with calculated columns but must repeat the expression:

    Original post

    Maybe you should have a look at this similar topic about joinTable

    Especially the following part - why I think your query isn't working:

    1. Table is easy, it is the magento namespace/entity format, which you use in your configuration, resource models and the collection. You can use an array of the format array('alias' => 'namespace/entity')

    I'm affraid your table thebot_url is a custom table. So you will need to add a resource model.

    This is an example how you would add this to a module

    <?xml version="1.0"?>
<config>
    ...
    <!-- The module you are using this query on -->
    <global>
        ...
        <models>
            <thebot_url>
                <class>Custom_TheBotUrl_Model</class>
                <resourceModel>thebot_url_mysql4</resourceModel>
            </thebot_url>
            <thebot_url_mysql4>
                <class>Custom_TheBotUrl_Model_Mysql4</class>
                <entities>
                    <myfirsttable>
                        <table>thebot_url</table>
                    </myfirsttable>                    
                </entities>
            </thebot_mysql4>
        </models>
        ...
    </global>
    ...
</config>

    So now you have a namespace thebot_url and an entity myfirsttable which will result in magento selecting <table>thebot_url</table>

    All together: In the first argument ($table) for joinTable() would be thebot_url/myfirsttable for this example

    For more detailed information, you might want to read this article about creating a custom module with a custom table for Magento.