我想知道如何选择列。上面你可以看到我想在控制器中写的sql。但我想以正确的方式来做这件事。像magento中的其他sql语句一样。
我尝试添加->列("o.sku AS sku"),但没有成功。
有什么想法吗?提前感谢!
//sql
$readresult="SELECT o.name AS 'Product', o.sku AS 'SKU', c.entity_id AS 'Order', c.customer_email AS 'Email', c.customer_firstname AS 'Voornaam', c.customer_lastname AS 'Achternaam', c.customer_middlename AS 'Tussenvoegsel', o.product_options AS 'Options', a.telephone AS 'Telefoon', a.postcode AS 'Postcode', a.street AS 'Street', a.city AS 'City', c.increment_id AS 'Bestelnr' FROM magento_sales_flat_order AS c INNER JOIN magento_sales_flat_order_item AS o ON c.entity_id = o.order_id INNER JOIN magento_sales_flat_order_address AS a ON a.parent_id = c.entity_id WHERE o.product_id =".$product." GROUP BY c.entity_id ORDER BY o.sku;";
//THE MAGENTO WAY!
$resource = Mage::getSingleton('core/resource');
$read = $resource->getConnection('core_read');
$o = $resource->getTableName('magento_sales_flat_order');
$o_item = $resource->getTableName('magento_sales_flat_order_item');
$o_address = $resource->getTableName('magento_sales_flat_order_address');
$select = $read->select()->from(array('c'=>$o))
->join(array('o'=>$o_item), 'c.entity_id = o.order_id', array(o.sku AS 'SKU'))
->join(array('a'=>$o_address), 'a.parent_id = c.entity_id', array())
->where('o.product_id', $product)
->group('c.entity_id')
->order('o.sku');
您根本不会像这样处理核心模块。这些低级别SQL查询只能由资源模型使用。尤其是不在控制器内。
您应该通过调用Mage::getModel('sales/order')->getCollection()
来获得Mage_Sales_Model_Resource_Order_Collection
的实例,并从中构建查询。只要看一下源代码,就可以确切地了解如何做到这一点;从app/code/core/Mage/Sales/Model/Resource/Order/Collection.php开始,沿着继承链往下走。
例如,您可以使用类似$collection->addAttributeToSearchFilter()
的东西添加WHERE
。
Magento有很棒的数据库抽象,你一定会使用它。
希望这能有所帮助。