mysql - magento 集合上的条件 joinLeft

Question

我的SQL查询语句如下:

SELECT * FROM `sales_flat_order_grid` AS `main_table` 
LEFT JOIN (SELECT * from `customer_address_entity_varchar` 
WHERE attribute_id=31) AS `ce3` 
ON main_table.customer_id=ce3.entity_id

我需要将其翻译成 Magento 风格。我试过了

$collection = Mage::getResourceModel('sales/order_grid_collection'); 
$tel = Mage::getModel('eav/entity_attribute')->loadByCode('2', 'telephone');

$collection->getSelect()  
    ->joinLeft(array('ce3' => 'customer_address_entity_varchar'), 'main_table.customer_id=ce3.entity_id', array('telephone' => 'value'))
    ->where('ce3.attribute_id='.$tel->getAttributeId())

但这不起作用，因为在连接后应用了 where 语句，导致查询总数小于 sales_flat_order_grid。

Answer 1

我相信这可以解决您的问题:

 $collection -> getSelect() -> joinLeft(array("oauth"=>'customer_entity_varchar'), 
'main_table.customer_id=oauth.entity_id and oauth.attribute_id = 156',
 array('OauthProvider' => "value"));