In a previous posts, I’ve talked about how to:
There’s a lot more to do than just viewing the last query and selecting distinct rows, though. For example, you can also JOIN two tables in WordPress without having to write raw SQL.
So to be consistent with some of the previous posts, I thought I’d share how to do this using the WordPress API .
How To JOIN Two Tables in WordPress
Before looking at code for actually how to perform a
JOIN , let’s define the operation. SitePoint has a pretty good definition:
JOIN” is an SQL keyword used to query data from two or more related tables. Unfortunately, the concept is regularly explained using abstract terms or differs between database systems. It often confuses me. Developers cope with enough confusion, so this is my attempt to explain JOINs briefly and succinctly to myself and anyone who’s interested.
If we were to simplify this, I’d say this would work:
A `JOIN` is a way to retrieve records from at least two tables.
For example, in WordPress, let’s say we wanted to pull back all information for a single post. This would include information from the
posts table as well as the
postmeta table. To do this, we’d write a query to would
JOIN the two tables based on, say, a given post ID.
Though it’s not too bad to do this within normal SQL syntax, it’s a good practice to use the WordPress API.
Here’s an example showing how to tweak the query that’s runs on the search page. It performs a
JOIN with another table:
The code comments should be pretty clear. If not, though, here’s what’s happening:
- We check to make sure we aren’t in the dashboard and we’re on the search page. If either of these are true, then we just return the `$join` clause as it was set when passed into the function.
- Next, we grab a reference to `$wpdb`.
- We append the `JOIN` clause to the existing query such that it joins the `posts` table to the `post` meta table via the `post_id`.
This will return records from both the posts table and the post meta table which will ultimately allow us to display as much content as we want on the search page permitting it’s contained within those tables.
Other Types of JOINs
Depending on the nature of what you’re doing, this may not be something you need to do. After all, it is possible to get information from the post meta table using another API.
But let’s say you’ve stored some custom information somewhere in that table (or perhaps another table) than an API doesn’t make available.
This is one way in which you can gain access to that information using the WordPress API and SQL.