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
Improve this question
2
  • 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. Commented May 29, 2014 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 Commented May 29, 2014 at 12:46

1 Answer 1

Reset to default
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

Improve this answer
1
  • 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? Commented May 29, 2014 at 10:27

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.