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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT DATE_FORMAT(post_date, '%M %Y') as month ,(SELECT group_concat( wp_terms.name separator ', ') FROM wp_terms INNER JOIN wp_term_taxonomy on wp_terms.term_id = wp_term_taxonomy.term_id INNER JOIN wp_term_relationships wpr on wpr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id WHERE taxonomy = 'job_listing_region' and wp_posts.ID = wpr.object_id ) AS 'location', COUNT(*) as count FROM wp_posts WHERE post_type='job_listing' and post_status IN ('publish', 'pending', 'expired') GROUP BY month, location order by post_date |
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:
|
1 2 3 4 5 6 |
[0] => Array ( [month] => October 2016 [location] => LOCATTION NAME [count] => 1 ) |
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).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
function get_summary_stats() { global $wpdb; $sql = <<<QUERY SELECT DATE_FORMAT(post_date, '%M %Y') as month ,(SELECT group_concat( {$wpdb->prefix}terms.name separator ', ') FROM {$wpdb->prefix}terms INNER JOIN {$wpdb->prefix}term_taxonomy on {$wpdb->prefix}terms.term_id = {$wpdb->prefix}term_taxonomy.term_id INNER JOIN {$wpdb->prefix}term_relationships wpr on wpr.term_taxonomy_id = {$wpdb->prefix}term_taxonomy.term_taxonomy_id WHERE taxonomy = 'job_listing_region' and {$wpdb->prefix}posts.ID = wpr.object_id ) AS 'location', COUNT(*) as count FROM {$wpdb->prefix}posts WHERE post_type='job_listing' and post_status IN ('publish', 'pending', 'expired') GROUP BY month, location order by post_date QUERY; $raw_result = $wpdb->get_results( $sql, 'ARRAY_A' ); // collapse it! $results = array(); foreach ($raw_result as $lineIndex => $value) { $newKey = $value['month']; if (!array_key_exists($newKey, $results)) { // add a new results line $results[$newKey] = array('month' => $newKey); } // add the value to its own column on the results line if ($value['location']) { $results[$newKey][$value['location']] = $value['count']; } else { $results[$newKey]['Not Set'] = $value['count']; } } // as an added bonus, add the total to the result line foreach ($results as $resultIndex => &$resultValue) { $resultValue['Total'] = array_sum($resultValue); } return $results; } |
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.