3

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?

2
  • Hello @krybbio i want to add one custom filed of comment history in order export can u help me..? May 14 '20 at 10:28
  • @ShanHaider you need to add your custom field in grid an then use the same logic in aroundGetReport. Pay attention to the answer in this post: return $this->collection; must be located outside the if statment.
    – krybbio
    May 14 '20 at 15:42
2

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.

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.