How to merge data source results?

Question

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

Answer 1

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/