Query in Magento can be quite troublesome, if you are a starter..or non-starter. So I’ve tried to share some of the difficult queries using collection in Magento, so that it will be helpful to my blog readers and also a place where I can later refer to. Here is one of them, may not be dificult to all, but for me I had a very bad time initially, when I first started on Magento about a year ago. I had always thought to post on my blog, but when one of my collegue asked me about this I remembered and here’s the query.
$collection = Mage::getResourceModel('customer/customer_collection')
->addNameToSelect()
->addAttributeToSelect('email')
->addAttributeToSelect('created_at')
->addAttributeToSelect('group_id')
->joinAttribute('billing_street', 'customer_address/street', 'default_billing', null, 'left')
->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
->joinAttribute('billing_fax', 'customer_address/fax', 'default_billing', null, 'left')
->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left')
->joinAttribute('billing_country_code', 'customer_address/country_id', 'default_billing', null, 'left')
->joinAttribute('shipping_street', 'customer_address/street', 'default_shipping', null, 'left')
->joinAttribute('shipping_postcode', 'customer_address/postcode', 'default_shipping', null, 'left')
->joinAttribute('shipping_city', 'customer_address/city', 'default_shipping', null, 'left')
->joinAttribute('shipping_telephone', 'customer_address/telephone', 'default_shipping', null, 'left')
->joinAttribute('shipping_fax', 'customer_address/fax', 'default_shipping', null, 'left')
->joinAttribute('shipping_region', 'customer_address/region', 'default_shipping', null, 'left')
->joinAttribute('shipping_country_code', 'customer_address/country_id', 'default_shipping', null, 'left')
->joinAttribute('taxvat', 'customer/taxvat', 'entity_id', null, 'left');
Hope this helps somebody!
Thanks for great notes ,
I have some issue you might could help ,
$collection = Mage::getResourceModel(‘sales/order_collection’)
->addAttributeToSelect(‘*’)
->joinAttribute(‘billing_firstname’, ‘order_address/firstname’, ‘billing_address_id’, null, ‘left’)
->joinAttribute(‘billing_lastname’, ‘order_address/lastname’, ‘billing_address_id’, null, ‘left’)
->joinAttribute(‘shipping_firstname’, ‘order_address/firstname’, ‘shipping_address_id’, null, ‘left’)
->joinAttribute(‘shipping_lastname’, ‘order_address/lastname’, ‘shipping_address_id’, null, ‘left’)
->addExpressionAttributeToSelect(‘billing_name’,
‘CONCAT({{billing_firstname}}, ” “, {{billing_lastname}})’,
array(‘billing_firstname’, ‘billing_lastname’))
->addExpressionAttributeToSelect(‘shipping_name’,
‘CONCAT({{shipping_firstname}}, ” “, {{shipping_lastname}})’,
array(‘shipping_firstname’, ‘shipping_lastname’))
->joinAttribute(‘shipping_country_id’, ‘order_address/country_id’, ‘shipping_address_id’, null, ‘left’)
->addExpressionAttributeToSelect(‘international’,
‘(“US” = {{shipping_country_id}})’,
array(‘shipping_country_id’))
->joinField(‘item_product_id’, ‘sales/order_item’, ‘product_id’, ‘order_id=entity_id’, null, ‘left’)
->groupByAttribute(‘entity_id’);
// Add the presale column
$collection->getSelect()->joinLeft(
array(‘product_presale’=>’catalog_product_entity_int’),
‘product_presale.entity_id=`_table_item_product_id`.`product_id` and product_presale.attribute_id=’.$productattribute->getAttributeId(),
array(‘presale_item_count’=>new Zend_Db_Expr(‘SUM(product_presale.value)’))
);
When I m applying having method on presale.value its giving me error of sqlstate unknown column where its query work fine on phpmyadmin
could u help me in this
sajid
I guess you have already added product_presale as product’s attribute, I tried registering one such attribute with code “product_presale” and added its id in the query (second part) and the query ran well in phpmyadmin as well as it sent me collection values. The column retured, should be retreived using
foreach($collection as $coll){
print_r($coll->getPresaleItemCount());
}
As you can see in PHPMyadmin result column added presale_item_count. I think you did the hardest part and missed at last..or I didn’t understand what you want