Join table for grid Magento 2 not working

Question

I tried to create a join to select data for my grid table in Magento 2 but the problem I am always getting is an empty record for alias that I wanted to use in my grid. Please refer the below image:

Here is the code that I am using to achieve this:

app/code/Lime/CustomerBank/etc/di.xml

<?xml version="1.0" ?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:framework:ObjectManager/etc/config.xsd">
    <preference for="Lime\CustomerBank\Api\BankRepositoryInterface" type="Lime\CustomerBank\Model\BankRepository"/>
    <preference for="Lime\CustomerBank\Api\Data\BankInterface" type="Lime\CustomerBank\Model\Bank"/>
    <preference for="Lime\CustomerBank\Api\Data\BankSearchResultsInterface" type="Magento\Framework\Api\SearchResults"/>
    <virtualType name="Lime\CustomerBank\Model\ResourceModel\Bank\Grid\Collection" type="Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult">
        <arguments>
            <argument name="mainTable" xsi:type="string">lime_customerbank_bank</argument>
            <argument name="resourceModel" xsi:type="string">Lime\CustomerBank\Model\ResourceModel\Bank\Collection</argument>
        </arguments>
    </virtualType>
    <virtualType name="CustomerBankGirdFilterPool" type="Magento\Framework\View\Element\UiComponent\DataProvider\FilterPool">
        <arguments>
            <argument name="appliers" xsi:type="array">
                <item name="regular" xsi:type="object">Magento\Framework\View\Element\UiComponent\DataProvider\RegularFilter</item>
                <item name="fulltext" xsi:type="object">Magento\Framework\View\Element\UiComponent\DataProvider\FulltextFilter</item>
            </argument>
        </arguments>
    </virtualType>
  <virtualType name="BankAccountGridDataProvider" type="Magento\Framework\View\Element\UiComponent\DataProvider\DataProvider">
      <arguments>
          <argument name="collection" xsi:type="object" shared="false">Lime\CustomerBank\Model\ResourceModel\BankAccount\Collection</argument>
          <argument name="filterPool" xsi:type="object" shared="false">CustomerBankGirdFilterPool</argument>
      </arguments>
  </virtualType>
    <type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <arguments>
            <argument name="collections" xsi:type="array">
                <item name="lime_customerbank_bank_grid_data_source" xsi:type="string">Lime\CustomerBank\Model\ResourceModel\Bank\Grid\Collection</item>
                <item name="lime_customerbank_bankaccount_grid_data_source" xsi:type="string">Lime\CustomerBank\Model\ResourceModel\BankAccount\Grid\Collection</item>
            </argument>
        </arguments>
    </type>
    <virtualType name="Lime\CustomerBank\Model\ResourceModel\BankAccount\Grid\Collection" type="Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult">
        <arguments>
            <argument name="mainTable" xsi:type="string">lime_customerbank_bankaccount</argument>
            <argument name="resourceModel" xsi:type="string">Lime\CustomerBank\Model\ResourceModel\BankAccount\Collection</argument>
             <argument name="eventPrefix" xsi:type="string">lime_customerbank_grid_collection</argument>
            <argument name="eventObject" xsi:type="string">lime_grid_collection\</argument>
        </arguments>
    </virtualType>
</config>

app/code/Lime/CustomerBank/view/adminhtml/ui_component/lime_customerbank_bankaccount_index.xml

<?xml version="1.0" ?>
<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
    <argument name="context" xsi:type="configurableObject">
        <argument name="class" xsi:type="string">Magento\Framework\View\Element\UiComponent\Context</argument>
        <argument name="namespace" xsi:type="string">lime_customerbank_bankaccount_index</argument>
    </argument>
    <argument name="data" xsi:type="array">
        <item name="js_config" xsi:type="array">
            <item name="provider" xsi:type="string">lime_customerbank_bankaccount_index.lime_customerbank_bankaccount_grid_data_source</item>
            <item name="deps" xsi:type="string">lime_customerbank_bankaccount_index.lime_customerbank_bankaccount_grid_data_source</item>
        </item>
        <item name="spinner" xsi:type="string">lime_customerbank_bankaccount_columns</item>
    </argument>
    <dataSource name="lime_customerbank_bankaccount_grid_data_source">
        <argument name="dataProvider" xsi:type="configurableObject">
            <argument name="class" xsi:type="string">BankAccountGridDataProvider</argument>
            <argument name="name" xsi:type="string">lime_customerbank_bankaccount_grid_data_source</argument>
            <argument name="primaryFieldName" xsi:type="string">bankaccount_id</argument>
            <argument name="requestFieldName" xsi:type="string">id</argument>
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="component" xsi:type="string">Magento_Ui/js/grid/provider</item>
                    <item name="update_url" path="mui/index/render" xsi:type="url"/>
                    <item name="storageConfig" xsi:type="array">
                        <item name="indexField" xsi:type="string">bankaccount_id</item>
                    </item>
                </item>
            </argument>
        </argument>
    </dataSource>
    <listingToolbar name="listing_top">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="sticky" xsi:type="boolean">true</item>
            </item>
        </argument>
        <bookmark name="bookmarks"/>
        <columnsControls name="columns_controls"/>
        <filters name="listing_filters"/>
        <paging name="listing_paging"/>
    </listingToolbar>
    <columns name="lime_customerbank_bankaccount_columns">
        <argument name="data" xsi:type="array"/>
        <selectionsColumn name="ids">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="indexField" xsi:type="string">bankaccount_id</item>
                </item>
            </argument>
        </selectionsColumn>
        <column name="bankaccount_id">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="sorting" xsi:type="string">asc</item>
                    <item name="label" translate="true" xsi:type="string">ID</item>
                </item>
            </argument>
        </column>
        <column name="name">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" translate="true" xsi:type="string">Bank Name</item>
                </item>
            </argument>
        </column>
        <column name="customer_name">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" translate="true" xsi:type="string">Customer</item>
                </item>
            </argument>
        </column>
        <column name="branch">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" translate="true" xsi:type="string">Branch</item>
                </item>
            </argument>
        </column>
        <column name="account_number">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" translate="true" xsi:type="string">Account Number</item>
                </item>
            </argument>
        </column>
        <column name="status_label">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="filter" xsi:type="string">text</item>
                    <item name="label" translate="true" xsi:type="string">Status</item>
                </item>
            </argument>
        </column>
        <actionsColumn class="Lime\CustomerBank\Ui\Component\Listing\Column\BankAccountActions" name="actions">
            <argument name="data" xsi:type="array">
                <item name="config" xsi:type="array">
                    <item name="indexField" xsi:type="string">bank_id</item>
                </item>
            </argument>
        </actionsColumn>
    </columns>
</listing>

app/code/Lime/CustomerBank/Model/BankAccount.php

<?php


namespace Lime\CustomerBank\Model;

use Lime\CustomerBank\Api\Data\BankAccountInterface;

class BankAccount extends \Magento\Framework\Model\AbstractModel implements BankAccountInterface
{

    protected $_eventPrefix = 'lime_customerbank_bankaccount';

    /**
     * @return void
     */
    protected function _construct()
    {
        $this->_init('Lime\CustomerBank\Model\ResourceModel\BankAccount');
    }

    /**
     * Get bankaccount_id
     * @return string
     */
    public function getBankaccountId()
    {
        return $this->getData(self::BANKACCOUNT_ID);
    }

    /**
     * Set bankaccount_id
     * @param string $bankaccountId
     * @return \Lime\CustomerBank\Api\Data\BankAccountInterface
     */
    public function setBankaccountId($bankaccountId)
    {
        return $this->setData(self::BANKACCOUNT_ID, $bankaccountId);
    }

    /**
     * Get bank_id
     * @return string
     */
    public function getBankId()
    {
        return $this->getData(self::BANK_ID);
    }

    /**
     * Set bank_id
     * @param string $bankId
     * @return \Lime\CustomerBank\Api\Data\BankAccountInterface
     */
    public function setBankId($bankId)
    {
        return $this->setData(self::BANK_ID, $bankId);
    }

    /**
     * Get customer_id
     * @return string
     */
    public function getCustomerId()
    {
        return $this->getData(self::CUSTOMER_ID);
    }

    /**
     * Set customer_id
     * @param string $customerId
     * @return \Lime\CustomerBank\Api\Data\BankAccountInterface
     */
    public function setCustomerId($customerId)
    {
        return $this->setData(self::CUSTOMER_ID, $customerId);
    }

    /**
     * Get branch
     * @return string
     */
    public function getBranch()
    {
        return $this->getData(self::BRANCH);
    }

    /**
     * Set branch
     * @param string $branch
     * @return \Lime\CustomerBank\Api\Data\BankAccountInterface
     */
    public function setBranch($branch)
    {
        return $this->setData(self::BRANCH, $branch);
    }

    /**
     * Get account_number
     * @return string
     */
    public function getAccountNumber()
    {
        return $this->getData(self::ACCOUNT_NUMBER);
    }

    /**
     * Set account_number
     * @param string $accountNumber
     * @return \Lime\CustomerBank\Api\Data\BankAccountInterface
     */
    public function setAccountNumber($accountNumber)
    {
        return $this->setData(self::ACCOUNT_NUMBER, $accountNumber);
    }

    /**
     * Get status
     * @return string
     */
    public function getStatus()
    {
        return $this->getData(self::STATUS);
    }

    /**
     * Set status
     * @param string $status
     * @return \Lime\CustomerBank\Api\Data\BankAccountInterface
     */
    public function setStatus($status)
    {
        return $this->setData(self::STATUS, $status);
    }
}

app/code/Lime/CustomerBank/Model/ResourceModel/BankAccount.php

<?php


namespace Lime\CustomerBank\Model\ResourceModel;

class BankAccount extends \Magento\Framework\Model\ResourceModel\Db\AbstractDb
{

    /**
     * Define resource model
     *
     * @return void
     */
    protected function _construct()
    {
        $this->_init('lime_customerbank_bankaccount', 'bankaccount_id');
    }
}

app/code/Lime/CustomerBank/Model/ResourceModel/BankAccount/Collection.php

<?php
namespace Lime\CustomerBank\Model\ResourceModel\BankAccount;

use Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection;

class Collection extends AbstractCollection
{

    const YOUR_TABLE = 'lime_customerbank_bank';

    public function __construct(
        \Magento\Framework\Data\Collection\EntityFactoryInterface $entityFactory,
        \Psr\Log\LoggerInterface $logger,
        \Magento\Framework\Data\Collection\Db\FetchStrategyInterface $fetchStrategy,
        \Magento\Framework\Event\ManagerInterface $eventManager,
        \Magento\Store\Model\StoreManagerInterface $storeManager,
        \Magento\Framework\DB\Adapter\AdapterInterface $connection = null,
        \Magento\Framework\Model\ResourceModel\Db\AbstractDb $resource = null
    ) {
        $this->_init(
            'Lime\CustomerBank\Model\BankAccount',
            'Lime\CustomerBank\Model\ResourceModel\BankAccount'
        );
        parent::__construct(
            $entityFactory, $logger, $fetchStrategy, $eventManager, $connection,
            $resource
        );
        $this->storeManager = $storeManager;
    }

    protected function _initSelect()
    {
        parent::_initSelect();
        $this->getSelect()->join(
                ['secondTable' => $this->getTable('customer_entity')],
                ' secondTable.entity_id = main_table.customer_id',
                [
                   'main_table.status' => 'IF(main_table.status = "1", "'.__("Active").'", "'.__("Inactive").'")',
                   'customer_name' => 'CONCAT(secondTable.firstname," ",secondTable.lastname)'
                ]
            );
        $this->getSelect()->join(
                ['thirdTable' => $this->getTable('lime_customerbank_bank')],
                ' thirdTable.bank_id = main_table.bank_id',
                [
                  'bank_name' => 'thirdTable.name',
                  'bank_status' => 'thirdTable.status',
                  'bank_status_label' => 'IF(thirdTable.status = "1", "'.__("Active").'", "'.__("Inactive").'")',
                ]
            );
    }
}

app/code/Lime/CustomerBank/Model/ResourceModel/BankAccount/Grid/Collection.php

<?php
namespace Lime\CustomerBank\Model\ResourceModel\BankAccount\Grid;

use Magento\Framework\Api\Search\SearchResultInterface;
use Magento\Framework\Search\AggregationInterface;
use Lime\CustomerBank\Model\ResourceModel\BankAccount\Collection as CustomerBankCollection;

/**
 * Class Collection
 * Collection for displaying grid
 */
class Collection extends CustomerBankCollection implements SearchResultInterface
{
    /**
     * Resource initialization
     * @return $this
     */
   public function __construct(
        \Magento\Framework\Data\Collection\EntityFactoryInterface $entityFactory,
        \Psr\Log\LoggerInterface $logger,
        \Magento\Framework\Data\Collection\Db\FetchStrategyInterface $fetchStrategy,
        \Magento\Framework\Event\ManagerInterface $eventManager,
        \Magento\Store\Model\StoreManagerInterface $storeManager,
        $mainTable,
        $eventPrefix,
        $eventObject,
        $resourceModel,
        $model = 'Magento\Framework\View\Element\UiComponent\DataProvider\Document',
        $connection = null,
        \Magento\Framework\Model\ResourceModel\Db\AbstractDb $resource = null
    ) {
        parent::__construct(
            $entityFactory,
            $logger,
            $fetchStrategy,
            $eventManager,
            $storeManager,
            $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;
    }


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

    /**
     * Set search criteria.
     *
     * @param \Magento\Framework\Api\SearchCriteriaInterface $searchCriteria
     *
     * @return $this
     * @SuppressWarnings(PHPMD.UnusedFormalParameter)
     */
    public function setSearchCriteria(
        \Magento\Framework\Api\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;
    }
}

?>

Answer 1

Have you tried without the joins to make sure you are getting data? Also try just the single join to ensure it is working.