2

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
  • I realise this probably isn't that helpful as you've cut the query down, but your query isn't returning and columns from shop_locator_shop that aren't in shop_locator_media. – Richard May 29 '14 at 10:13
  • Thankyou your comment was very relevant! After reading your point I realised a better way to do this is by filtering the returned data based on the url param. ->addFieldToFilter('shop_id',array('eq' => $shop_id) This has solved my problem :D – tony09uk May 29 '14 at 12:46
4
$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

  • Hi, thanks for your response. Prior to adding the question I tried shp.shop_id = shop_locator_media.shop_id, this failed. The last example I have shown tries your suggestion of shp.shop_id = main_table.shop_id which returns all data from the media table. Am I missing a WHERE command? if so how/where do i add it? – tony09uk May 29 '14 at 10:27

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.