CODEWITHSUNDEEP
mysqlsqldatabase
PHCoder
PHCoder

Reputation: 1

Delete coupons stored in database WooCommerce

I found this post: How WooCommerce Coupons are stored in Database?

It is working correct and loads everything of the coupons in the database. Instead of a SELECT query I want to DELETE all of this.

How do I need to modify the code?

SELECT p.`ID`, 
       p.`post_title`   AS coupon_code, 
       p.`post_excerpt` AS coupon_description, 
       Max(CASE WHEN pm.meta_key = 'discount_type'      AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS discount_type,          -- Discount type 
       Max(CASE WHEN pm.meta_key = 'coupon_amount'      AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS coupon_amount,          -- Coupon amount 
       Max(CASE WHEN pm.meta_key = 'free_shipping'      AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS free_shipping,          -- Allow free shipping 
       Max(CASE WHEN pm.meta_key = 'expiry_date'        AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS expiry_date,                -- Coupon expiry date 
       Max(CASE WHEN pm.meta_key = 'minimum_amount'     AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS minimum_amount,         -- Minimum spend 
       Max(CASE WHEN pm.meta_key = 'maximum_amount'     AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS maximum_amount,         -- Maximum spend 
       Max(CASE WHEN pm.meta_key = 'individual_use'     AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS individual_use,         -- Individual use only 
       Max(CASE WHEN pm.meta_key = 'exclude_sale_items' AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_sale_items,         -- Exclude sale items 
       Max(CASE WHEN pm.meta_key = 'product_ids'    AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_ids,                -- Products 
       Max(CASE WHEN pm.meta_key = 'exclude_product_ids'AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_product_ids,        -- Exclude products 
       Max(CASE WHEN pm.meta_key = 'product_categories' AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS product_categories,             -- Product categories 
       Max(CASE WHEN pm.meta_key = 'exclude_product_categories' AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS exclude_product_categories,-- Exclude Product categories 
       Max(CASE WHEN pm.meta_key = 'customer_email'     AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS customer_email,         -- Email restrictions 
       Max(CASE WHEN pm.meta_key = 'usage_limit'    AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS usage_limit,                -- Usage limit per coupon 
       Max(CASE WHEN pm.meta_key = 'usage_limit_per_user'   AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS usage_limit_per_user,   -- Usage limit per user 
       Max(CASE WHEN pm.meta_key = 'usage_count'    AND  p.`ID` = pm.`post_id` THEN pm.`meta_value` END) AS total_usaged                   -- Usage count 
FROM   `wp_posts` AS p 
       INNER JOIN `wp_postmeta` AS pm ON  p.`ID` = pm.`post_id` 
WHERE  p.`post_type` = 'shop_coupon' 
       AND p.`post_status` = 'publish' 
GROUP  BY p.`ID` 
ORDER  BY p.`ID` ASC;

FIXED to delete coupons from database with it's postmeta by running 2 queries:

DELETE FROM `wp_posts` WHERE `post_type` = 'shop_coupon';

DELETE FROM
  `wp_postmeta`
WHERE
  (
    meta_key = 'discount_type'
    OR meta_key = 'coupon_amount'
    OR meta_key = 'individual_use'
    OR meta_key = 'product_ids'
    OR meta_key = 'exclude_product_ids'
    OR meta_key = 'usage_limit'
    OR meta_key = 'limit_usage_to_x_items'
    OR meta_key = 'expiry_date'
    OR meta_key = 'free_shipping'
    OR meta_key = 'exclude_sale_items'
    OR meta_key = 'product_categories'
    OR meta_key = 'exclude_product_categories'
    OR meta_key = 'minimum_amount'
    OR meta_key = 'maximum_amount'
    OR meta_key = 'customer_email'
    OR meta_key = 'usage_limit_per_user'
  );

Upvotes: 0

Views: 1949

Answers (3)

Thomas Luzat
Thomas Luzat

Reputation: 740

There are a number of problems with most solutions:

  • All direct database manipulation is dangerous, especially without backups.
  • Just deleting records by meta_key is dangerous: some posts other than coupons may for example own 'product_ids' meta data.
  • Deleting specific meta keys may leave additional meta data available in different versions or by plugins behind.
  • Queries need to be adjusted if the configured table prefix is not wp_.

Still, in many cases running such a query does make sense. A query which deletes the coupons and exactly determines all their associated meta data does use a join based on wp_posts.ID:

DELETE p, m
    FROM wp_posts p
    LEFT JOIN wp_postmeta m ON p.ID = m.post_id
    WHERE p.post_type = 'shop_coupon'

A somewhat less intrusive and dangerous way to do it, is to move everything into trash:

UPDATE wp_posts SET post_status = 'trash' WHERE post_type = 'shop_coupon'

... and empty the trash from within the WordPress backend. This gives plugins a chance to invoke filters and hooks and is a great solution for many post_types. I would suggest to use that for more complex types should as orders with associated items, comments and more. Both queries can be limited to only affect published posts:

DELETE p, m
    FROM wp_posts p
    LEFT JOIN wp_postmeta m ON p.ID = m.post_id
    WHERE p.post_type = 'shop_coupon'
    AND p.post_status = 'publish'

UPDATE wp_posts SET post_status = 'trash'
    WHERE post_type = 'shop_coupon'
    AND post_status = 'publish'

Upvotes: 2

ravioli
ravioli

Reputation: 3833

Assuming you want to delete from wp_posts, you just need to get the list of IDs to delete:

DELETE FROM `wp_posts`
WHERE `ID` IN (
  SELECT p.`ID`
  FROM   `wp_posts` AS p 
         INNER JOIN `wp_postmeta` AS pm ON  p.`ID` = pm.`post_id` 
  WHERE  p.`post_type` = 'shop_coupon' 
         AND p.`post_status` = 'publish' 
)
;

You're not referencing any of the columns in your SELECT list, so you don't need to include them in your sub-query. You can also remove the GROUP BY since you just want a list of the IDs.

You can probably re-write the DELETE a little cleaner using EXISTS or a join instead of IN, but either way will give you the same result.

Upvotes: 0

Snooley
Snooley

Reputation: 71

SELECT * FROM `wp_posts` WHERE `post_type` = 'shop_coupon' ORDER BY `ID` DESC

If the select statement above returns all the rows you would like to delete, then you just need to replace the select with delete and remove the order by as it isn't needed. Like so:

DELETE FROM `wp_posts` WHERE `post_type` = 'shop_coupon'

Upvotes: 0

Related Questions