5

I have this line:

$collection->getSelect()->join(
  'sales_flat_order_address', 
  'main_table.entity_id = sales_flat_order_address.parent_id', 
  array('telephone','city','postcode','country_id' ) 
)
->where("sales_flat_order_address.address_type =  'billing'");

The problem is that it works partially with joinLeft and "the other part" works with joinRight but if I use join it returns nothing(0 results).

And I'm showing the data like this:

$this->addColumn('telephone', array(
            'header' => Mage::helper('sales')->__('Telephone'),
            'index' => 'telephone',
            'filter_index' => 'sales_flat_order_address.telephone',
        ));

Magento version is 1.9.

The actual query:

SELECT 
  `main_table`.*, 
  `sales_flat_order_address`.`telephone`, 
  `sales_flat_order_address`.`city`, 
  `sales_flat_order_address`.`postcode` 
FROM 
  `sales_flat_invoice_grid` AS `main_table` 
RIGHT JOIN 
  `sales_flat_order_address` 
ON 
  main_table.entity_id = sales_flat_order_address.parent_id 
WHERE 
  (sales_flat_order_address.address_type = 'billing')

In this case it returns the proper data but the "LEFT" field are empty, because I'm using RIGHT JOIN I guess.

  • Can you show use the full query and the left/right joins. – David Manners Nov 9 '14 at 19:55
  • 1
    Try doing echo $collection->getSelect()->__toString(); then run the same SQL in mysql client I find it easier to debug what I am doing wrong that way. – James Cowie Nov 9 '14 at 20:49
  • I've updated my question with the actual query. Any help is appreciated! – Somebody Nov 9 '14 at 20:57
7

In your query main_table.entity_id is the ID of an invoice whereas sales_flat_order_address.parent_id is the ID of an order. It doesn't make sense to join on those two columns.

Instead you need to use the order ID of each invoice:

$collection->getSelect()->join(
    'sales_flat_order_address',
// note this join clause!
    'main_table.order_id = sales_flat_order_address.parent_id',
    array('telephone', 'city', 'postcode', 'country_id')
)
->where("sales_flat_order_address.address_type = 'billing'");

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.