Magento join collection query

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

    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:

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

License under CC-BY-SA with attribution


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