If you need to write a query against the WordPress database, grab a reference to $wpdb and get to work, right?

In some instances, sure. I’m not opposed to this. After all, the API exists for a reason. But sometimes, I think we bypass built-in APIs. We go straight for $wpdb without looking to see if other hooks already exist.

Distinct

If a hook exists, why not use it? It’s one level of abstraction above the database and it uses the native API.

As mentioned in a previous post, I want to cover a few ways to customize queries that run against the database.

Case in point: selecting DISTINCT records from WordPress.

DISTINCT Records in WordPress

It’d be easy to jump into the code, show how to do it, and then move on to the next post. But that’s no good – how are you supposed to understand what you’re doing?

Defining DISTINCT

Why not review what a DISTINCT record is? So before taking a look at how to do this in WordPress, check this out:

The ALL and DISTINCT options specify whether duplicate rows should be returned. ALL (the default) specifies that all matching rows should be returned, including duplicates. DISTINCT specifies removal of duplicate rows from the result set. It is an error to specify both options. DISTINCTROW is a synonym for DISTINCT.

Depending on your level of experience with MySQL, this may read like jargon. Or it may be clear. Either way, perhaps the simplest way to define this is like so:

The ALL and DISTINCT options specify whether duplicate rows should be returned.

DISTINCT comes in handy whenever you’re working with data that may return duplicate records. This is something that happens when you’re working with, say, a JOIN.

But I digress.

DISTINCT Queries in WordPress

So now you know what DISTINCT queries do. Here’s how to use them in WordPress.

Let’s say that you’re customizing the search query. Sure, you can use other tools like SearchWP and sometimes that works great. But sometimes you don’t have the luxury of using third-party plugins on a project.

So then you’re left for customizing your own query. So let’s say that you’re going to be writing a search query that will JOIN two different tables (which I’ll cover in a future post).

The problem with this, as mentioned, is that you can retrieve duplicate results. We don’t want that, so we need to make sure we’re selecting distinct records. This way, we only get a single instance of a given record back.

Here’s the code:

First, we hook into the posts_distinct hook. We check to make sure that we’re not in the dashboard and we’re not on the search template.

If we are, then we just return the argument passed into the function. If we aren’t, then we add a DISTINCT clause to the query.

And that’s all there is to it. To see it in action, you can wire this up in your project and take a look at the query that just ran.

A bit easier than writing raw SQL and it uses native API functions. Nice, isn’t it?

Category:
Notes
Tags:

    Leave a Reply