SQL query

[betisimo76]

Hi,

I have slight knowledge of sql, but not advanced enough to be able to pull off the following query.

I have a store with products with combinations and without them. I would like to be able to get the following for all active products (with or without combination):

Product name
Category
stock units
Final price (tax included)
Weight

Prestashop 1.7.8.7

Somebody could help me?

As a note, I indicate that in the products that have combinations, you should see in the list to which combination that product belongs, to distinguish it from the rest. Is that for this I don't know if I have to put a new column called "combination" or not.

Thank you very much!

[betisimo76]

Please, any help?

Thanks!!

[ps8modules]

Hi.
Compiling such sql is almost impossible. Several nested SELECTs would have to be inserted. You cannot use more than one SELECT in Prestashop's SQL manager, and you cannot use IF functions, etc.

The solution for you is php and the use of Prestashop functions.

Can you please tell me what programming knowledge you have with Prestashop?

Should the output be a CSV file?

Edited November 21, 2022 by 4you.software (see edit history)

[betisimo76]

Hi.

First of all, thanks for your quick response. I was unaware that the Prestashop SQL manager had so many limitations.

I have knowledge of PHP and I understand SQL structures but I'm not a pro. I can defend myself with some difficulty, but nothing more. Indeed, the result could be exported to a CSV.

Thanks for everything.

[Knowband Plugins]

Have you checked the getProducts() in product.php, as in that function you can find the required SQL, KIndly edit that as per your need.

public static function getProducts(
        $id_lang,
        $start,
        $limit,
        $order_by,
        $order_way,
        $id_category = false,
        $only_active = false,
        Context $context = null
    ) {
        if (!$context) {
            $context = Context::getContext();
        }

        $front = true;
        if (!in_array($context->controller->controller_type, ['front', 'modulefront'])) {
            $front = false;
        }

        if (!Validate::isOrderBy($order_by) || !Validate::isOrderWay($order_way)) {
            die(Tools::displayError());
        }
        if ($order_by == 'id_product' || $order_by == 'price' || $order_by == 'date_add' || $order_by == 'date_upd') {
            $order_by_prefix = 'p';
        } elseif ($order_by == 'name') {
            $order_by_prefix = 'pl';
        } elseif ($order_by == 'position') {
            $order_by_prefix = 'c';
        }

        if (strpos($order_by, '.') > 0) {
            $order_by = explode('.', $order_by);
            $order_by_prefix = $order_by[0];
            $order_by = $order_by[1];
        }
        $sql = 'SELECT p.*, product_shop.*, pl.* , m.`name` AS manufacturer_name, s.`name` AS supplier_name
                FROM `' . _DB_PREFIX_ . 'product` p
                ' . Shop::addSqlAssociation('product', 'p') . '
                LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl ON (p.`id_product` = pl.`id_product` ' . Shop::addSqlRestrictionOnLang('pl') . ')
                LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m ON (m.`id_manufacturer` = p.`id_manufacturer`)
                LEFT JOIN `' . _DB_PREFIX_ . 'supplier` s ON (s.`id_supplier` = p.`id_supplier`)' .
                ($id_category ? 'LEFT JOIN `' . _DB_PREFIX_ . 'category_product` c ON (c.`id_product` = p.`id_product`)' : '') . '
                WHERE pl.`id_lang` = ' . (int) $id_lang .
                    ($id_category ? ' AND c.`id_category` = ' . (int) $id_category : '') .
                    ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') .
                    ($only_active ? ' AND product_shop.`active` = 1' : '') . '
                ORDER BY ' . (isset($order_by_prefix) ? pSQL($order_by_prefix) . '.' : '') . '`' . pSQL($order_by) . '` ' . pSQL($order_way) .
                ($limit > 0 ? ' LIMIT ' . (int) $start . ',' . (int) $limit : '');
        $rq = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($sql);
        if ($order_by == 'price') {
            Tools::orderbyPrice($rq, $order_way);
        }

        foreach ($rq as &$row) {
            $row = Product::getTaxesInformations($row);
        }

        return $rq;
    }