Magento – Adding data to custom module grid using FK relationship

adminhtmlgridmagento2module

I've created a new module with a number of foreign keys to base magento db tables, i want to add data from the magento table (customer_entity) into my custom grid.

I have a FK relationship connecting custom_entity "customer_id" to "entity_id" in customer_entity. I would like to show both the Name and Email address of the user in the custom entity grid, how do i do this?

I understand in Magento 1 you would override prepareCollection(), how do i do this in Magento 2?

I've added the following 

    protected function _renderFiltersBefore() {
    $joinTable = $this->getTable('customer_entity');
    $this->getSelect()->join($joinTable.' as customer','amrita_practitioner.customer_id = customer.entity_id', array('*'));
    parent::_renderFiltersBefore();
}

and i'm trying to add the column as follows

<column name="email">
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="filter" xsi:type="string">text</item>
                <item name="label" xsi:type="string" translate="true">Email</item>
            </item>
        </argument>
    </column>

Can anyone shed any light on this, i'm having a number of problems using multiple models in one module, and any help would be greatly appreciated.

Thanks,

Sophie

Best Answer

I believe you can display the foreign table values by defining a class in the admin_html/ui_component/YOUR_GRID_XML_FILE as follows (GIVEN EXAMPLE IS FOR ADMIN USER with Access as the name of the controller / model )

 <column name="user_id" class="YOUR_COMPANY_NAMESPACE\MODULE_NAME\Ui\Component\Listing\Column\AccessActions" >
        <argument name="data" xsi:type="array">
            <item name="config" xsi:type="array">
                <item name="editor" xsi:type="array">
                    <item name="editorType" xsi:type="string">text</item>
                    <item name="validation" xsi:type="array">
                        <item name="required-entry" xsi:type="boolean">true</item>
                    </item>
                </item>
                <item name="filter" xsi:type="string">text</item>
                <item name="label" xsi:type="string" translate="true">User ID</item>
            </item>
        </argument>
    </column>

Once you have defined your user id field to a class we should create the file at the same path and form the data as follows: 

  <?php

namespace YOUR_COMPANY\MODULE_NAME\Ui\Component\Listing\Column;

use Magento\Framework\UrlInterface;
use Magento\Framework\View\Element\UiComponent\ContextInterface;
use Magento\Framework\View\Element\UiComponentFactory;
use Magento\Ui\Component\Listing\Columns\Column;

/**
 * Class TestActions
 */
class AccessActions extends Column
{

    /**
     * @var UserFactory
     */

    protected $userFactory;

    /**
     * Constructor
     *
     * @param ContextInterface $context
     * @param UiComponentFactory $uiComponentFactory
     * @param UserFactory $userFactory
     * @param array $components
     * @param array $data
     */
    public function __construct(
        ContextInterface $context,
        UiComponentFactory $uiComponentFactory,
        \Magento\User\Model\UserFactory $userFactory,
        array $components = [],
        array $data = []
    ) {
        $this->userFactory = $userFactory;
        parent::__construct($context, $uiComponentFactory, $components, $data);
    }

    /**
     * Prepare Data Source
     *
     * @param array $dataSource
     * @return array
     */
    public function prepareDataSource(array $dataSource)
    {
        if (isset($dataSource['data']['items'])) {
            foreach ($dataSource['data']['items'] as & $item) {
                if (isset($item['access_id'])) {


                       if($this->getData('name') == "user_id"){
                         $user = $this->userFactory->create()->load($item['user_id']);
                         $item[$this->getData('name')]=[$user->getUsername()];

                       }
                }
            }
        }

        return $dataSource;
    }
}

The above will post the user name instead of user id.

Related Topic