I have this working SQL query :

SELECT main_table.* FROM prd_brand AS main_table INNER JOIN catalog_product_entity_int ON main_table.brand_id=catalog_product_entity_int.value group by brand_id order by name asc

And I'm trying to get it work in Magento, so I did this :

        $brandCollection = $brand->getCollection();
        $brandCollection->join('catalog_product_entity_int', 'catalog_product_entity_int.value = main_table.brand_id', '*');
        echo $brandCollection->getSelect();

It generates this SQL query:

SELECT `main_table`.*, `catalog_product_entity_int`.* FROM `prd_brand` AS `main_table` INNER JOIN `catalog_product_entity_int` ON catalog_product_entity_int.value = main_table.brand_id

The problem is that I don't like to select the catalog_product_entity_int but only the main_table (prd_brand) table.

How can I do it ?


can you please try like this it will work

$brandCollection = $brand->getCollection();
        'cpei' => 'catalog_product_entity_int'),
        'cpei.value = main_table.brand_id',

Group by use :

->group ( 'main_table.brand_id' )

Order by use :

->order(array('line_items_per_product DESC','product_id'));

  • please check and let me know :) Jun 29 '17 at 18:19
  • and it works ! Thank you so much ! what if I want to add 'group by' and 'order by' criterias ? Jun 29 '17 at 22:00
  • Sure i will provide the query Jun 30 '17 at 5:55
  • I updated the code in ans. if any doubts please ask. Thanks Jun 30 '17 at 7:29

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.