Magento – Magento 2 – Join product collection to custom table

admin-panelmagento2uicomponent

I have created a custom module, that allows commenting, well now I have created admin grid and there is a little problem. Hope you can help!
My custom module's table:

comment_id | product_id | created_at | email | name | comment | status_id

And my adming grid:

date | product_name (which displays product_id for now) | name | comment | status

  1. So my first question is, how can I join product collection to my
    custom table, so I could be able to display product name in the product name column?

  2. And my second question is, I have table status_id in database table and values are 1 and 0, how can I display these ID'š in admin grid as "approved" and "not approved"?

I created my admin grid using UI component.

EDIT:
OK now I'm trying to do it like this. Collection class:

<?php

namespace Magebit\ProductComments\Model\ResourceModel\Comments;

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

class Collection extends AbstractCollection
{
    /**
     * Define model & resource model
     */
    protected function _construct()
    {
        $this->_init(
            'Magebit\ProductComments\Model\Comments',
            'Magebit\ProductComments\Model\ResourceModel\Comments'
        );
    }

    protected function initSelect()
    {
        parent::_initSelect();
        $this->getSelect()->joinleft(
            ['catalog_product_entity_varchar' => $this->getTable('catalog_product_entity_varchar')],
            'main_table.product_id = catalog_product_entity_varchar.entity_id',
            ['product_name' => 'value']
        );

        return $this;
    }
}

And then in ui component listing.xml:

<column name="product_name">
    <argument name="data" xsi:type="array">
        <item name="config" xsi:type="array">
            <item name="filter" xsi:type="string">text</item>
            <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="label" xsi:type="string" translate="true">Product Name</item>
        </item>
    </argument>
</column>

But it's not working, is there something else I should do?

Best Answer

For case 1, you can use the following code.

$collection = $this->_productCollectionFactory->create(); // get product collection
$collection->addAttributeToSelect('*');
$joinType = "left"; // join type
$where = ""; // put condition
$fields = "*"; // fields
$collection->joinTable(
    ['custom' => 'custom_table'],
    'custom.product_id = e.entity_id',
    $fields,
    $where,
    $joinType
);
Related Topic