WP Job Manager summary statistics

My client required a reporting mechanism for job listing submissions on their site. They needed to know how many jobs had been created each month, by region. There are a few ways you could do this.

One would be to pump out a bunch of queries, one per region, one after the other, then iterate the results to classify the content month by month. That’s doable, but not much fun.

A more efficient way is to generate a single custom SQL query that uses the COUNT function to gather just the numbers we need.

This took some tuning to get it right; it’s basically gathering instances of taxonomy terms, based on the published date of the post they are attached to. The output of this query is an array where each item has a unique date (month, year) and a unique taxonomy term (or terms, comma separated, if there are more than one), ie:

To construct a table where you have a single array, with one row per date range, you can iterate the array, concatenating lines as you go.

Here’s a full example, with the current database table prefix included (rather than assuming we’re using wp_¬†everywhere).

The only thing I’d observe is that the raw results array will return values of location, comma separated, where multiple terms are used, and these values will be transcribed verbatim, into the table. If you wanted to split those values to each of the terms, you can do so when collapsing the data, but it will inflate the summary numbers.

Leave a Reply

Your email address will not be published. Required fields are marked *