How to add a column in magento grid joining other table example shipment grid

Updated . Posted . Visible to the public.

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();
vasan
Last edit
vasan
Posted by vasan to vasan's deck (2021-02-20 14:39)