HowTo: Join 2 tables in Magento 2

Recently I faced the situation where I needed the information of a second table in my collection and the first thought that crossed my mind was let's rewrite the collection to add my on stuff but... is that really the right approach?

First, by rewriting the core collection class I am going to affect all the places that collection is used, and I do not need that, second, the whole the idea of rewriting things feels wrong to me, specially having mechanisms like observers and plugins available to inject custom logic in a non-intrusive way, so that is probably NOT the best thing to do.

What is the best solution then?

I will explain the procedure I followed to achieve this.

First, create a class that extends your core collection class.

<?php

namespace Vendor\CompanyCatalog\Model\ResourceModel\Catalog\Product\Grid;

class Collection extends \Magento\Catalog\Model\ResourceModel\Product\Collection
{

}

Second, rewrite the _initSelect() method to insert the join.

<?php

namespace Vendor\CompanyCatalog\Model\ResourceModel\Catalog\Product\Grid;

class Collection extends \Magento\Catalog\Model\ResourceModel\Product\Collection
{
    protected function _initSelect()
    {
        parent::_initSelect();
        $this->joinCompanyCatalogProductTable();
        return $this;
    }

    private function joinCompanyCatalogProductTable()
    {
        $this->getSelect()->joinLeft(
            ['company' => $this->getTable(\Vendor\CompanyCatalog\Setup\InstallSchema::COMPANY_CATALOG)],
            'company.product_id = e.entity_id',
            ['company_id']
        );
    }
}

As you can see in here I am taking advantage of the _initSelect() method to join the catalog_product_entity table with my custom table.

At this point you might be thinking, this guy said rewrites feel wrong, and now he is basically rewriting the method _initSelect() of the core collection. Yes, that is right, BUT the difference is that I am not creating a preference in the di.xml, therefore the core collection is not going to be rewritten, instead I am going to tell my grid to use Vendor\CompanyCatalog\Model\ResourceModel\Catalog\Product\Grid\Collection.

Hope this helps.

If you have any question or know a better way to achieve this, feel free to share in the comments below.