0

First of all, sorry for this title, I tried my best to describe it.

I'm trying to override the WordPress' function wc_get_product_ids_on_sale() because one of my plugin is creating _sale_price even if it's the same than price.

I'd to know, how can I get only post_id which has :

  • _sale_price different from _price
  • _sale_price not empty

I only know how to get empty...

SELECT post_id FROM wp_postmeta 
WHERE meta_key = '_sale_price'
AND meta_value <> ''

Do you have any ideas ?

enter image description here

Pierre
  • 490
  • 1
  • 7
  • 26

1 Answers1

1

You need to use pivoting to extract and then compare the meta values:

SELECT post_id
FROM wp_postmeta
GROUP BY post_id
HAVING
    MAX(CASE WHEN meta_key = '_sale_price' THEN meta_value END) = '' AND
    MAX(CASE WHEN meta_key = '_price' THEN meta_value END) <> '';

This answer assumes that the meta_value column is text, and not a number. If the column be numeric, then you will have to clarify what empty means here.

If you are new to the concept of pivot queries, then I refer you to this SO question for more information.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks for your reply. I don't understand how those having compare _sale_price to _price. Can you explain me ? – Pierre Jan 16 '19 at 14:39
  • @Destunk If you are asking for a full explanation of this, then it really might be too broad for a single question here. Check the link above to see how to write a pivot query in MySQL. – Tim Biegeleisen Jan 16 '19 at 14:59
  • Thanks for the link, the problem was that I didn't know how to find this information on the web – Pierre Jan 17 '19 at 08:10