Question 165516 in Wordpress Question posted in
The official Wordpress documentation can be found here.

“Can WordPress use an SQL Select statement to get all records with one or more values in a string?”

I am stuck with this problem:

I made 3 custom meta_key in wp_postmeta. Now I have to select all the records that contain in the column meta_value, one or more words, from a string I provide.

This is the SQL I wrote, but it’s not giving me back any results, even if I have one word matching the REGEX.

 SELECT post_id FROM wp_postmeta WHERE
 meta_key = 'packaging_attributes'
 AND
 meta_key = 'properties_attributes'
 AND
 meta_key = 'tech_functions_attributes'
 AND
 meta_value REGEXP 'chased-floor|chased-wall-plasterboard|alarm-en|0-22|0-220-75|15|25'

This is the screenshot from the db:enter image description here

As you can see in row 1008 I have chased-wall-plasterboard, that should match the above select, and give me back 39 as result. But it’s not working.

If I remove the three meta_key = column_name I am going to get tons of results but not the 39.

Tags:

2

Answers


  1. 0

    I assume that what you really want are meta posts having records with all 3 meta values in the current WHERE clause. If so, then aggregation is one approach:

    SELECT post_id
FROM wp_postmeta
WHERE meta_key IN ('packaging_attributes', 'properties_attributes', 'tech_functions_attributes') AND
      meta_value REGEXP 'chased-floor|chased-wall-plasterboard|alarm-en|0-22|0-220-75|15|25'
GROUP BY post_id
HAVING COUNT(DISTINCT meta_key) = 3;

    It is not clear whether the restriction on meta_value really belongs there.

    Login or Signup to reply.
  2. 0

    You should use OR operation to gather all 4 rows.

     SELECT post_id FROM wp_postmeta WHERE
 meta_key = 'packaging_attributes'
 OR
 meta_key = 'properties_attributes'
 OR
 meta_key = 'tech_functions_attributes'
 OR
 meta_value REGEXP 'chased-floor|chased-wall-plasterboard|alarm-en|0-22|0-220-75|15|25'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search