
The way WordPress decided to deal with adding custom pieces of data is both cumbersome and elegant. Because it stores ALL metadata in one table, it can be painful to try to figure out what a data object looks like. Things like adding Foreign keys and enforcing certain relationships can not be done easily.
Your new BFF: wp_postmeta
.
In WordPress, Custom fields do not actually exist until you add data to them. That means getting all the items that do NOT having some data can get tricky. The reason for this, is that posts do NOT have a structure you can reliably depend on, like a traditional programming object model. Rather, custom meta was tacked onto WordPress as a bit of an afterthought.
The Car example
Let’s imagine you have a custom post type called Car
and you want to have custom fields like color
, towing_capacity
, fuel_type
, and battery_capacity
. If you were building a database of Cars, or building a list of Car objects in code, you might add a Tesla model 3 and picture this:
Name (post_title) | Tesla Model 3 |
color | black |
towing_capacity | 2000 lbs |
fuel_type | electricity |
battery_capacity | 62 kwH |
You might expect a 1999 Dodge Neon might look like this:
Name (post_title) | Dodge Neon |
color | Lime |
towing_capacity | |
fuel_type | gas |
battery_capacity |
However, in reality, in the WordPress database, the Dodge Neon looks more like this: 1
Name (post_title) | Dodge Neon |
color | Lime |
fuel_type | gas |
The towing_capacity
and battery_capacity
fields are not added at all in the database to the Neon. You never added the content, so nothing gets added to the wp_postmeta
table.
So what? Why does this matter?
This sounds great and efficient, and from a database size perspective, it is. However, if those fields don’t exist, how do you find all the cars that can’t TOW? Or what if you want to find all the cars that use diesel but have no towing capacity? Often, a WordPress developer will do one query to get ALL the car records, then get call get_postmeta
on each entry. If you have 100,000 cars in your database, that’s 100,001 calls to your database.
A database administrator (DBA) would look at this issue and tell you that it can be done in one SQL statement. If Cars had their own structure and table, you could do something like this:
SELECT ID,fuel_type
FROM wp_cars
WHERE towing_capacity = ''
AND fuel_type = 'diesel'
Simple. Easy to understand and read. However, WordPress puts all the postmeta into one table so to do it efficiently in ONE query, you have to do something like:
SELECT p.ID, m.meta_value
AS fuel_type
FROM wp_posts AS p
INNER JOIN wp_postmeta AS m on m.post_id = p.ID
WHERE p.post_type = 'car'
AND m.meta_key = 'fuel_type'
AND p.ID NOT IN (
SELECT DISTINCT post_id
FROMwp_postmeta
WHERE meta_key = 'towing_capacity'
)
This is actually two separate queries in the database as we’re using a subquery, but it’s one database call and a lot more efficient than making 100,001 calls to the database. It’s also a LOT harder to understand and read, which is why many WordPress developers will typically just loop through the items and use the built-in meta functions, despite its inefficiency.
While it’s true that it’s a drawback, structuring the WP database this actually offers much better flexibility overall. But I’ll save why it is a good thing for a different post.