(SOLVED) Show only manufacturers with products

[hugoduarte]

Good evening,

In manufacturer.php, how can i change the query to only show me the manufacturers with products associated?

Edited June 15, 2016 by hugoduarte (see edit history)

[rocky]

It sounds like you're using an old version of PrestaShop, since manufacturer.php no longer exists in PrestaShop v1.6.1.5, but I'll paste my solution here anyway in case it helps.

 

I suggest creating override/classes/Manufacturer.php with the following:

<?php

class Manufacturer extends ManufacturerCore {

    public static function getManufacturers($get_nb_products = false, $id_lang = 0, $active = true, $p = false, $n = false, $all_group = false, $group_by = false)
    {
        if (!$id_lang) {
            $id_lang = (int)Configuration::get('PS_LANG_DEFAULT');
        }
        if (!Group::isFeatureActive()) {
            $all_group = true;
        }

        $manufacturers = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
		SELECT m.*, ml.`description`, ml.`short_description`
		FROM `'._DB_PREFIX_.'manufacturer` m
		'.Shop::addSqlAssociation('manufacturer', 'm').'
		INNER JOIN `'._DB_PREFIX_.'manufacturer_lang` ml ON (m.`id_manufacturer` = ml.`id_manufacturer` AND ml.`id_lang` = '.(int)$id_lang.')
                WHERE m.`id_manufacturer` IN (SELECT `id_manufacturer` FROM `'._DB_PREFIX_.'product`)
		'.($active ? 'AND m.`active` = 1' : '')
        .($group_by ? ' GROUP BY m.`id_manufacturer`' : '').'
		ORDER BY m.`name` ASC
		'.($p ? ' LIMIT '.(((int)$p - 1) * (int)$n).','.(int)$n : ''));
        if ($manufacturers === false) {
            return false;
        }

        if ($get_nb_products) {
            $sql_groups = '';
            if (!$all_group) {
                $groups = FrontController::getCurrentCustomerGroups();
                $sql_groups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');
            }

            $results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
					SELECT  p.`id_manufacturer`, COUNT(DISTINCT p.`id_product`) as nb_products
					FROM `'._DB_PREFIX_.'product` p USE INDEX (product_manufacturer)
					'.Shop::addSqlAssociation('product', 'p').'
					LEFT JOIN `'._DB_PREFIX_.'manufacturer` as m ON (m.`id_manufacturer`= p.`id_manufacturer`)
					WHERE p.`id_manufacturer` != 0 AND product_shop.`visibility` NOT IN ("none")
					'.($active ? ' AND product_shop.`active` = 1 ' : '').'
					'.(Group::isFeatureActive() && $all_group ? '' : ' AND EXISTS (
						SELECT 1
						FROM `'._DB_PREFIX_.'category_group` cg
						LEFT JOIN `'._DB_PREFIX_.'category_product` cp ON (cp.`id_category` = cg.`id_category`)
						WHERE p.`id_product` = cp.`id_product` AND cg.`id_group` '.$sql_groups.'
					)').'
					GROUP BY p.`id_manufacturer`'
                );

            $counts = array();
            foreach ($results as $result) {
                $counts[(int)$result['id_manufacturer']] = (int)$result['nb_products'];
            }

            if (count($counts)) {
                foreach ($manufacturers as $key => $manufacturer) {
                    if (array_key_exists((int)$manufacturer['id_manufacturer'], $counts)) {
                        $manufacturers[$key]['nb_products'] = $counts[(int)$manufacturer['id_manufacturer']];
                    } else {
                        $manufacturers[$key]['nb_products'] = 0;
                    }
                }
            }
        }

        $total_manufacturers = count($manufacturers);
        $rewrite_settings = (int)Configuration::get('PS_REWRITING_SETTINGS');
        for ($i = 0; $i < $total_manufacturers; $i++) {
            $manufacturers[$i]['link_rewrite'] = ($rewrite_settings ? Tools::link_rewrite($manufacturers[$i]['name']) : 0);
        }
        return $manufacturers;
    }
    
}

It is line 19 that I added to the original query:

                WHERE m.`id_manufacturer` IN (SELECT `id_manufacturer` FROM `'._DB_PREFIX_.'product`)

This line excludes manufacturers from the query that aren't referenced in the product table.

[hugoduarte]

I'm using version 1.6.1.4.

 

Thanks for the help it works.

[rocky]

I'm happy it's working for you. Please edit your first post and add [sOLVED] to the front of the title.

[torregrossa]

I am using 1.6.1.9 also here works!

 

thanks

Edited April 1, 2017 by torregrossa (see edit history)

[mitzayapa]

Nice thread and solution. How about showing only manufacturers with more than 3 products , is there any way to achive that ?

Thank you.