mysql - having 子句中的 Magento 未知列

Question

我需要在 Magento 自定义集合网格中使用 having 子句，我尝试这样做:

$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");

显示此错误:

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

sql查询是:

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)

那么为什么 magento 不将表“table_url”加入到这个请求中(我认为这是用来计算结果的)？

Answer 1

更新

简答

使用这段代码:

$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");

解释

找到答案...按照代码

首先查询失败的原因:没有 GROUP BY , 但有一个 HAVING在查询中。

非常可疑。

起初我认为你的代码应该更像下面这样，而 $collection->getSelect()部分看起来被覆盖了。

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

实际上，更多的研究表明，不是您提供的查询让您失败，而是 Magento 的内置 getSelectCountSql() .

寻找另一个人

这篇文章中关于 problems with GROUP BY HAVING on a Magento Collection 的人似乎有同样的问题。

他的解决方案是扩展 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
    [...]
}

回到源头

我们要跟踪方法: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:

---

原帖

也许你应该看看这个类似的 topic about joinTable

尤其是以下部分 - 为什么我认为您的查询不起作用:

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')

我相信您的表格 thebot_url 是自定义表格。所以你需要添加一个资源模型。

这是一个如何将其添加到模块的示例

<?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>

现在你有了一个命名空间 thebot_url和一个实体 myfirsttable这将导致 magento 选择 <table>thebot_url</table>

一起:在第一个参数( $table )中 joinTable()将是 thebot_url/myfirsttable对于这个例子

有关更多详细信息，您可能需要阅读这篇关于 creating a custom module with a custom table for Magento 的文章.