Magento 2: Join EAV attribute from product attribute set on a custom flat table

Question

Hi fellow programmers,

I would like to know how to do a join on my custom flat table (i.e. discountrules), I've implemented a generic Repository based on the Pestle archive, which implements the RepositoryInterface, it uses the DiscountRule Resource model and it's corresponding Collection, but I would like to join a attribute on this flat table within the Repository.

I can do a join using the $collection->getSelect()->join(... implementation, but I was wondering whether we can specify the EAV attribute and join them automatically.

I hope this question is valid and there's enough information to understand what I'm looking for, I hope someone can help me out!

Answer 1

I've managed to get a working solution where you can use the attribute set model ( or the full EAV ) simply by inversing the query, ie. instead of joining on the flat table, i'm in turn joining the flat table on the product object.

public function getDiscountRules($productId)
{
    $productCollection = $this->productCollectionFactory->create();
    $discountRuleTable = $this->productCollection->getConnection()->getTableName('discount_rules');
    $productCollection
        ->getSelect()
        ->join(
            ['dr' => $discountRuleTable],
            'dr.product_id ON e.entity_id'
        );

   $productCollection->addFieldToFilter('entity_id', $productId);
   return $productCollection->getItems();    
}

This simply joins the flat table on the product EAV and still gives you all the information you want, without having to join 4 tables just to join on the attributes for the products.