Product collection join with custom table undefined column

Question

I am working with some product collection join with custom table, I've a custom product attribute, need to add a join condition with the product custom attribute with the custom table attribute.

The custom table is agreements and in product we're also has a custom attribute called agreement, when i tried to join as like below i am getting error like agreement is undefined field (agreement is a product custom attribute - select)

    $this->_collection->getSelect()->joinLeft(
                ['agreement_table'=>'agreements'],
                'e.agreement = agreement_table.id',
                []
   );

Thanks in advance for your better support.

Answer 1

Following code it working for me,

$productCollection = $this->productFactory->create()->getCollection();
$collection = $productCollection->create()->addAttributeToSelect(['sku' , 'entity_id' , 'agreement']);
$collection->getSelect()->joinLeft(array("agreement_table"=>"agreement_table"),"agreement_table.id=e.agreement",array("*"));
$collection->getSelect()->group(array('e.entity_id'));

Thanks & Regards,