2

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 :) ).

enter image description here

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

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']
        );
    }
| improve this answer | |
  • It is added in the class Collection extends \Magento\Framework\Model\ResourceModel\Db\Collection\AbstractCollection – Attila Naghi Nov 19 '19 at 10:56
  • Yes it should work – Waqar Ali Nov 19 '19 at 11:00
  • nope, it doesn't. Something is still missing :) . Can you share your thoughts , where should i look more ? :) – Attila Naghi Nov 19 '19 at 11:06
  • create an instance of this model and get a collection you will see data is fetching or not this is best way to debug – Waqar Ali Nov 19 '19 at 11:15
  • I get the query string: echo $this->getSelect(); and executed in my IDE, and i get the results. Maybe my ui component is not correct. not sure :( – Attila Naghi Nov 19 '19 at 11:48
1

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']
    );
}
| improve this answer | |

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.