Query wp_postmeta table to return post_id rows with LIKE condition on one meta_value and order by another meta_value

Question

I currently have this so I can show product results that say "Product data not found" in the meta_key "product_info"

My current query is this SQL:

SELECT *
FROM `wp_postmeta`
WHERE `meta_key` LIKE 'product_info'
    AND `meta_value` LIKE '%Product data not found%'

Apart from this, I need to be able to show them in order from the oldest to the newest and this data is found in the meta_key "last_update" in the form of numbers as in the example.

How would I do user order by with the meta_key "last_update"

SQL STRUCTURE EXAMPLE

post_id   meta_key         meta_value
19248   product_info     Product data not found
19248   last_update      1959520849
19249   product_info     Product data not found
19249   last_update      1659520849
19250   product_info     OK
19250   last_update      1759520849
19251   product_info     Product data not found
19251   last_update      1859520849

I wish to achieve this as a result

post_id   meta_key         meta_value
19249   product_info     Product data not found
//not visible result 19249   last_update      1659520849
19251   product_info     Product data not found
//not visible result 19251   last_update      1859520849
19248   product_info     Product data not found
//not visible result 19248   last_update      1959520849

As we can see, it was ordered from the lowest number only taking into account the post_id that they have in their product_info "Product data not found"

Not visible result = I put it so you can see that it was ordered by time

Sorry if the question is poorly phrased, I'm new to SQL.

Answer 1

You need to JOIN to wp_postmeta again, looking for the same post_id and a meta_key of last_update. You can then ORDER BY the meta_value from the second table:

SELECT wp1.*
FROM wp_postmeta wp1
LEFT JOIN wp_postmeta wp2 ON wp2.post_id = wp1.post_id AND wp2.meta_key = 'last_update'
WHERE wp1.meta_key LIKE 'product_info' AND wp1.meta_value LIKE '%Product data not found%'
ORDER BY COALESCE(wp2.meta_value, 0)

Output for your sample data:

post_id meta_key        meta_value
19249   product_info    Product data not found
19251   product_info    Product data not found
19248   product_info    Product data not found

Demo on db-fiddle.com

Note that I've used a LEFT JOIN with COALESCE to deal with any posts which don't have a last_update value. The 0 value in COALESCE means those posts will sort first; if you want them to sort last, use COALESCE(wp2.meta_value, 2147483647) instead.

Answer 2

A very common approach accumulate related data in such a table structure is to perform a pivot. A pivot query which known column names makes it much easier to develop and maintain your query because after extruding the desired value from aggregate data, you can enjoy very simple access.

The benefit of using GROUP BY and sifting through aggregate data might seem like a lot of work initially, but when you start piling up multiple rows for a single post_id, then this approach really reviews its rewards.

Schema (MySQL v8.0)

CREATE TABLE wp_postmeta (
  `post_id` INTEGER,
  `meta_key` VARCHAR(50),
  `meta_value` VARCHAR(50)
);

INSERT INTO wp_postmeta
  (`post_id`, `meta_key`, `meta_value`)
VALUES
  ('19248', 'product_info', 'Product data not found anywhere'),
  ('19248', 'last_update', '1959520849'),
  ('19249', 'product_info', 'This Product data not found'),
  ('19249', 'last_update', '1659520849'),
  ('19250', 'product_info', 'OK'),
  ('19250', 'last_update', '1759520849'),
  ('19251', 'product_info', 'Product data not found ...yes, I looked'),
  ('19251', 'last_update', '1859520849');

---

Query View on DB Fiddle

SELECT post_id,
       MAX(CASE meta_key WHEN 'product_info' THEN meta_value END) product_info,
       MAX(CASE meta_key WHEN 'last_update' THEN meta_value END) last_update
FROM wp_postmeta
GROUP BY post_id
HAVING MAX(CASE meta_key WHEN 'product_info' THEN meta_value END) LIKE '%Product data not found%'
ORDER BY last_update;

post_id	product_info	last_update
19249	This Product data not found	1659520849
19251	Product data not found ...yes, I looked	1859520849
19248	Product data not found anywhere	1959520849

---

Some related advice that I've previously given on the topic of pivots:

• Possible dupe target candidate: Select user having qualifying data on multiple rows in the wp_usermeta table
• How to add 2 columns with same id in 1 array in php?
• Display data from mysql columns as rows in PHP
• Need help writing SQL SELECT statement that combines two tables
• How to retrieve RSForm submission data grouped by SubmissionId?
• Return FieldValue Where FieldName Equals Value and ID Equals Number
• MySql SELECT multiple according to form input and publish
• Roles and permissions in php
• Left Join multiple times 2 tables using same column ON
• How to get sum of same value and different value in same row?
• Group query result data by column and set default values for all missing columns in each group
• Charts/Graphs for RSFORM using mysql for VChart and/or PlotaLot
• PlotALOT RSForm Query showing only one result