Woocommerce Question posted in
The official Woocommerce documentation can be found here.

Woocommerce – How can I do complex calculations in MYSQL?

How can I find all rows in a table of data, which are relative to the result of some calculation between data from rows in another table?

(As an example, for an eshop, I want to find all products (rows) in a table of products, which are having a price (column in another table) that is below the sum of the relative shipping price, plus the regular price of this product (that data are in another table).

How can I achieve this?

To make it more clear, lets try it with a woocommerce MYSQL query:

SELECT * FROM eu_posts p WHERE p.ID IN (SELECT post_id from eu_postmeta m where m.meta_key="_regular_price" and m.meta_value < (SELECT m.meta_value WHERE m.meta_key='shipping_cost' and m.post_id = p.ID) + (Select m.meta_value WHERE m.meta_key='bb_price' and m.post_id = p.ID));

but the above returns an empty set, though there are rows of products with price lower than the expected sum of the two other values.

Is it possible to do such calculations inside MYSQL?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    It seems I found an answer, the following query works:

    SELECT * from wp_posts WHERE ID IN 
(SELECT post_id FROM wp_postmeta m WHERE m.meta_key="_regular_price" 
AND m.meta_value < 
(SELECT meta_value FROM wp_postmeta d 
WHERE d.meta_key='shipping_cost' AND d.post_id = m.post_id ) + 
(SELECT meta_value FROM wp_postmeta e 
WHERE e.meta_key='_price' AND e.post_id = m.post_id));

    The key seems to be, as Barmar seems to suggest, to name values differently.


  2. 0

    You should join with wp_postmeta multiple times, not use correlated subqueries.

    SELECT p.*, m1.meta_value AS price, m2.meta_value AS shipping_cost, m3.meta_value AS regular_price
FROM wp_posts AS p
JOIN wp_postmeta AS m1 ON p.id = m1.post_id
JOIN wp_postmeta AS m2 ON p.id = m2.post_id
JOIN wp_postmeta AS m3 ON p.id = m3.post_id
WHERE m1.meta_key = '_price' AND m2.meta_key = 'shipping_cost' AND m3.meta_key = '_regular_price'
AND m1.meta_value < m2.meta_value + m3.meta_value
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search