I’m writing a special plug-in for WordPress for a client and have a working keyword / custom-field search feature that works. You enter your keyword or phrase and it searches multiple fields for the keywords, returning only distinct results.
If I search for a newspaper titled “The Herald of Freedom & Light” I get 5 article results.
SELECT
SQL_CALC_FOUND_ROWS
DISTINCT
wp_posts.* FROM wp_posts
LEFT JOIN `wp_postmeta` ON `wp_posts`.`ID` = `wp_postmeta`.`post_id`
WHERE
1=1
AND `post_type` = 'post'
AND `post_status` = 'publish'
AND (`wp_postmeta`.`meta_key` = 'newspaper_title' AND `wp_postmeta`.`meta_value` = 'The Herald of Freedom & Torch Light')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10
If I attempt to search for the same newspaper with a subject of “Politics” I get 0 results (when it should be no less than 3).
SELECT
SQL_CALC_FOUND_ROWS
DISTINCT
wp_posts.* FROM wp_posts
LEFT JOIN `wp_postmeta` ON `wp_posts`.`ID` = `wp_postmeta`.`post_id`
WHERE
1=1
AND `post_type` = 'post'
AND `post_status` = 'publish'
AND (`wp_postmeta`.`meta_key` = 'newspaper_title' AND `wp_postmeta`.`meta_value` = 'The Herald of Freedom & Torch Light')
AND (`wp_postmeta`.`meta_key` = 'article_subject' AND `wp_postmeta`.`meta_value` = 'Politics')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10
I’ve searched around, and most answers to questions about JOINS with multiple conditions say “move the conditions into the join”. Well, I’ve done that with the following query and it turns up 16 results (including post revisions I’ve filtered out!) without properly searching through the keywords I gave. Not only that, but by moving the conditions a 1-condition search like just for the newspaper_title will turn up the same 16 results!
SELECT
SQL_CALC_FOUND_ROWS
DISTINCT
wp_posts.* FROM wp_posts
LEFT JOIN `wp_postmeta` ON
`wp_posts`.`ID` = `wp_postmeta`.`post_id`
AND (`wp_postmeta`.`meta_key` = 'newspaper_title' AND `wp_postmeta`.`meta_value` = 'The Herald of Freedom & Torch Light')
AND (`wp_postmeta`.`meta_key` = 'article_subject' AND `wp_postmeta`.`meta_value` = 'Politics')
WHERE
1=1
AND `post_type` = 'post'
AND `post_status` = 'publish'
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 10
How must I rewrite my SQL for these multiple conditions to play nicely together? I’ve got 7 other fields to work into this search functionality with similar conditions.
Your problem is that you’re trying to logically compare values that are contained in different rows. A single row can’t have a meta_key that is both “newspaper_title” and “article_subject”. If you change the AND to an OR, then you’ll receive records that are either or and not both.
I think the solution here is to use a pivot table for the meta values. The idea here is to aggregate the information contained in multiple rows into a single row per post_id then in the where clause target where all the columns have a value of 1. I’ve put together a script as an example based on what information you’ve provided:
Please ensure this script is run on a test environment and does not conflict with your existing data
The final query in the script is the one you’re after. With the test dataset it returns:
For each attribute you need to check, you would add an additional case statement as shown above in the meta query and add to the where clause the condition to check whether it was found. (i.e. newTargetedValue = 1)
Update based on OP comment:
In my opinion, the score or count method isn’t as flexible as using a pivot table. The inner/pivot table is essentially setting flags for the attributes that have matched based on the cases you’ve provided. (The value will be 1 or 0) In your current example, you’re just ANDing all those together so everything has to be set so a score or count could be used. If you later were required to logically compare those attributes to accommodate a more advanced search, the count/score no longer works. I’ll try to explain with an example.
Say I asked you to add to the search results you’ve already provided in the question, where I want all posts that had a meta value of ‘day’ = ‘Sunday’ regardless of the paper. So in short I want:
That wouldn’t work with a count/score because matching rows can return 1, 2, or 3 rows depending on how many attributes match.
With a pivot table, you can still use logical expressions: (Including the meta flags for clarity)
Here are the results:
Yes, it looks somewhat complex, but once you have the initial idea it’s pretty straight-forward as to how you go about adding more search targets and how to logically compare them to get the records you’re after.
Hope that explanation made things a little more digestible.
Your code as it is tries to find rows where the meta_key is both “newspaper_title” and “article_subject” at the same time. That is of course impossible. What you really want to ask is, “which rows in
wp_postshave a row inwp_postmetawith “newspaper_title” and another row with “article_subject”.You can do that with multiple
JOINs, but they will explode pretty quickly as you have more and more criteria to check. Another way is to check that the number of rows that match any of the criteria matches the number of criteria:You can change the above query to use a subquery instead, like
2 = (SELECT COUNT(*)...)Using your specific situation (please excuse any minor syntax problems since I don’t normally work with MySQL):
A LEFT JOIN is an OUTER join, which means you will get all rows from table on the left, with matching rows or null values if match is not found from table on the right.
Switch to INNER JOIN to limit number of rows returned to only these, that have matches in table on the right.
I figured I would share what I’ve found with your help.
1.) This is an schema issue.
We left WordPress’ post/post-meta format in place and used a plugin to store our record data within the post-meta tables. This created a major issue: instead of having a definitive table designed to contain our data, we had a post (with just a title) and a slew of associated post-meta rows (sorta key-value style).
Therefore searching with 1 query is impractical.
2.) The client demanded search across multiple fields (late).
I took our existing keyword search that operated across all relevant post-meta rows and attempted to expand it. This originally worked, at the time, because it grabbed the first result and returned it… and it didn’t care about multiple matches. As soon as I tried to have multiple conditions that applied only to the post-meta table I pushed the limit of what SQL should do.
We really should have made our own table.
3.) We really should’ve made our own table from the beginning.
I knew it was possible that the client would want to search in very specific ways. And, I should have foreseen the difficulties of spreading the actual column data over multiple rows in a second table. Except for an edge-case this is bad design.
Conclusion
When you have need of searching through nearly every attribute of a post simultaneously don’t rely upon WordPress’ Custom Fields. They are very powerful for everything except searching. Build your own table, link it to posts, search in your custom table and join in the valid postsânot the other way around!