Home > Mobile >  How I can simplify this query and preselect stocks rather then manually define
How I can simplify this query and preselect stocks rather then manually define

Time:02-20

SELECT uuid,name,
       (SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='3ca5a0f3-e522-11eb-84ff-2cf05dcbfb6f') AS '3ca5a0f3-e522-11eb-84ff-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='4641bb5c-e615-11eb-84ff-2cf05dcbfb6f') AS '4641bb5c-e615-11eb-84ff-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='7be9468e-a0da-11eb-84ff-2cf05dcbfb6f') AS '7be9468e-a0da-11eb-84ff-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='822e8e40-e617-11eb-84ff-2cf05dcbfb6f') AS '822e8e40-e617-11eb-84ff-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='90070678-e617-11eb-84ff-2cf05dcbfb6f') AS '90070678-e617-11eb-84ff-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='997808b0-e617-11eb-84ff-2cf05dcbfb6f') AS '997808b0-e617-11eb-84ff-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='a45d439d-4e74-11ec-8502-2cf05dcbfb6f') AS 'a45d439d-4e74-11ec-8502-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='a4c4caba-e51a-11eb-84ff-2cf05dcbfb6f') AS 'a4c4caba-e51a-11eb-84ff-2cf05dcbfb6f',
(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid='c678beaf-1c65-11ec-84ff-2cf05dcbfb6f') AS 'c678beaf-1c65-11ec-84ff-2cf05dcbfb6f',
(SELECT sum(quantity) FROM products_stocks WHERE product_uuid = uuid GROUP by product_uuid) AS 'total'
FROM ( SELECT uuid,name FROM products where hidden=0) AS u;

I need to first select all stocks whene hidden=0 and then select quantity for each stock in pivot table, column names must be stocks.uuid

Now I manually defined all needed stocks but it's bad way )

sql fiddle with create and data: http://sqlfiddle.com/#!9/c4a5d03/1

CodePudding user response:

Try a query like this

SELECT DISTINCT 
GROUP_CONCAT("SUM(IF(ps.`stock_uuid` = '"
            , ps.`stock_uuid`
            , "'"
            , " ,ps.`quantity`,0 )) AS '",ps.`stock_uuid`,"'") INTO @dyncols
FROM ( SELECT uuid,NAME FROM products WHERE hidden=0) AS u
INNER JOIN `products_stocks` AS ps ON u.`uuid` = ps.`product_uuid`;



SET @sql = CONCAT("SELECT p.uuid,p.name,",@dyncols,", SUM(ps.`quantity`) AS total FROM ( SELECT uuid,NAME FROM products WHERE hidden=0) AS p
LEFT JOIN `products_stocks` AS ps ON p.`uuid` = ps.`product_uuid`
GROUP BY p.`uuid`");

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

sample

MariaDB [test]> SELECT DISTINCT 
    -> GROUP_CONCAT("SUM(IF(ps.`stock_uuid` = '"
    -> , ps.`stock_uuid`
    -> , "'"
    -> , " ,ps.`quantity`,0 )) AS '",ps.`stock_uuid`,"'") INTO @dyncols
    -> FROM ( SELECT uuid,NAME FROM products WHERE hidden=0) AS u
    -> INNER JOIN `products_stocks` AS ps ON u.`uuid` = ps.`product_uuid`;
Query OK, 1 row affected (0.005 sec)

MariaDB [test]> 
MariaDB [test]> 
MariaDB [test]> 
MariaDB [test]> SET @sql = CONCAT("SELECT p.uuid,p.name,",@dyncols,", SUM(ps.`quantity`) AS total FROM ( SELECT uuid,NAME FROM products WHERE hidden=0) AS p
    "> LEFT JOIN `products_stocks` AS ps ON p.`uuid` = ps.`product_uuid`
    "> GROUP BY p.`uuid`");
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> 
MariaDB [test]> PREPARE stmt FROM @sql;
Query OK, 0 rows affected (0.001 sec)
Statement prepared

MariaDB [test]> EXECUTE stmt;
 -------------------------------------- ------------------------------------------------------------------------------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- ------- 
| uuid                                 | NAME                                                                                                        | 1351467d-be24-11eb-84ff-2cf05dcbfb6f | 3ca5a0f3-e522-11eb-84ff-2cf05dcbfb6f | 4641bb5c-e615-11eb-84ff-2cf05dcbfb6f | 7be9468e-a0da-11eb-84ff-2cf05dcbfb6f | 822e8e40-e617-11eb-84ff-2cf05dcbfb6f | 90070678-e617-11eb-84ff-2cf05dcbfb6f | 997808b0-e617-11eb-84ff-2cf05dcbfb6f | a45d439d-4e74-11ec-8502-2cf05dcbfb6f | a4c4caba-e51a-11eb-84ff-2cf05dcbfb6f | c678beaf-1c65-11ec-84ff-2cf05dcbfb6f | 1351467d-be24-11eb-84ff-2cf05dcbfb6f | 3ca5a0f3-e522-11eb-84ff-2cf05dcbfb6f | 4641bb5c-e615-11eb-84ff-2cf05dcbfb6f | 7be9468e-a0da-11eb-84ff-2cf05dcbfb6f | 822e8e40-e617-11eb-84ff-2cf05dcbfb6f | 90070678-e617-11eb-84ff-2cf05dcbfb6f | 997808b0-e617-11eb-84ff-2cf05dcbfb6f | a45d439d-4e74-11ec-8502-2cf05dcbfb6f | a4c4caba-e51a-11eb-84ff-2cf05dcbfb6f | c678beaf-1c65-11ec-84ff-2cf05dcbfb6f | total |
 -------------------------------------- ------------------------------------------------------------------------------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- ------- 
| 1365caa1-e068-11eb-84ff-2cf05dcbfb6f | Сахар кусковой 1 кг Чайкофский                                                                              |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| 175a2e7a-e084-11eb-84ff-2cf05dcbfb6f | Макароны из твердых сортов пшеницы перья 400 г ALTALIA                                                      |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| 1f3c3f75-c9b4-11eb-84ff-2cf05dcbfb6f | Бытовая зажигалка пьезо                                                                                     |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| 1f94a583-c9b3-11eb-84ff-2cf05dcbfb6f | Батарейки пальчиковые АА 4 шт Luxlite Shock                                                                 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| 22b9f167-e071-11eb-84ff-2cf05dcbfb6f | Сахар тростниковый демерара светлый 500 г Брауни                                                            |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| 25321456-ca9d-11eb-84ff-2cf05dcbfb6f | Влажные салфетки антибактериальные 15 шт Belux                                                              |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| 2cce5997-e070-11eb-84ff-2cf05dcbfb6f | Сахар-песок белый 900 г Чайкофский                                                                          |                                  288 |                                   34 |                                   38 |                                   32 |                                   37 |                                   36 |                                   30 |                                   10 |                                   60 |                                  195 |                                  288 |                                   34 |                                   38 |                                   32 |                                   37 |                                   36 |                                   30 |                                   10 |                                   60 |                                  195 |   760 |
| 42487cfb-c9b2-11eb-84ff-2cf05dcbfb6f | Газ для зажигалки 20 мл                                                                                     |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| 56d2d747-ce63-11eb-84ff-2cf05dcbfb6f | Бумажные полотенца 1 рулон 2-слойные белые Belux                                                            |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| 5c2abd5f-dd55-11eb-84ff-2cf05dcbfb6f | Губка для посуды 5 шт Belux                                                                                 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| 61ae32ba-ca9d-11eb-84ff-2cf05dcbfb6f | Влажные салфетки детские 72 шт Belux                                                                        |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| 628aa141-ca95-11eb-84ff-2cf05dcbfb6f | Чай зеленый 25 пакетиков Мэтр                                                                               |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| 8adad8b3-e088-11eb-84ff-2cf05dcbfb6f | Макароны из твердых сортов пшеницы рожки 400 г ALTALIA                                                      |                                  288 |                                   34 |                                   38 |                                   32 |                                   37 |                                   36 |                                   30 |                                   10 |                                   60 |                                  195 |                                  288 |                                   34 |                                   38 |                                   32 |                                   37 |                                   36 |                                   30 |                                   10 |                                   60 |                                  195 |   760 |
| 8b8d08d1-e06b-11eb-84ff-2cf05dcbfb6f | Сахар белый кусковой фигурный Mon Cafe 500 г                                                                |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| a3be8062-ce71-11eb-84ff-2cf05dcbfb6f | Туалетная бумага 2-слойная 4 рулона Belux                                                                   |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| ab88d303-e071-11eb-84ff-2cf05dcbfb6f | Сахар-песок тростниковый демерара светлый  900 г                                                            |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| aee12091-c9b1-11eb-84ff-2cf05dcbfb6f | Презервативы classic Domino                                                                                 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| b14df857-e068-11eb-84ff-2cf05dcbfb6f | Сахар кусковой 500 г Чайкофский                                                                             |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| b48438bd-ca95-11eb-84ff-2cf05dcbfb6f | Чай черный 25 пакетиков Мэтр                                                                                |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| b7dba1e7-c9ae-11eb-84ff-2cf05dcbfb6f | Рис Басмати Премиум 1 кг TaMashAe MIADI                                                                     |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| d2ddad20-c9b2-11eb-84ff-2cf05dcbfb6f | Батарейки мизинчиковые AAA 4 шт Luxlite Shock                                                               |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| e584607e-ca9c-11eb-84ff-2cf05dcbfb6f | Носовые платочки белые без аромата Belux                                                                    |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| f730557a-dd53-11eb-84ff-2cf05dcbfb6f | Туалетная бумага 3-слойная 4 рулона Batist                                                                  |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
| ff3d8825-dd56-11eb-84ff-2cf05dcbfb6f | Салфетки хозяйственные универсальные 110шт Семья и Комфорт                                                  |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |                                    0 |  NULL |
 -------------------------------------- ------------------------------------------------------------------------------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- -------------------------------------- ------- 
24 rows in set (0.000 sec)

MariaDB [test]> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> 
MariaDB [test]> 

CodePudding user response:

realized it as advised in comments:

SELECT GROUP_CONCAT(DISTINCT CONCAT('(SELECT quantity FROM products_stocks WHERE product_uuid = uuid and stock_uuid=''',uuid,''') AS ''',uuid,'''')) INTO @sql FROM stocks where hidden=0;
        SET @sql = CONCAT('SELECT id as source_id, name,name as _name,slug,thumbnail,price,',@sql,',(SELECT sum(quantity) FROM products_stocks WHERE product_uuid = uuid GROUP by product_uuid) AS total FROM ( SELECT id,uuid,name,slug,thumbnail,price FROM products where hidden=0) AS u having total>0');
        PREPARE stmt1 FROM @sql;
    
    EXECUTE stmt1;
  • Related