0

I am working on a site where product brands have been set up as categories. Each product has at least 2 categories, one for brand and others for product categories.

On the page for Brand-X, rather than list the products, I wish to show all the other categories that are associated with Brand-X's products.

What would be the most efficient way to obtain a category list from this structure?

For example:

Product A - Categories [Brand-X, Category-1, Category-2]
Product B - Categories [Brand-X, Category-4]
Product C - Categories [Brand-X, Category-2]
Product D - Categories [Brand-Y, Category-3]

on the Brand-X page, display links to Category-1, Category-2, Category-4
  • I could achieve this with an sql query: SELECT DISTINCT c2.category_id FROM catalog_category_product c1 INNER JOIN catalog_category_product c2 ON c1.product_id = c2.product_id WHERE c1.category_id = <<Brand-X-id>> but I do not think this the best way. – user3313446 Jun 20 at 15:31
0

I have used a join within the collection. Note this was used inside block, but I have included for the sake of the example.

    $collection = $this->_catalogLayer->getCurrentCategory()->getCollection()                            
                        ->addIsActiveFilter()
                        //limit the categories to the current store
                        ->addPathsFilter(1 . '/' . $this->_storeManager->getStore()->getRootCategoryId())                                              ;

    $collection->getSelect()
            ->join(['category_products' => 'catalog_category_product'], 'category_products.category_id = e.entity_id', [])
            ->join(['sub_categories' => 'catalog_category_product'], 'category_products.product_id = sub_categories.product_id', [])
            ->group('e.entity_id')
            ->where('sub_categories.category_id = ?', $this->_catalogLayer->getCurrentCategory()->getId())
            ->where('e.entity_id != ?', $this->_catalogLayer->getCurrentCategory()->getId());

    return $collection;   

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.