Magento-如何在排除给定列上的重复条目的同时加入两个表？

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.

我是Magento的实习生和新手。我觉得我已经开始掌握一切,但数据库访问功能对我来说仍然不是非常直观,除了MySQL不是我最强大的技能。

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.

现在,我正在创建一个模块,在后端,将显示一个自定义表,其中包含来自表'eav_attribute'的'frontend_label'和来自'eav_attribute_option_value'的'value'。我的表本身有对attribute_id和option_id的外键引用,是我将列连接到表的方法。

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.

如果为单个商店存储属性,我的查询工作正常。问题是'eav_attribute_option_value'为每个store_id都有重复的属性/选项对。也就是说,多个商店条目会导致我的表显示每个属性选项对的重复条目(我只希望每个属性选项对显示一次)。我想在我的查询中添加一个约束,以便下面的查询只返回属性选项对的唯一条目(实际上,忽略了属性/选项对在'eav_attribute_option_value'中重复的事实,因为它们是为每个选项存储的STORE_ID)。我的询问如下,但我希望有人可以指出我正确的方向,因为我目前卡住了。

Thanks everyone!

    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();
}

1 个解决方案

#1

I found the answer, I am able to group by "frontend_label" and "value" in order to avoid duplicate entries in this case. In my case, the code has now become:

我找到了答案,我能够通过“frontend_label”和“value”进行分组,以避免在这种情况下重复输入。在我的情况下,代码现在变成:

        $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')
        ->group('frontend_label','value');