WordPress Queries with IN Clauses (Who Knew)?

A little over a year ago, I wrote a post on how to use WP_Meta_Query whenever you have a set of keys you want to use to help pull back information from the database.

What about the case, though, when you have a number of different keys that would result in creating a really long array for the WP_Meta_Query class? For example, what if you had to loop through a collection of data before even setting up the query?

On some level, it might feel that like the natural thing to do would be to:

  1. iterate through the collection of keys,
  2. dynamically build up the results,
  3. combine them into a single result set,
  4. then work with whatever you’re given.

But doesn’t that sound a bit cumbersome (let alone slow)?

When it comes to using the WordPress API, I do what I can to stick to it before talking, say, directly to the database but there are also times where it makes sense to write a raw query than to write some type of clever code just to get the WordPress API to work.

WordPress Queries with IN Clauses

Before getting into the rationale as to why I’ve done the things that I have, I want to explain the problem and the approach. This will likely save someone from jumping into the comments prematurely.

WordPress Queries with IN Clauses
This screenshot has nothing to do with the query. Just a shot of the IDE for fun.

So here go:

  • I have a standard array of values that are eventually used to help take metadata and create a custom post type from them (because they’ve been imported from a third-party source).
  • I’m a big proponent of parameterizing queries (and thus using prepare) to make sure that data is being correct queried against the database. Unfortunately, this was not happening when trying to execute this query. I’ll explain why later in this post.
  • Thus, take the array and converting it into a string is helpful but it still doesn’t solve the problem as to why the standard prepare function wasn’t working.

With that said, I’m going to explain a few things:

  1. why I opted to use an array to store metadata values,
  2. why I’ve used implode to convert them to a string,
  3. why I am not using prepare to handle the query.

On Metadata Values

The reason for keeping metadata in an array as a property of the class is because this array could change over time.

That is, we may need to import additional third-party data, would need to remove third-party data, or we may need to make some modifications to whatever is present.

When this data is kept in a single place, it makes it much easier to manage for future versions of the code.

Imploding The Array

Whenever you’re running a query against the database and you have to work with an array of data, you can use WP_Meta_Query and use each key as parts of the arguments array.

But if you have a relatively large set of data, then you first have to loop through all of it, then you have to create the query, then you have to process it.

And once you’ve done all of that, I’m not convinced that the code that’s been written hasn’t come at the expense of performance. This is why, sometimes, I opt to use wpdb.

Not Using Prepare

Now, when interfacing directly with the database, I try to make sure:

  1. I have a good reason to do so,
  2. I’m using parameterized queries.

But I’ve been working with this specific set of data for a little while trying to use every permutation of WordPress development of which I am aware (including talking to several peers about it) to try to make this work in the best way possible.

It wasn’t happening, though. And that’s when I stumbled across the following page in the Codex:

In 99% of cases, you can use $wpdb->prepare() instead, and that is the recommended method. This function is only for use in those rare cases where you can’t easily use $wpdb->prepare(). One example is preparing an array for use in an IN clause.

And that’s exactly was I was trying to do: I was looking to search the post meta table where the meta key values were contained in an array.

So here’s how I worked all of this out.

First, I created an array to hold the various meta keys that I know I’ll eventually need to map (though the value of the keys doesn’t matter for the purpose of this post):

Then I converted them to a MySQL-ready string. Granted, this is not user input and it’s being using in an IN clause, so it can’t be used in a prepare statement:

Finally, I created the query and retrieved the results:

And that’s how I ended up working with the arrays, the class property, and setting up my query.

Is this is the best way to work with WordPress queries with IN clauses? I’m not sure, but given my experience, what I’ve read, and how it’s working, I’m happy with the end result.

2 Replies to “WordPress Queries with IN Clauses (Who Knew)?”

  1. When I do IN clauses, I implode by comma an array_fill list of %s strings with a count of the values I’m checking. Then I pass those vars into a $prepare array I use for ongoing prepared values before and after the IN clause usage.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.