Development

December 18, 2019 Updated on December 19, 2019

Calculating a paid price average from user reviews

In this blog post you will learn how you can extend the functionality of JReviews using its powerful Events system, to calculate an average "paid price" for products or services using information provided by users through review custom fields. This is a great way to utilize the information you already have on your site to provide even more value to your site visitors.

In my previous developer blog post I showed you how you can create a simple, but effective e-commerce store in Joomla and WordPress, without the need for 3rd party solutions. This was done using Developer Filters. In this example, we'll be using the Events System to perform a calculation once specific events are triggered.

For example, if you have an affiliate site for products or services, one of the questions you can ask users when they leave a review is how much they paid for the product. This is great feedback, but it would be even better if you could display the average price paid by all users that have left a review. And we can easily do this with a listing custom field and a little bit of code logic to calculate the average and store it in that listing field.

Get the code for this tutorial from Github

Requirements to setup automatic average calculations

JReviews 3 for Joomla or WordPress
JReviews Queue Add-on (free) - this is optional
Server file access to your server to create the necessary files.

How does calculating price average from user reviews work?

It's quite simple actually. We are going to use the JReviews Events System to listen for events for new user reviews, review updates and review deletions, and when any of those events takes place, we calculate the average paid price and store it in a listing custom field. We'll be using the following three Review events:

JReviews\Events\ReviewWasFirstPublished
JReviews\Events\ReviewWasDeleted
JReviews\Events\ReviewWasUpdated

Steps to automate the calculation of the paid price average

Create review and listing price fields of decimal or integer type

If you don't already have a "price paid" review custom field, you need to create it now. You also need to create the "average price paid" listing custom field. The field names you use here will need to be replaced in the code below.

For this tutorial, we are using jr_pricepaid for the review field, and jr_averagepricepaid for the listing field.

If the listings are submitted by site users, you can change the listing price field Access Setting so that only administrators are allowed to submit/edit the field. This way the average for the listing cannot be changed by the listing owner.

Mapping Review Events to Listeners using the Event Listener Provider

The first step is to associate a specific listener with the three Review events mentioned above. In other words, we tell JReviews that when any of these events is triggered, it needs to run certain code. In this case, our listener will be called UpdateListingAveragePrice.

When the ReviewIsFirstPublished event is triggered => the UpdateListingAveragePrice listener handles the event to update the average.

We are going to use the same UpdateListingAveragePrice listener for all three events.

While you can have as many Event Listener provider files as you want, for this example, we are going to use a generic provider that you can continue to use for other Event/Listener logic you want to add in the future.

In Joomla create the following file:

/templates/jreviews_overrides/events/providers/custom_listener_provider.php

In WordPress create the following file:

/jreviews_overrides/events/providers/custom_listener_provider.php

And the code for the provider is the following:

<?php
defined( 'MVC_FRAMEWORK') or die;

use Clickfwd\Listener\ListenerProvider;

class CustomListenerProvider extends ListenerProvider
{
	protected $listen = [
		'JReviews\Events\ReviewWasFirstPublished' => [
			'JReviews\Listeners\UpdateListingAveragePrice',
		],
		'JReviews\Events\ReviewWasUpdated' => [
			'JReviews\Listeners\UpdateListingAveragePrice',
		],
		'JReviews\Events\ReviewWasDeleted' => [
			'JReviews\Listeners\UpdateListingAveragePrice',
		]
	];
}

The keys in the $listen array are the names of the Events, and each key has an array of listeners. So for example, if you wanted to perform other actions when a review is submitted, you can map additional listeners.

		'JReviews\Events\ReviewWasFirstPublished' => [
			'JReviews\Listeners\UpdateListingAveragePrice',
			'JReviews\Listeners\AddReviewerToMailingList',
		],

That's just for illustration purposes, and we are not actually going to add a reviewer to a mailing list in this tutorial.

Creating the UpdateListingAveragePrice listener to handle the events

Next we need to create the UpdateListingAveragePrice listener.

In Joomla create the following file:

/templates/jreviews_overrides/events/listeners/update_listing_average_price.php

In WordPress create the following file:

/jreviews_overrides/events/listeners/update_listing_average_price.php

The basic code for our new listener looks like this:

<?php
namespace JReviews\Listeners;

defined( 'MVC_FRAMEWORK') or die;

use JReviews\Listeners\Traits\ListenerSetting;
use Clickfwd\Listener\QueueableListener;
use League\Event\EventInterface;
use GuzzleHttp\Client as Guzzle;

\S2App::import('ListenerTrait','listener_setting','jreviews');

class UpdateListingAveragePrice extends QueueableListener
{
    use ListenerSetting;

    protected $queue = false;

    /**
     * Execution delay in seconds when using the Queue Add-on
     * @var int
     */
    protected $queue_delay = 60;

    public function handle(EventInterface $event){}
}

All listeners receive the $event object in the handle method. And it's within the handle method that we can run the code to calculate the price average for the reviews and update the listing custom field. First, we retrieve the submitted review data and from that we can also get the price value, to see if one was provided.

$review = $event->getReview();
		
// Check if the submitted review includes a price
$pricePaid = (float) \S2Array::get($review,'Field.pairs.'.$this->reviewPriceField.'.value.0');

If the review price field has a value, and this is a review for a listing (com_content, not for a 3rd party solution integrated via the Everywhere Add-on), then we continue. If it's a new review, or an updated review, we calculate the average using all existing reviews for the listing. Below you can see the database query to calculate the average.

$model = new \S2Model();

$averageQuery = sprintf('
	SELECT 
		AVG(%s) 
	FROM #__jreviews_comments AS Review 
	LEFT JOIN  #__jreviews_review_fields AS Field on Review.id = Field.reviewid
	WHERE Review.pid = %d and Review.mode = "com_content" AND Review.published = 1 AND Field.%s > 0
', $this->reviewPriceField, $review['Review']['listing_id'],$this->reviewPriceField);

$averagePrice = $model->query($averageQuery,'loadResult');

If it's a review that's being deleted, then we need to exclude the review from the average calculation, so the database query looks like this:

$averageQuery = sprintf('
	SELECT 
		AVG(%s) 
	FROM #__jreviews_comments AS Review 
	LEFT JOIN  #__jreviews_review_fields AS Field on Review.id = Field.reviewid
	WHERE Review.pid = %d and Review.mode = "com_content" AND Review.published = 1 AND Review.id != %d AND Field.%s > 0
', $this->reviewPriceField, $review['Review']['listing_id'], $review['Review']['review_id'], $this->reviewPriceField);

Notice that we only include published reviews, and reviews for "com_content", which are core JReviews listing. Finally, we can run the database query to update the listing average price custom field using the following database query.

$query = sprintf('
	UPDATE 
		#__jreviews_content 
	SET %s = %s WHERE contentid = %d
', $this->listingPriceField, $averagePrice, $review['Review']['listing_id']);

$model->query($query);

Putting it all together, you can find the complete code for the listener below. You can see that we have a switch statement for the average calculation to exclude the current review if the review is being deleted. We also the define the names of the review and listing custom fields at the beginning of the class with the $reviewPriceField and $listingPriceField class properties. Make sure you update those to match the names of your custom fields.

<?php
namespace JReviews\Listeners;

defined( 'MVC_FRAMEWORK') or die;

use JReviews\Listeners\Traits\ListenerSetting;
use Clickfwd\Listener\QueueableListener;
use League\Event\EventInterface;
use GuzzleHttp\Client as Guzzle;

\S2App::import('ListenerTrait','listener_setting','jreviews');

class UpdateListingAveragePrice extends QueueableListener
{
    use ListenerSetting;

	protected $queue = false;

    /**
     * Execution delay in seconds when using the Queue Add-on
     * @var int
     */
    protected $queue_delay = 60;

	protected $reviewPriceField = 'jr_pricepaid';

	protected $listingPriceField = 'jr_averagepricepaid';

    public function handle(EventInterface $event)
    {	
		$review = $event->getReview();
		
		// Check if the submitted review includes a price
		$pricePaid = (float) \S2Array::get($review,'Field.pairs.'.$this->reviewPriceField.'.value.0');
		
		if ( $pricePaid && $review['Review']['extension'] == 'com_content' )
		{
			$model = new \S2Model();

            switch($event->getName())
            {
                case 'JReviews\Events\ReviewWasDeleted':

                	// Excludes current review (in process of being deleted) from average
					$averageQuery = sprintf('
						SELECT 
							AVG(%s) 
						FROM #__jreviews_comments AS Review 
						LEFT JOIN  #__jreviews_review_fields AS Field on Review.id = Field.reviewid
						WHERE Review.pid = %d and Review.mode = "com_content" AND Review.published = 1 AND Review.id != %d AND Field.%s > 0
					', $this->reviewPriceField, $review['Review']['listing_id'], $review['Review']['review_id'], $this->reviewPriceField);

                break;

                default:

					$averageQuery = sprintf('
						SELECT 
							AVG(%s) 
						FROM #__jreviews_comments AS Review 
						LEFT JOIN  #__jreviews_review_fields AS Field on Review.id = Field.reviewid
						WHERE Review.pid = %d and Review.mode = "com_content" AND Review.published = 1 AND Field.%s > 0
					', $this->reviewPriceField, $review['Review']['listing_id'],$this->reviewPriceField);

                break;
            }

			$averagePrice = $model->query($averageQuery,'loadResult');

			$query = sprintf('
				UPDATE 
					#__jreviews_content 
				SET %s = %s WHERE contentid = %d
			', $this->listingPriceField, $averagePrice, $review['Review']['listing_id']);

			$model->query($query);
		}		
    }
}

Now you should clear the file registry in the JReviews dashboard so it can recognize the new files, and submit your first review for testing.

Taking it a step further with the Queue Add-on

Right now the above code executes in the same process of saving, updating or deleting a review. This is fine if a listing has just a handful of reviews as the added delay shouldn't be too noticeable. However, once you have dozens of reviews per listing and thousands of reviews in the system, things may change. If that's the case then you can rely on the Queue Add-on to execute the listener code in a separate request. You need to update the above code to enable queuing for the listener:

   /**
     * Add task to the queue
     * @var bool
     */
	protected $queue = true;

The Queue Add-on allows running tasks via cron, although for large sites where the queue fills up quickly, it's recommended to setup Supervisor. Please refer to the Queue Add-on Documentation for more details.

You can use the same approach shown in this tutorial to perform other types of calculations for review custom fields.

Navigation