Magento 2 - the grid columns are empty after joining table

Question

I have two tables: A & B . I want to display two columns from table B in my grid. The column names are: Price (See img) and Status(which is missing from the img, but it's there :) ).

In my resource model I added this function:

protected function _initSelect()
{
    parent::_initSelect();

    $this->getSelect()->joinLeft(
        ['secondTable' => $this->getTable('B')],
        'main_table.entity_id = secondTable.package_id',
        ['price','status']
    );
}

In the result of the query I am getting values for my price and status column. But the rows from the the Price column are empty.

This is the xml part from the ui component:

<column name="max_weight" sortOrder="70">
    <settings>
        <filter>text</filter>
        <label translate="true">Max Weight</label>
    </settings>
</column>
<column name="price" sortOrder="80">
    <settings>
        <filter>text</filter>
        <label translate="true">Price</label>
    </settings>
</column>

Can someone point me to a direction how to fix this ? or What am I missing? Thnx

Answer 1

In Your

Vendor\Module\Model\ResourceModel\Module\Collection.php

add this function

    protected function _initSelect()
    {
        parent::_initSelect();
        $this->getSelect()->joinLeft(
            'secondTable' => $this->getTable('B')],
            'main_table.entity_id = secondTable.package_id',
            ['price','status']
        );
    }

Answer 2

I needed to update my di.xml file, into this:

    <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <arguments>
            <argument name="collections" xsi:type="array">
                <!-- Please check the ui component: <dataProvider name='vendor_module_packages_grid_data_source' -->
                <item name="vendor_module_packages_grid_data_source" xsi:type="string">Vendor\Module\Model\ResourceModel\Packages\Grid\Collection</item> 
            </argument>
        </arguments>
    </type>
    <type name="Vendor\Module\Model\ResourceModel\Packages\Grid\Collection">
        <arguments>
            <argument name="mainTable" xsi:type="string">vendor_module_packages</argument>
            <argument name="eventPrefix" xsi:type="string">packages_ui_grid_collection</argument>
            <argument name="eventObject" xsi:type="string">packages_grid_collection</argument>
            <argument name="resourceModel" xsi:type="string">Vendor\Module\Model\ResourceModel\Packages</argument>
        </arguments>
    </type>

Side note: If you used before, you should remove it.

Next, I needed to create a new file in the app/code/Vendor/Module/Model/ResourceModel/Packages/Grid/Collection.php. Here is the content of it:

<?php
namespace Vendor\Module\Model\ResourceModel\Packages\Grid;

use Magento\Framework\Api\SearchCriteriaInterface;
use Magento\Framework\Api\Search\SearchResultInterface;
use Magento\Framework\DB\Adapter\AdapterInterface;
use Magento\Framework\Data\Collection\Db\FetchStrategyInterface;
use Magento\Framework\Data\Collection\EntityFactoryInterface;
use Magento\Framework\Event\ManagerInterface as EventManagerInterface;
use Magento\Framework\Model\ResourceModel\Db\AbstractDb;
use Magento\Framework\Api\Search\AggregationInterface;
use Psr\Log\LoggerInterface;
use Vendor\Module\Model\ResourceModel\Packages\Collection as PackagesCollection;

class Collection extends PackagesCollection implements SearchResultInterface
{
    /**
     * Aggregations
     *
     * @var AggregationInterface
     */
    protected $aggregations;

    /**
     * constructor
     *
     * @param EntityFactoryInterface $entityFactory
     * @param LoggerInterface $logger
     * @param FetchStrategyInterface $fetchStrategy
     * @param EventManagerInterface $eventManager
     * @param string $mainTable
     * @param string $eventPrefix
     * @param string $eventObject
     * @param string $resourceModel
     * @param AdapterInterface $connection
     * @param AbstractDb $resource
     * @param $model
     */
    public function __construct(
        EntityFactoryInterface $entityFactory,
        LoggerInterface $logger,
        FetchStrategyInterface $fetchStrategy,
        EventManagerInterface $eventManager,
        $mainTable,
        $eventPrefix,
        $eventObject,
        $resourceModel,
        AdapterInterface $connection = null,
        AbstractDb $resource = null,
        $model = 'Magento\Framework\View\Element\UiComponent\DataProvider\Document'
    )
    {
        parent::__construct($entityFactory, $logger, $fetchStrategy, $eventManager, $connection, $resource);
        $this->_eventPrefix = $eventPrefix;
        $this->_eventObject = $eventObject;
        $this->_init($model, $resourceModel);
        $this->setMainTable($mainTable);
    }


    /**
     * @return AggregationInterface
     */
    public function getAggregations()
    {
        return $this->aggregations;
    }

    /**
     * @param AggregationInterface $aggregations
     * @return $this
     */
    public function setAggregations($aggregations)
    {
        $this->aggregations = $aggregations;
    }


    /**
     * Retrieve all ids for collection
     * Backward compatibility with EAV collection
     *
     * @param int $limit
     * @param int $offset
     * @return array
     */
    public function getAllIds($limit = null, $offset = null)
    {
        return $this->getConnection()->fetchCol($this->_getAllIdsSelect($limit, $offset), $this->_bindParams);
    }

    /**
     * Get search criteria.
     *
     * @return \Magento\Framework\Api\SearchCriteriaInterface|null
     */
    public function getSearchCriteria()
    {
        return null;
    }

    /**
     * Set search criteria.
     *
     * @param SearchCriteriaInterface $searchCriteria
     * @return $this
     * @SuppressWarnings(PHPMD.UnusedFormalParameter)
     */
    public function setSearchCriteria(SearchCriteriaInterface $searchCriteria = null)
    {
        return $this;
    }

    /**
     * Get total count.
     *
     * @return int
     */
    public function getTotalCount()
    {
        return $this->getSize();
    }

    /**
     * Set total count.
     *
     * @param int $totalCount
     * @return $this
     * @SuppressWarnings(PHPMD.UnusedFormalParameter)
     */
    public function setTotalCount($totalCount)
    {
        return $this;
    }

    /**
     * Set items list.
     *
     * @param \Magento\Framework\Api\ExtensibleDataInterface[] $items
     * @return $this
     * @SuppressWarnings(PHPMD.UnusedFormalParameter)
     */
    public function setItems(array $items = null)
    {
        return $this;
    }
}

And Finally, I needed an extra method to my resource model: app/code/Vendor/Module/Model/ResourceModel/Packages/Collection.php. See:

protected function _initSelect()
{
    parent::_initSelect();
    $this->getSelect()->joinLeft(
        ['secondTable' => $this->getTable('another table')],
        'main_table.{entity_id} = secondTable.{another_table_id}',
        ['column1','column2']
    );
}