Following steps explain how to add additional column in shipment grid by joining tables
1) Add a plugin
<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
<plugin name="antyra_extended_shipping_grid"
type="Vasan\Shipping\Model\Plugin\AddDataToShipmentGrid"
sortOrder="10"
disabled="false"/>
</type>
/**
* @param \Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory $subject
* @param OrderGridCollection $collection
* @param $requestName
* @return mixed
*/
public function afterGetReport($subject, $collection, $requestName)
{
if ($requestName !== 'sales_order_shipment_grid_data_source') {
return $collection;
}
if ($collection->getMainTable() === $collection->getResource()->getTable('sales_shipment_grid')) {
try {
$shipmentTrackingTableName = $collection->getResource()->getTable('sales_shipment_track');
$salesOrderAddress = $collection->getResource()->getTable('sales_order_address');
$salesOrderGrid = $collection->getResource()->getTable('sales_order_grid');
$salesCreditGrid = $collection->getResource()->getTable('sales_creditmemo_grid');
$collection->getSelect()->joinLeft(
['sst' => $shipmentTrackingTableName],
'sst.parent_id = main_table.entity_id',
['carrier_code','track_number','title']
);
$collection->getSelect()->joinLeft(
['sog' => $salesOrderGrid],
'main_table.order_increment_id = sog.increment_id',
['base_grand_total', 'payment_method']
);
$collection->getSelect()->joinLeft(
['soa' => $salesOrderAddress],
'soa.parent_id = main_table.order_id ',
['telephone']
)->where("soa.address_type = 'shipping'");
$collection->getSelect()->joinLeft(
['scg' => $salesCreditGrid],
'main_table.order_increment_id = scg.order_increment_id',
['refunded' => 'scg.base_grand_total']
);
$collection->addFilterToMap('refunded', 'scg.base_grand_total');
$collection->addFilterToMap('base_grand_total', 'sog.base_grand_total');
$collection->addFilterToMap('payment_method', 'sog.payment_method');
$collection->addFilterToMap('entity_id', 'main_table.entity_id');
} catch (\Zend_Db_Select_Exception $selectException) {
$this->logger->log(100, $selectException);
}
}
return $collection;
}
Another example of simple joining
$collection = $this->adminUserCollectionFactory->create();
$vendorTableName = $collection->getResource()->getTable('vasan_vendor_entity');
$collection->getSelect()->joinLeft(
['vve' => $vendorTableName],
'vve.email = main_table.email',
['entity_id']
)->where('entity_id = ?', $vendorId);
return $collection->getFirstItem();
Posted by vasan to vasan's deck (2021-02-20 14:39)