StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMagento- How can I join a two tables while excluding duplicate entries on a given column?
text
Body
copied!<p>I'm an intern and pretty new to Magento. I feel like I'm starting to get the hang of things, but database access functions still aren't extremely intuitive for me, on top of MySQL not being my strongest skillset. </p> <p>Right now, I am creating a module that, on the backend, will display a custom table with rows containing 'frontend_label' from table 'eav_attribute' and 'value' from 'eav_attribute_option_value'. My table itself has foreign key references to attribute_id and option_id and is my means for joining the columns to my table.</p> <p>My query works fine if the attributes are stored for a single store. The catch is that 'eav_attribute_option_value' has duplicate attribute/option pairs for each store_id. That said, multiple store entries causes my table to display duplicate entries for each attribute option pair (I only want each attribute option pair to display once). I would like to add a constraint on my query such that the below query only returns unique entries for the attribute option pairs (in effect, ignoring the fact that the attribute/option pairs are duplicated in 'eav_attribute_option_value' due to them being stored for each store_id). My query is below, but I was hoping that someone could point me in the right direction, as I am currently stuck.</p> <p>Thanks everyone!</p> <pre><code> protected function _prepareCollection() { $collection = Mage::getModel('landing/management')->getCollection(); $collection->getSelect() ->join(array('eavattr' => 'eav_attribute'),'eavattr.attribute_id = main_table.attribute_id') ->join(array('opt'=>'eav_attribute_option_value'),'opt.option_id = main_table.option_id') ->reset(Zend_Db_Select::COLUMNS) ->columns(array('*')) ->columns(array('frontend_label'),'eavattr') ->columns(array('value'),'opt'); $this->setCollection($collection); return parent::_prepareCollection(); } </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload