Once upon a time I tried to build a theme and plugin store into the WordPress dashboard. Kind of like iTunes for WordPress. I called it WP App Store. Long story short, it didn’t work, I started this company (Delicious Brains Inc.), and the rest is history.

Maybe you already knew that. But did you know that the store itself was a custom app built on WordPress? Yup. I used a few common plugins but mostly it was a lot of custom code. The entire ecommerce system was custom.¹

I learned a lot. One of the most important things was why it is sometimes necessary to use custom tables in WordPress. In fact, I wrote a blog post about it at the time. It wasn’t long after that I stopped working on WP App Store, but I did write some code for managing custom tables, a very simple object-relational mapping (ORM).

If you’re not a fan of static methods, you’re probably not going to like the following code much. The use of static methods was inspired by a custom CMS that I had worked with in the past.

First, I should warn you that this class requires PHP 5.3+ due to the get_called_class() function. The class provides CRUD methods to operate on a custom table. It is an abstract class, intended to be extended by other classes whose names match the name of the table.

Let’s look at the example from my blog post. In it, I suggested creating a custom table to store order meta data. Something like this…

CREATE TABLE IF NOT EXISTS `wp_wpas_order_meta` (
  `order_id` bigint(20) NOT NULL,
  `publisher_id` bigint(20) NOT NULL,
  `product_title` varchar(255) NOT NULL,
  `product_type` varchar(255) NOT NULL,
  `commission_amount` float NOT NULL,
  `is_test` tinyint(4) NOT NULL,
  `is_refunded` tinyint(4) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

We can extend the model class above with just a few lines of code to operate on this table.

class WPAS_Model_Order_Meta extends WPAS_Model {
    static $primary_key = 'order_id';
}

The name of the class is important. If you look at the WPAS_Model::__table() method, it derives the name of the database table from the name of the class. So if your class name doesn’t match your table name, you will end up with SQL errors.

You’ll also notice that we’re overriding the $primary_key class variable. Our custom table uses order_id as the primary key column whereas the WPAS_Model class defaults to id.

Now let’s take this new class for a spin. First, let’s add some data…

$order_id = 4556;

WPAS_Model_Order_Meta::insert( array(
    'order_id' => $order_id,
    'publisher_id' => 151,
    'product_title' => 'Canvas',
    'product_type' => 'theme',
    'commission_amount' => '10.23',
    'is_test' => '0',
    'is_refunded' => '0',
) );

And now let’s retrieve the data from the database and print it out…

$order_meta = WPAS_Model_Order_Meta::get( $order_id );
print_r( $order_meta );

Output:

stdClass Object
(
    [order_id] => 4556
    [publisher_id] => 151
    [product_title] => Canvas
    [product_type] => theme
    [commission_amount] => 10.23
    [is_test] => 0
    [is_refunded] => 0
)

Now let’s update the product title to “Storefront”…

$data = array( 'product_title' => 'Storefront' );
$where = array( 'order_id' => $order_id );
WPAS_Model_Order_Meta::update( $data, $where );

That’s quite a few lines for just updating a single field, so let’s add some convenience functions for flipping on the is_test and is_refunded flags…

Now we can very concisely set those flags…

WPAS_Model_Order_Meta::set_as_test( $order_id );
WPAS_Model_Order_Meta::set_as_refunded( $order_id );

Finally, let’s remove our data from the database…

WPAS_Model_Order_Meta::delete( $order_id );

To be clear, I’m not suggesting that this eliminates the need for object classes. In our example I would still have a WPAS_Order class with lots of methods for things like calculating tax and handling order completion. These model classes are intended to house the database-related code and keep it out of the object classes.

It’s been 3 years since I wrote this code and I have mixed feelings about it. If I were in need of a simple ORM like this today, I’d probably recode it, getting rid of the static methods, and returning an array of model object instances rather than an array of stdClass objects. I might also use the Factory design pattern. Or maybe I’d say the heck with it and go looking for a simple ORM that’s already out there and well tested. If I needed more than a simple ORM, I’d probably go with Eloquent (used by Laravel).

Do I really need an ORM at all? Sure, it maintains a separation between the database layer and business logic layer, but how much of a help is that. On one hand, it greatly increases the number of files you have to bounce between during development and on the other it reduces the length of the object classes.

There’s pros and cons for sure. It’s probably overkill for most WordPress plugins, but large complex plugins could probably benefit from it. What do you think?

More On Custom Tables

• Article: Custom Table for WordPress Custom Fields by Brad Touesnard
• Podcast: Apply Filters Episode 12
• Podcast: Apply Filters Episode 32
• Article: Reasons for Custom Tables and an API by Pippin Williamson

¹ Why did I build the store from scratch? Why not use WooCommerce, EDD, etc? Because it required a very custom checkout experience and it would have been very tough (and likely hacky) to customize an existing ecommerce system.