7

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:

enter image description here

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;
    }
}

?>
1

1 Answer 1

0

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

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.