6

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.

10

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
        );
  • 1
    Thank you, One question what if the user_product_table contain multiple same sku's? Is it also possible to join? – Prakash Thapa Dec 9 '16 at 9:26
  • 2
    Yes, it is. We can join. – Khoa TruongDinh Dec 9 '16 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 '18 at 6:44
  • @John can you ask a new question and link to my answer for a reference? I will take a look. – Khoa TruongDinh May 30 '18 at 6:49
  • i think i've found something i'll report back to you if it works – John May 30 '18 at 7:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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