8

I have a custom table user_product_table with a field sku(this field join to product collection) and user. How can I join this table with product collection, so that I don't have to loop my custom collection to get product in each row.

My $userCollection:

 $userCollection = $this->_userProductFactory->create()
        ->getCollection()
        ->addFieldToFilter('user', 2);

My product Collection:

$productColelction = $this->_productCollectionFactory->create()
        ->addAttributeToSelect(array('name','image','short_description','sku'))

I would like to have final collection both my custom table fields and selected product attributes.

1 Answer 1

14

We can use the join like this:

            $joinConditions = 'u.sku = e.sku';

            $productColelction->getSelect()->join(
                ['u' => $productColelction->getTable('user_product_table')],
                $joinConditions,
                []
            );

Multi conditions:

        $joinConditions[] = "u.sku = e.sku";

        $joinConditions[] = "e.type_id= 'simple' ";

        $joinConditions = implode(
            ' AND ', $joinConditions
        );
8
  • 1
    Thank you, One question what if the user_product_table contain multiple same sku's? Is it also possible to join? Dec 9, 2016 at 9:26
  • 2
    Yes, it is. We can join. Dec 9, 2016 at 9:53
  • how would you add a join condition that was based off a variable generated earlier, for example i have a set type_id i'm looking for each time this module runs and so my variable is called $typeid would it be "e.type_id= ' ".$typeid." ' ";
    – John
    May 30, 2018 at 6:44
  • @John can you ask a new question and link to my answer for a reference? I will take a look. May 30, 2018 at 6:49
  • i think i've found something i'll report back to you if it works
    – John
    May 30, 2018 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.