0

I'm using UI components to list campaigns, and gettings the rows at collection class, it should return more than one row. Anyone knows how to merge the data to be displayed in the campaign_listing.xml So I can have just one row and the columns, in this case store_id can be together?

protected function _initSelect()
{
    parent::_initSelect(); 

    $this->getSelect()->join(
        ['secondTable' => $this->getTable('bi_loyalty_campaign_store')],
        'entity_id = secondTable.campaign_id',
        ['store_id']
        )->join(
        ['thirdTable' => $this->getTable('store_group')],
        'store_id = thirdTable.group_id',
        ['stores' => 'name']
    );
    $this->addFilterToMap('stores', 'thirdTable.name');

}

I want to transform this:

'3', 'exp', 'jkvjhfh', '2018-06-12', '2018-06-29', '1', '1', 'Main Website Store'`
'3', 'exp', 'jkvjhfh', '2018-06-12', '2018-06-29', '1', '2', 'loja2'

into this:

'3', 'exp', 'jkvjhfh', '2018-06-12', '2018-06-29', '1', '1,2', 'Main Website Store, loja2'

Currently I'm getting this error when listings campaigns with ui components:

Item (Magento\Framework\View\Element\UiComponent\DataProvider\Document) with the same ID "3" already exists.

Because there are more than one campaign with the same id.

The query that i would like to is:

SELECT main_table.*, group_concat(secondTable.store_id), group_concat(thirdTable.name) AS stores FROM bi_loyalty_campaigns AS main_table INNER JOIN bi_loyalty_campaign_store AS secondTable ON main_table.entity_id = secondTable.campaign_id INNER JOIN store_group AS thirdTable ON thirdTable.group_id =store_id GROUP BY campaign_id ORDER BY entity_id ASC

0

Finally got the answer!!

If someone need in the future it is really easy.

$this->getSelect()->join( ['campaignStores' => $this->getTable('bi_loyalty_campaign_store')], 'main_table.entity_id = campaignStores.campaign_id', ['storeId' => 'GROUP_CONCAT(campaignStores.store_id)'] )->join( ['stores' => $this->getTable('store_group')], 'campaignStores.store_id = stores.group_id', ['stores' => 'GROUP_CONCAT(stores.name)']);

Good site for future reference is: https://webkul.com/blog/magento2-mysql-join-explained/

| improve this answer | |

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.