Support needed for a query

I need help to finish a SQL Query for a Woocommerce product list. I need this for a wpdatatable sql input.

I searched for a example code and have adapted a found code:

SELECT
  p.ID,
  p.post_title,
  p.post_content,
  p.post_excerpt,
  t.name AS product_category,
  t.term_id AS product_id,
  tt.term_taxonomy_id AS tt_term_taxonomia,
  tr.term_taxonomy_id AS tr_term_taxonomia,
  MAX(CASE WHEN pm1.meta_key = '_price' then pm1.meta_value ELSE NULL END) as price
FROM wp_posts p 
LEFT JOIN wp_postmeta pm1 ON pm1.post_id = p.ID
LEFT JOIN wp_term_relationships AS tr ON tr.object_id = p.ID
JOIN wp_term_taxonomy AS tt ON tt.taxonomy = 'product_cat' AND tt.term_taxonomy_id = tr.term_taxonomy_id 
JOIN wp_terms AS t ON t.term_id = tt.term_id
WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish'
GROUP BY p.ID,p.post_title

It works but without product rows for variable products. It shows only the main variation of a product.

Example: product steak: I have 6 different variations of steaks as own products with different prices and also different weight characteristics. With my SQL query I can only see the main product “steak” without variations and variation prices.

Table wp_Posts

id   | post_title                     | post_type         | post_parent | post_name | regular_price |
-----+--------------------------------+-------------------+-------------+-----------+---------------|
4113 | Steaks                         | product           | 0           |
4119 | Steaks-Lungenbraten Steak 125g | product_variation | 4113        | steaks_4  |6              |
4120 | Steaks-Hüftsteak 200g          | product_variation | 4113        | steaks_5  |4,4            |
4121 | Steaks-Flankensteak 600g       | product_variation | 4113        | steaks_6  |8,4            |

Table wp_postmeta-

Post_ID |meta_key                | meta_value         |
--------+----------------+----------------------------+---------
4113    |                        | 
4119    | attribute_pa_steaks    | lungenbraten-steak |
4119    | _price.                | 6                  |
4120    | attribute_pa_steaks    | hueftsteak         |
4120    | _price.                | 4,4                |
4121    | attribute_pa_steaks    | flanksteak         |
4121    | _price.                | 8,4                |

wp_term_relationship

object_id.  | term_taxonomy_id. | term_order |
------------+-------------------+------------+
4113        |    6              |    0       |
4113        |   296             |    0       |
4113        |   297             |    0       |
4113        |   298             |    0       |

wp_term_taxonomy

term_taxonomy_id. | term_id  | taxonomy    | description. | parent |count |
------------------+----------+-------------+--------------+--------+------+
296               |   296    |  pa_steaks  |              |  0     | 1.   |
297               |   297    |  pa_steaks  |              |  0     | 1.   |
298               |   298    |  pa_steaks  |              |  0     | 1.   |

wp_terms

term_id     |  name              | slug               | term_group | 
------------+--------------------+--------------------+------------+
296         | Flanksteak         | flanksteak         | 0          |
297         | Hüftsteakk         | hueftsteak         | 0          |
298         | Lungenbraten Steak | Lungenbraten-steak | 0          |

Answer

Your query is invalid, as the select list doesn’t match the GROUP BY. Even if you DBMS is flawed and doesn’t raise an error, I don’t recommend using such a query. It’s results may be arbitrary (as is the case in MySQL for instance). Write a query that leaves no doubt as to what data you really want to select.

As you want just one value from the key/value table wp_postmeta, you don’t need aggregation:

SELECT
  p.id,
  p.post_title,
  p.post_content,
  p.post_excerpt,
  t.name AS product_category,
  t.term_id AS product_id,
  tt.term_taxonomy_id AS tt_term_taxonomia,
  tr.term_taxonomy_id AS tr_term_taxonomia,
  pm1.meta_value AS price
FROM wp_posts p 
LEFT JOIN wp_postmeta pm1 ON pm1.post_id = p.ID AND pm1.meta_key = '_price'
JOIN wp_term_relationships AS tr ON tr.object_id = p.ID
JOIN wp_term_taxonomy AS tt ON tt.taxonomy = 'product_cat'
                           AND tt.term_taxonomy_id = tr.term_taxonomy_id 
JOIN wp_terms AS t ON t.term_id = tt.term_id
WHERE p.post_type in('product', 'product_variation') AND p.post_status = 'publish'
ORDER BY
  CASE WHEN p.post_parent = 0 THEN id else post_parent END,
  CASE WHEN p.post_parent = 0 THEN 1 else 2 END;