CODEWITHSUNDEEP
sqlmagentofilterleft-joinwhere-clause
Akshay Vasu
Akshay Vasu

Reputation: 445

How to add a where clause with the left join

I have written a code to retrieve the city, email, street and other details of the customers from the table order_address the issues here is for every order there are two rows where for one row the address_type is shipping and for another it is billing. When I query the system is automatically retrieving the data from the second row that is billing. I want the data of the row with the address type shipping. How can achieve this? Can anyone let me know how can i use the where clause here to filter the rows only with the address_type="shipping".

$select = $collection->getSelect();
            $select->joinLeft(array('payment' => $collection->getTable('sales/order_payment')), 'payment.parent_id=main_table.entity_id', array('payment_method' => 'method'));
            $select->joinLeft(array('email' => $collection->getTable('sales/order_address')), 'email.parent_id=main_table.entity_id', array('customer_email' => 'email','customer_phone' => 'telephone','shipping_street' => 'street','shipping_city' => 'city','shipping_postcode' => 'postcode','shipping_region' => 'region','shipping_country' => 'country_id'));
            $select->join('sales_flat_order_item', '`sales_flat_order_item`.order_id=`main_table`.entity_id', array('skus' => new Zend_Db_Expr('group_concat(`sales_flat_order_item`.sku SEPARATOR ", ")')));
            $select->group('main_table.entity_id');
        }

Upvotes: 0

Views: 803

Answers (1)

user247217
user247217

Reputation: 394

Try below code hope this helps. I just added email.address_type=shipping to your code at line#3.

$select = $collection->getSelect();
            $select->joinLeft(array('payment' => $collection->getTable('sales/order_payment')), 'payment.parent_id=main_table.entity_id', array('payment_method' => 'method'));
            $select->joinLeft(array('email' => $collection->getTable('sales/order_address')), 'email.parent_id=main_table.entity_id AND email.address_type="shipping"', array('customer_email' => 'email','customer_phone' => 'telephone','shipping_street' => 'street','shipping_city' => 'city','shipping_postcode' => 'postcode','shipping_region' => 'region','shipping_country' => 'country_id'));
            $select->join('sales_flat_order_item', '`sales_flat_order_item`.order_id=`main_table`.entity_id', array('skus' => new Zend_Db_Expr('group_concat(`sales_flat_order_item`.sku SEPARATOR ", ")')));
            $select->group('main_table.entity_id');
        }

Upvotes: 1

Related Questions