mysql - Query in Wordpress of a Custom Post Type with the postmeta

Question

I am trying to make a wordpress shortcode with a query. I have to make a query of how many orders were made of the products in the last day and in the last week.

I don't know how to filter by date (the date is also in text format). Thank you very much.

Until now I did this:

The Table:

post_id	meta_key	meta_value
164	order_date	09-12-2020 18:17
164	order_items_5_order_product	chcolate
164	order_items_6_order_product	menta
165	order_date	05-12-2020 14:35
165	order_items_1_order_product	chcolate
165	order_items_2_order_product	menta
165	order_items_3_order_product	granizo
166	order_date	02-10-2020 11:37
166	order_items_5_order_product	chcolate
166	order_items_6_order_product	menta

question from:https://stackoverflow.com/questions/65865869/query-in-wordpress-of-a-custom-post-type-with-the-postmeta

Answer 1

Basically you must use a pivot table and prepared statements to generate a dynamic table

CREATE TABLE tc_10_postmeta 
    (`post_id` int, `meta_key` varchar(27), `meta_value` varchar(16))
;
    
INSERT INTO tc_10_postmeta 
    (`post_id`, `meta_key`, `meta_value`)
VALUES
    (164, 'order_date', '09-12-2020 18:17'),
    (164, 'order_items_5_order_product', 'chcolate'),
    (164, 'order_items_6_order_product', 'menta'),
    (165, 'order_date', '05-12-2020 14:35'),
    (165, 'order_items_1_order_product', 'chcolate'),
    (165, 'order_items_2_order_product', 'menta'),
    (165, 'order_items_3_order_product', 'granizo'),
    (166, 'order_date', '02-10-2020 11:37'),
    (166, 'order_items_5_order_product', 'chcolate'),
    (166, 'order_items_6_order_product', 'menta')
;

SELECT
    `post_id`,
    MAX(IF(`meta_key` = 'order_date', STR_TO_DATE(`meta_value`, '%m-%d-%Y %H:%i'), NULL ))  as ORDERDate
    , SUM(IF(`meta_key`  LIKE "order_items_%_order_product" AND  `meta_value` = 'chcolate',1,0)) as 'chcolate'
    ,SUM(IF(`meta_key`  LIKE "order_items_%_order_product" AND  `meta_value` = 'menta',1,0)) as 'menta'
    ,SUM(IF(`meta_key`  LIKE "order_items_%_order_product" AND  `meta_value` = 'granizo',1,0)) as 'granizo'
FROM tc_10_postmeta
GROUP BY `post_id`

post_id | ORDERDate           | chcolate | menta | granizo
------: | :------------------ | -------: | ----: | ------:
    164 | 2020-09-12 18:17:00 |        1 |     1 |       0
    165 | 2020-05-12 14:35:00 |        1 |     1 |       1
    166 | 2020-02-10 11:37:00 |        1 |     1 |       0

SET @sql ='';
SELECT 
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(IF(`meta_key`  LIKE "order_items_%_order_product" AND  `meta_value` = "',
     `meta_value`,
      '",1,0)) AS "',
      `meta_value`, '"'      
    )
  ) 
  INTO @sql
FROM tc_10_postmeta
WHERE `meta_key`  LIKE "order_items_%_order_product";
SET @sql = CONCAT('select `post_id`
                  ,MAX(IF(`meta_key` = "order_date", STR_TO_DATE(`meta_value`, "%m-%d-%Y %H:%i"), NULL ))  as ORDERDate
                  ,',@sql,' from tc_10_postmeta
                  group by `post_id`');

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

post_id | ORDERDate           | chcolate | granizo | menta
------: | :------------------ | -------: | ------: | ----:
    164 | 2020-09-12 18:17:00 |        1 |       0 |     1
    165 | 2020-05-12 14:35:00 |        1 |       1 |     1
    166 | 2020-02-10 11:37:00 |        1 |       0 |     1

db<>fiddle here