Magento join collection query

  • I have this line:

      '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:

      `sales_flat_invoice_grid` AS `main_table` 
      main_table.entity_id = sales_flat_order_address.parent_id 
      (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.

    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.

    I've updated my question with the actual query. Any help is appreciated!

  • 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:

    // 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'");

License under CC-BY-SA with attribution

Content dated before 7/24/2021 11:53 AM