Visualizer: Sample queries to generate charts

Easy Digital Downloads

Total revenue per payment gateway

SELECT pm1.meta_value, SUM(pm2.meta_value) FROM `wp_postmeta` pm1, `wp_postmeta` pm2 WHERE pm1.meta_key='_edd_payment_gateway' AND pm1.post_id=pm2.post_id AND pm2.meta_key='_edd_payment_total' AND pm1.meta_value<>'' GROUP BY pm1.meta_value

Number of subscriptions per product

SELECT prod.post_title, COUNT(*) FROM `wp_edd_subscriptions`, `wp_posts` prod, ( SELECT pm.post_id AS 'postid', pm.meta_value AS 'subsid' FROM `wp_posts` p, `wp_postmeta` pm WHERE p.post_type='edd_payment' AND p.id=pm.post_id AND pm.meta_key ='subscription_id' ) subs WHERE `wp_edd_subscriptions`.id = subs.subsid AND prod.id = product_id GROUP BY product_id ORDER BY prod.post_title

Total payments per subscribed product

SELECT prod.post_title, SUM((SELECT pp.meta_value FROM wp_postmeta pp WHERE pp.post_id=subs.postid AND pp.meta_key='_edd_payment_total')) FROM `wp_edd_subscriptions`, `wp_posts` prod, ( SELECT pm.post_id AS 'postid', pm.meta_value AS 'subsid' FROM `wp_posts` p, `wp_postmeta` pm WHERE p.post_type='edd_payment' AND p.id=pm.post_id AND pm.meta_key ='subscription_id' ) subs WHERE `wp_edd_subscriptions`.id = subs.subsid AND prod.id = product_id GROUP BY product_id ORDER BY prod.post_title

Payment per month/year

SELECT DATE_FORMAT(p.post_date, '%M, %Y') AS dt, CAST(SUM(pm1.meta_value) AS DECIMAL(10,2)) FROM `wp_postmeta` pm1, `wp_posts` p WHERE p.id=pm1.post_id AND pm1.meta_key='_edd_payment_total' GROUP BY dt, YEAR(p.post_date), MONTH(p.post_date) ORDER BY YEAR(p.post_date), MONTH(p.post_date)

WooCommerce

You can use the below resources to generate queries:

WordPress (core)

Comments per post

SELECT post_title, COUNT(DISTINCT c.`comment_ID`) AS cc FROM wp_posts p LEFT JOIN wp_comments c ON p.id = c.`comment_post_ID` GROUP BY p.ID ORDER BY cc DESC

Popularity of authors on the basis of comments per author

SELECT u.user_nicename, COUNT(DISTINCT c.`comment_ID`) AS cc FROM wp_users u LEFT JOIN wp_posts p ON p.`post_author` = u.id LEFT JOIN wp_comments c ON p.id = c.`comment_post_ID` GROUP BY u.id ORDER BY cc DESC

Using PHP variables in DB queries

Variables can be used in the DB queries for Visualizer by using a code snippet. The snippet can be added at the end of the functions.php file of your child theme, or in a dedicated plugin.

The code snippet will contain a part of the DB queries, more specifically the ones where PHP variables can be added.

In the following example, we'll use a basic query to fetch WordPress posts in a table chart. There are two users on the website, one is the creator of the website and some content (stefancoti), and the second user (vytis) is the author of a few posts.

We'll use the same query, once without any PHP variables and the second time with the PHP variable get_current_user_id()

SELECT * FROM wp_users

The output of this query consists of all posts within the website(logged in as stefancoti):

Now, we'll use the code snippet to fetch only the posts where the author is the current user of this WordPress instance.

SELECT * FROM wp_users

Code Snippet

add_filter( 'visualizer_db_query', function( $query, $chart_id ) {
	if ($chart_id = 8727) {
		$query = str_replace( "LIMIT 1000", " WHERE post_author=". get_current_user_id(), $query );
		return $query;
	}
}, 10, 2 );

The output of this query combined with the code snippet is(logged in as vytis):

To make sure the above example work, you need to set the table chart to update live. Without live updating, the query will run without the code snippet being interpreted by Visualizer on time. For more details on the schedule options for charts, check this chapter of the documentation.

Can't find the query you want? Contact us here and we might be able to help you!