Magento 2 Export CSV on Sales Order Grid with Custom Fields

Question

I succesfully added some custom fields on Sales Order Grid.

If I export "Excel XML" is working fine but with "CSV" custom fields are blank.

I have compared the following files:

Magento\Ui\Model\Export\ConvertToXml

and

Magento\Ui\Model\Export\ConvertToCSV

Both files get items collecction in the same way:

Magento\Ui\Model\Export\ConvertToXml

$searchResult = $component->getContext()->getDataProvider()->getSearchResult();
$searchResultItems = $searchResult->getItems();

and

Magento\Ui\Model\Export\ConvertToCSV

$dataProvider = $component->getContext()->getDataProvider();
$items = $dataProvider->getSearchResult()->getItems();

But the second one seems not joining the custom tables defined in Module plugin:

public function aroundGetReport(
    \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject,
    \Closure $proceed,
    $requestName
) {
    $result = $proceed($requestName);
    if ($requestName == 'sales_order_grid_data_source') {
        if ($result instanceof $this->collection
        ) {
            if(is_null($this->registry->registry('cc_added'))){
                $this->logger->debug(print_r($this->collection->getSelect()->getPart('columns'),true));
                $select = $this->collection->getSelect();
                $select->joinLeft(
                    ['soi' => 'sales_order_item'],
                    'soi.order_id = main_table.entity_id',
                    [
                    'items_skus'  => new \Zend_Db_Expr('group_concat(soi.sku SEPARATOR ",")'),
                    'items_names' => new \Zend_Db_Expr('group_concat(soi.name SEPARATOR ",")')
                    ]
                );                
                $select->joinLeft(
                    ['soa' => 'sales_order_address'],
                    'main_table.entity_id = soa.parent_id',
                    ['bill_to_country' => 'country_id']
                );                
                $select->joinLeft(
                    ['bot' => 'bileamara_order_tickets'],
                    'bot.order_id = main_table.entity_id',
                    ['has_ticket' => 'bot.nr_tickets']
                );                
                $select->where('`soa`.address_type = "billing" AND `soi`.product_type = "simple"');
                $select->group('main_table.entity_id');

                $this->collection->addFilterToMap(
                    'items_skus',
                    'soi.sku'
                );
                $this->collection->addFilterToMap(
                    'items_names',
                    'soi.name'
                );
                $this->collection->addFilterToMap(
                    'bill_to_country',
                    'soa.country_id'
                );
                $this->collection->addFilterToMap(
                    'has_ticket',
                    'bot.nr_tickets'
                );

                $this->registry->register('cc_added', true);

                return $this->collection;
            }
        }
    }
    return $result;
}

In fact, when I try to export the CSV by filtering through a custom field, I get the following error:

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'has_ticket' in 'where clause', query was: SELECT COUNT(*) FROM `sales_order_grid` AS `main_table` WHERE (`has_ticket` = '1') AND (`has_ticket` = '1')

Why Magento\Ui\Model\Export\ConvertToCSV dont't get joined tables like Magento\Ui\Model\Export\ConvertToXml?

Answer 1

In my case the problem was in plugin aroundGetReport since i use $this->registry->registry('cc_added') check.

Magento\Ui\Model\Export\ConvertToCSV calls $dataProvider->getSearchResult() more than once, so only the first one get the joined collection.

Move the return $this->collection; out of the if(is_null($this->registry->registry('cc_added'))) condition solve the issue.