1

I would like to create a query to populate a table dynamically by querying the 3 databases that contain relevant information about their entry.

wp_gf_entry, wp_gf_entry_meta, and (ideally) with wp_posts

The query is working with wp_gf_entry and the wp_gf_entry_meta tables.

SELECT 
  t2.id, 

  MAX(CASE WHEN t1.`meta_key` = '1.3' THEN t1.meta_value END) firstname,
  MAX(CASE WHEN t1.`meta_key` = '1.6' THEN t1.meta_value END) lastname,
  MAX(CASE WHEN t1.`meta_key` = '5.4' THEN t1.meta_value END) state,
  MAX(CASE WHEN t1.`meta_key` LIKE '%45.%' THEN t1.meta_value END) division

FROM
  `wp_gf_entry_meta` t1 
  LEFT JOIN `wp_gf_entry` t2
    ON (
      t1.`entry_id` = t2.`id` 
    )
WHERE (
    t2.`form_id` = 71
    AND t2.`is_read` = 1 AND t2.`status` = 'active' 
        AND t1.`meta_key` IN ('1.3', '1.6', '5.4', '45.1', '45.2', '45.3', '45.4', '45.5', '45.6')
  )
  GROUP BY t2.`id`
ORDER BY 
    t1.`id` ASC,
    t1.`entry_id` ASC

This works well, IF the entry has been 'read' and the entry hasn't been 'trashed'.

+-----+-----------+----------+-------+------------------------------+
| id  | firstname | lastname | state |           division           |
+-----+-----------+----------+-------+------------------------------+
| 166 | Steve     | Jobs     | QLD   | (null)                       |
| 253 | Bill      | Clinton  | NSW   | (null)                       |
| 427 | Maria     | McOldguy | VIC   | Grand Master's (65 and over) |
| 447 | Some      | Bloke    | NSW   | Master's (60-64)             |
+-----+-----------+----------+-------+------------------------------+

But this query still requires a human to interact with the website multiple times a day to process entries (marking them read) - because entries are still written to the database regardless of whether or not the entry has been successfully paid for.

This is where I would like to query the wp_posts table using the woocommerce_order_number key and value pair stored in the wp_gf_entry_meta row for each entry.

So modified the query to this to pull the woocommerce_order_number into the array and what I thought would be something that could be queried:

SELECT 
  t2.id, 

  MAX(CASE WHEN t1.`meta_key` = 'woocommerce_order_number' THEN t1.meta_value END) wcID,
  MAX(CASE WHEN t1.`meta_key` = '1.3' THEN t1.meta_value END) firstname,
  MAX(CASE WHEN t1.`meta_key` = '1.6' THEN t1.meta_value END) lastname,
  MAX(CASE WHEN t1.`meta_key` = '5.4' THEN t1.meta_value END) state,
  MAX(CASE WHEN t1.`meta_key` LIKE '%45.%' THEN t1.meta_value END) division

FROM
  `wp_gf_entry_meta` t1 
  LEFT JOIN `wp_gf_entry` t2
    ON (
      t1.`entry_id` = t2.`id` 
    )
WHERE (
t2.`form_id` = 71
    AND t2.`is_read` = 1 AND t2.`status` = 'active'
        AND t1.`meta_key` IN ('1.3', '1.6', '5.4', '45.1', '45.2', '45.3', '45.4', '45.5', '45.6', 'woocommerce_order_number')

  )
  GROUP BY t2.`id`
ORDER BY 
    t1.`id` ASC,
    t1.`entry_id` ASC

The above query gives me a column with the woocommerce order id:

+-----+------+-----------+----------+-------+------------------------------+
| id  | wcID | firstname | lastname | state |           division           |
+-----+------+-----------+----------+-------+------------------------------+
| 166 |    1 | Steve     | Jobs     | QLD   | (null)                       |
| 253 |    2 | Bill      | Clinton  | NSW   | (null)                       |
| 427 |    3 | Maria     | McOldguy | VIC   | Grand Master's (65 and over) |
| 447 |    4 | Some      | Bloke    | NSW   | Master's (60-64)             |
+-----+------+-----------+----------+-------+------------------------------+

However, I'd like to take the human factor out by adding the following to the WHERE statement

SELECT 
  t2.id, 

  MAX(CASE WHEN t1.`meta_key` = 'woocommerce_order_number' THEN t1.meta_value END) wcID,
  MAX(CASE WHEN t1.`meta_key` = '1.3' THEN t1.meta_value END) firstname,
  MAX(CASE WHEN t1.`meta_key` = '1.6' THEN t1.meta_value END) lastname,
  MAX(CASE WHEN t1.`meta_key` = '5.4' THEN t1.meta_value END) state,
  MAX(CASE WHEN t1.`meta_key` LIKE '%45.%' THEN t1.meta_value END) division

FROM
  `wp_gf_entry_meta` t1 
  LEFT JOIN `wp_gf_entry` t2
    ON (
      t1.`entry_id` = t2.`id` 
    )
WHERE (
t2.`form_id` = 71
        AND t1.`meta_key` IN ('1.3', '1.6', '5.4', '45.1', '45.2', '45.3', '45.4', '45.5', '45.6', 'woocommerce_order_number')

        AND t1.`meta_value` IN (SELECT t3.ID FROM wp_posts t3 
                WHERE t3.ID = [wcID]
                    AND t3.`post_status` IN ('wc-processing', 'wc-completed', 'wc-free-of-charge') 
                    AND t3.`post_type` = 'shop_order' )
  )
  GROUP BY t2.`id`
ORDER BY 
    t1.`id` ASC,
    t1.`entry_id` ASC

Results in this error message "Warning: #1292 Truncated incorrect DOUBLE value: 'wcID'".

And I'm not quite sure how to finesse this final AND statement.

SQL Fiddle location

Is there a way to query the wp_posts table and ID column using the woocommerce_order_number alias wcID?

0

Check

SELECT 
  t2.id, 
  MAX(CASE WHEN t1.`meta_key` = 'woocommerce_order_number' THEN t1.meta_value END) wcID,
  MAX(CASE WHEN t1.`meta_key` = '1.3' THEN t1.meta_value END) firstname,
  MAX(CASE WHEN t1.`meta_key` = '1.6' THEN t1.meta_value END) lastname,
  MAX(CASE WHEN t1.`meta_key` = '5.4' THEN t1.meta_value END) state,
  MAX(CASE WHEN t1.`meta_key` LIKE '%45.%' THEN t1.meta_value END) division
FROM  wp_gf_entry_meta t1 
LEFT JOIN wp_gf_entry t2 
    ON t1.entry_id = t2.id
LEFT JOIN ( SELECT t3.ID 
            FROM wp_posts t3 
            WHERE t3.`post_status` IN ('wc-processing', 'wc-completed', 'wc-free-of-charge') 
              AND t3.`post_type` = 'shop_order' ) t4 
    ON t1.`meta_key` = 'woocommerce_order_number' AND t1.meta_value = t4.ID
WHERE t2.`form_id` = 71
  AND t2.`is_read` = 1 
  AND t2.`status` = 'active'
  AND t1.`meta_key` IN ('1.3', '1.6', '5.4', '45.1', '45.2', '45.3', '45.4', '45.5', '45.6', 'woocommerce_order_number')
GROUP BY t2.id

fiddle

  • That is exactly what I had in mind! Thank you very much. – Zoe Sep 18 '19 at 5:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.