2

I have this working SQL query :

select prd_brand.* from prd_brand
inner join
(
    select distinct value from catalog_product_entity_int
    where 
    rowid in (select rowid from catalog_product_entity_int where attribute_id = 97 and value = 1)
)t
on prd_brand.brand_id = t.value

How can I nest this select query in the join in Magento 2 ?

I did it like this :

$subquery = new \Zend_Db_Expr('SELECT DISTINCT value from catalog_product_entity_int where  rowid in (select rowid from catalog_product_entity_int where attribute_id = 97 and value = 1)');
    $brandCollection->join(array(
                        'cpei' => 'catalog_product_entity_int'),
                        'cpei.value = main_table.brand_id',
                    array('subquery' => $subquery));

But it gives this query :

    SELECT `main_table`.*, 
SELECT DISTINCT value from catalog_product_entity_int 
where rowid in 
(select rowid from catalog_product_entity_int where attribute_id = 97 and value = 1) AS `subquery` 
FROM `prd_brand` AS `main_table` 
INNER JOIN `catalog_product_entity_int` AS `cpei` ON cpei.value = main_table.brand_id
3

Can you try like below. It will surely work. i have checked in my local :).

$subquery = new \Zend_Db_Expr('(SELECT DISTINCT value from catalog_product_entity_int where  row_id in (select row_id from catalog_product_entity_int where attribute_id = 97 and value = 1))');

$brandCollection->getSelect()->join( array( 't' => $subquery ), 'main_table.brand_id = t.value', array());
  • any doubts please ask :) – Rama Chandran M Jun 30 '17 at 19:22
  • it gives exactly the same query. – androniennn Jun 30 '17 at 21:58
  • Any idea please? I'm really stuck... Thank you. – androniennn Jul 1 '17 at 11:15
  • 1
    Sure I will provide quert – Rama Chandran M Jul 1 '17 at 11:28
  • 1
    I updated the query please check it – Rama Chandran M Jul 1 '17 at 17:38

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.