3

In my module I have custom db table that connected with magento customers by customer_id field. What I need is to add field value from this table to customer admin grid. How can I add join with my table and add my column value to customer grid collection, so it looks like other native grid fields with ability to sorting and filtering ?

6

I resolved it this way

Vendor\Module\view\adminhtml\ui_component\customer_listing.xml:

<listing xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="urn:magento:module:Magento_Ui:etc/ui_configuration.xsd">
 <columns name="customer_columns" class="Magento\Customer\Ui\Component\Listing\Columns">
    <column name="my_custom_column">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="label" xsi:type="string" translate="true">My Custom Column</item>
                <item name="sortOrder" xsi:type="number">35</item>
                <item name="filter" xsi:type="string">text</item>
                <item name="sortable" xsi:type="boolean">true</item>
            </item>
        </argument>
    </column>
  </columns>
</listing>

Vendor\Module\etc\di.xml:

<preference for="Magento\Customer\Model\ResourceModel\Grid\Collection"
            type="Magento\Customer\Model\ResourceModel\Grid\Collection2" />
<virtualType name="Magento\Customer\Model\ResourceModel\Grid\Collection2" type="Vendor\Module\Ui\Component\Listing\CustomerDataProvider">
    <arguments>
        <argument name="mainTable" xsi:type="string">customer_grid_flat</argument>
        <argument name="resourceModel" xsi:type="string">Magento\Customer\Model\ResourceModel\Customer</argument>
    </arguments>
</virtualType>

Vendor\Module\Ui\Component\Listing\CustomerDataProvider.php:

class CustomerDataProvider extends \Magento\Framework\View\Element\UiComponent\DataProvider\SearchResult
{
   protected function _initSelect()
   {
      parent::_initSelect();
      $this->getSelect()->joinLeft(
        ['secondTable' => $this->getTable('custom_table')],
        'main_table.entity_id = secondTable.customer_id',
        ['my_custom_column']
      );
      return $this;
  }
}
  • i will get a SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main_table.attribute_id' in 'on clause', query was: SELECT COUNT(*) FROM customer_grid_flat AS main_table LEFT JOIN eav_attribute ON eav_attribute.attribute_id = main_table.attribute_id – divya sekar Jun 19 at 11:28
1

I have created admin grid which has join of two custom tables. you cannot do this by using virtual type is di.xml, so you need to follow these steps and update your

etc/di.xml,

Model/Resource/Modulename/Collection.php add join in this file,

Model/Resource/Modulename/Grid/Collection.php,

For customer grid you need to modify the files accordingly, below is the explanation which will create joins of tables, you can follow these steps to have join between your custom table and existing customer table. and also update uicomponent to add column in grid

IN your etc/di.xml

<type name="Magento\Framework\View\Element\UiComponent\DataProvider\CollectionFactory">
        <arguments>
            <argument name="collections" xsi:type="array">
                <item name="namespace_modulename_listing_data_source" xsi:type="string">Namespace\Modulename\Model\Resource\Modulename\Grid\Collection</item>
            </argument>
        </arguments>
</type>
<type name="Namespace\Modulename\Model\Resource\Modulename\Grid\Collection">
    <arguments>
        <argument name="mainTable" xsi:type="string">tablename</argument>
        <argument name="eventPrefix" xsi:type="string">namespace_modulename_grid_collection</argument>
        <argument name="eventObject" xsi:type="string">namespace_grid_collection</argument>
        <argument name="resourceModel" xsi:type="string">Namespace\Modulename\Model\Resource\Modulename</argument>
    </arguments>
</type>

IN your Model/Resource/Modulename/Collection.php

<?php
namespace Namespace\Modulename\Model\Resource\Modulename;

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

class Collection extends AbstractCollection
{
    /**
     * Define model & resource model
     */
    const YOUR_TABLE = 'tablename';

    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(
            'Namespace\Modulename\Model\Modulename',
            'Namespace\Modulename\Model\Resource\Modulename'
        );
        parent::__construct(
            $entityFactory, $logger, $fetchStrategy, $eventManager, $connection,
            $resource
        );
        $this->storeManager = $storeManager;
    }
    protected function _initSelect()
    {
        parent::_initSelect();

        $this->getSelect()->joinLeft(
                ['secondTable' => $this->getTable('tablename')],
                'main_table.columnname = secondTable.columnname',
                ['columnname1','columnname2','columnname3']
            );
    }
}
?>

IN your Model/Resource/Modulename/Grid/Collection.php

<?php
namespace Namespace\Modulename\Model\Resource\Modulename\Grid;

use Magento\Framework\Api\Search\SearchResultInterface;
use Magento\Framework\Search\AggregationInterface;
use Namespace\Modulename\Model\Resource\Modulename\Collection as ModulenameCollection;

/**
 * Class Collection
 * Collection for displaying grid
 */
class Collection extends ModulenameCollection 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;
    }
}

?>

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.