This entry is part 5 of 5 in the Building a Database Abstraction Layer Series
- Custom Database API – Reasons for Custom Tables and an API
- Custom Database API – Structuring Your Data
- Custom Database API – Creating the Tables
- Custom Database API – The Basic API Class
- Custom Database API – A Sample Orders Class
In the previous part of this series we wrote the base class that we can then extend for each table in our database. For this part, we’re going to look at what an orders table might look at and build the database class for interacting with the orders table.
Note: this is meant purely as a sample and could be extended much further depending on your specific needs. It is intended that the class we write in this tutorial be a foundational API and not necessarily a complete one.
Looking back at our base database class, we have the following methods:
Here’s the complete base class:
Our orders class will extend the base class, meaning all of these methods are accessible in our orders DB class. We will name our new class PW_Orders_DB. Since we are extending the base class, we will only need to write a few new methods. The new methods we will write include:
- __construct() – This will define our table name, primary column ID, and the table version.
- get_columns() – This will retrieve an array of table columns and their data types.
- get_column_defaults() – This will retrieve default values for each column.
- get_orders() – This will return an array of order objects.
- count() – This will count the number of orders.
- create_table() – This will create our database table.
We could also write additional methods, such as get_order(), but those are not necessary for this tutorial. They are trivial to write once the foundational concepts are solid.
As before, we will go over each method and detail what it does and how it works. Let’s start with the __construct() method.
The constructor, which is invoked automatically when the class is instantiated, does nothing more than define the name of our table, the primary column, and the version number.
The create_table() method is responsible for creating and updating the database table structure. Refer back to our part on creating database tables if you would like a refresher.
Our table will contain the following columns:
Note: these are just example columns. When building an orders table for production usage, you may have additional tables as well.
The create_table() method looks like this:
To trigger the table creation, we would run something like this:
$db = new PW_Orders_DB; $db->create_table();
Usually this would be done during a plugin install / activation routine. Running create_table() also works when you need to update the structure of the table after its been created, such as when you release a new version of your plugin with new table columns included.
get_columns() and get_column_defaults()
These two methods define the name and datatypes of each column, and the default value of each. While helpful on their own for certain scenarios, they are primarily used in the insert() and update() methods of the base class.
The get_orders() method is the primary class method that will be used the most. It will provide a way to easily retrieve order records from the database and will include options to specify the number of orders to retrieve, the sorting method, and several other parameters.
For example, our method will permit queries like this:
$orders_db = new PW_Orders_DB; $orders = $orders_db->get_orders( array( 'email' => 'email@example.com' ) );
That would retrieve all orders made with the email address firstname.lastname@example.org.
Another example to retrieve the 10 most valuable orders, in terms of purchase total:
$orders_db = new PW_Orders_DB; $orders = $orders_db->get_orders( array( 'number' => 10, 'orderby' => 'total' ) );
This is where a database abstraction layer becomes immensely helpful. When you have robust methods for retrieving database rows, you avoid having to write extensive SQL queries every time you wish to query data from a table.
The get_orders() method is significantly more complex than our previous methods, but when broken down, it is not difficult to grok.
Our complete method:
At the top of the method, we use the wp_parse_args() function to set up default values. If you have not made it a habit of using wp_parse_args(), I highly recommend it.
After we have set up the default parameters, we set up the where statements for each of the optional parameters, of which there are four:
- order_id – Supports passing in a single order ID or array of order IDs.
- status – Supports a single status or an array of statuses.
- email – Supports a single email address or an array of addresses.
- date – Supports a single date or array of start and/or end dates.
These four where clauses allow us to easily retrieve specific subsets of orders. The first three where clauses are nearly identical and look like this:
The fourth where clause for the date parameter is a bit different. This one permits for orders in specific date ranges to be queried and is much more complex. It allows for two date formats to be passed in:
- A single date string, such as January 1, 2017 or 2016-03-12
- An array with start and/or end dates
We parse the date supplied and then construct the SQL:
Next we set up the orderby option:
Notice that we use the get_columns() method to ensure that the value specified is a valid column name. If it is not, we fall back to ordering by the primary table column.
If ordering by subtotal or total, we have to append +0 to the column name in order to ensure that float values are sorted properly.
Up next is caching. Please do not forget to cache database queries.
To ensure that subsequent queries are cached without interfering with our ability to adjust the query parameters and retrieve non-stale data, we create a unique cache key based on the query arguments supplied to get_orders(). That key is constructed like this:
$cache_key = ( true === $count ) ? md5( 'pw_orders_count' . serialize( $args ) ) : md5( 'pw_orders_' . serialize( $args ) );
And finally, we construct the final query and then cache the results:
Now we have just one method left.
The count() method supports the exact same parameters as the get_orders() method but simply returns an integer instead of an array of orders. It is actually just a wrapper to:
get_orders( $args, true );
The method looks like this:
And we are done! Here’s our final orders database class:
There are numerous ways this class could be expanded to be even more helpful. Here are a few suggestions:
- Support querying orders by the customer_id column
- Add additional columns for billing address details and customer / admin notes
- Include a get_order() method for retrieving a specific order