MySQL Query to Get All Coupon

MySQL query to list all the published WooCommerce Coupon.

One of my answer in StackOverflow on Question How WooCommerce Coupons are stored in Database? has got many hits, So I thought to put a whole query in this tutorial which might help other developers.

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;

Let me know in the comment if this helps you or not if it helps then do hit the Facebook like button and share with other WooCommerce developers.

Default image

Raunak Gupta

I'm an expert Laravel, WooCommerce, WordPress theme and plugin developer, over the time I have developed several sites and applications. I like to help the upcoming developer, So I share my experience through this blog.

Articles: 19

6 Comments

  1. Hi, this is great, thank you!
    Is it possible to somehow also add detail of the customer that used the coupon? Like their name or email address?
    all my coupons has a usage limit of 1.

    Thank you
    Schalk

  2. Thank you for this, how do I update the minimum amount field?

    I got ‘Unknown column ‘minimum_amount’ in ‘field list’ All changes were reverted (DDL statements can’t be reverted)’ when I edited using TablePlus directly from the output record.

    Thank you !

Leave a Reply to EmmanuelCancel Reply