Magento – How to join a collection

collection;table

On the face of it my question appears to be simple

How do I join a collection?

but there seems to be a good amount of information on this subject (so there seemed little need for this post), which is why I was very reluctant to put this question on. Finally after a day and a half of reading and trying different alternatives I am still unable to join a two table and return related media to my grid.

I figure this inability to join a collection comes from a lack of understanding in what I am trying to achieve. I have looked at many blog posts and questions on SE and believe what I am doing to be correct. I found this, this and this to be very helpful, but I cannot seem to return only the data I am after, I get all data held in my media table or nothing.

THE WORKING SQL

SELECT      shop_locator_media.shop_id, 
            shop_locator_media.is_default, 
            shop_locator_media.location, 
            shop_locator_shop.shop_id
FROM       `shop_locator_media`
INNER JOIN `shop_locator_shop`
ON          shop_locator_media.shop_id = shop_locator_shop.shop_id
WHERE       shop_locator_media.shop_id = 2

FAILED ATTEMPTS TO MAKE THE JOIN

    $collection = Mage::getModel('namespace_module/media')->getCollection();
    $collection->joinTable(
                 array('shop' => 'namespace_module/shop_locator_shop'), 'shop_id = shop_id',
                 array('shop_id' => 'shop_id')
    );
    //returns PHP Fatal error:  Call to undefined method namespace_module_Model_Resource_Media_Collection::joinTable()

    $collection = Mage::getModel('namespace_module/media')->getCollection();
    $collection->getSelect()->join(
        array('ls' => 'shop_locator_shop'),
        'main_table.shop_id = ls.shop_id',
        array('shop' => 'ls.shop_id')
    );
    //returns empty container shop_locator_shop and shop_locator_media shop_id both contain at least one row that will match
            e.g. shop_locator_shop.shop_id = 3 and shop_locator_media.shop_id = 3

    $collection = Mage::getModel('namespace_module/media')->getCollection();
    $collection->getSelect()->join(
            array('s'=>$this->getTable('namespace_module/shop_locator_shop')),
                  'main_table.shop_id = s.shop_id',
            array('s.shop_id'));
    //returns 1103 incorrect table name

    $collection = Mage::getModel('namespace_module/media')->getCollection();
    $collection->getSelect()->join(
            array('s'=>$this->getTable('namespace_module/shop')),
                  'main_table.shop_id = s.shop_id',
            array('s.shop_id'));
    //returns 1103 incorrect table name

    $collection = Mage::getModel('namespace_module/media')->getCollection();
    $collection->getSelect()->join(array('shp'=>'shop_locator_shop'),'shp.shop_id = shop_id');
    $collection->getItems();
    //return 1052 Column 'shop_id' in on clause is ambiguous

    $collection = Mage::getModel('namespace_module/media')->getCollection();
    $collection->getSelect()->join(array('shp'=>'shop_locator_shop'),'shp.shop_id = main_table.shop_id');
    $collection->getItems();
    //returns all data from shop_locator_media

Best Answer

$collection = Mage::getModel('namespace_module/media')->getCollection();
$collection->getSelect()->join(array('shp'=>'shop_locator_shop'),'shp.shop_id = shop_id');
$collection->getItems();
//return 1052 Column 'shop_id' in on clause is ambiguous

This fails because the second shop_id in shp.shop_id = shop_id isn't qualified by a table name. you can probably use shp.shop_id = main_table.shop_id or shp.shop_id = shop_locator_media.shop_id and it would work. You can check the correct table name or alias by dropping the sql out from the select and checking the from clause

--

Oh, sorry, I didn't you'd tried that on the next one, I think when you pull the select out it has a ->where method you can use. If you were filtering the collection directly you'd use addAttrbibuteToFilter

Related Topic