Jump to content
ESRCH

Creating a fast "pages->sum()" function

Recommended Posts

Hi,

I am developing a site where I need to sum a specific field value across a large number of pages selected with a selector string. For example, I am keeping track of the stock of specific products. The stock is influenced by various stock movements. I have a template for stock movements, with fields "product" (Page type) and "quantity" (Integer type).

Currently, I am searching for the pages and adding the quantities in two different steps:

// For a given $product
$movements = $pages->find("template=stock-movement, product=$product, quantity!=0");
$stock = 0;
foreach ($movements as $movement) {
  $stock += $movement->quantity;
}

This is fine, but I am doing this very often, and I would like to improve the performance of this calculation, since the quantity of stock movements can grow very large. To do this, I was thinking of using the SUM function offered by MySQL to achieve similar optimizations as what is achieved by the $pages->count() function. Ideally, this would translate into a $pages->sum() function that could be called as follows:

// For a given $product
$stock = $pages->sum("template=stock-movement, product=$product", "quantity");

I have tried looking through the code of the Pages, PageFinder and DatabaseQuerySelect classes in the core to get some inspiration, but I fear that I don't understand well enough how the query mechanism works to be able to adapt it to create the function mentioned above. Could anyone help me with this?

Thanks a lot in advance,

Eric

  • Like 2

Share this post


Link to post
Share on other sites

Hi kongondo,

Thanks for the quick reply and the welcome.

I had actually already read the topic that you mention, and this is what prompted my need: I would like to save a "stock" field for each product, but this field would have to be calculated each time a stock movement is saved, and calculating this field is precisely what takes so much time.

In the meantime, I continued looking into the problem, and I think that I came up with a solution with the following module:

class PagesSum extends WireData implements Module {

	public static function getModuleInfo() {

		return array(
			'title' => 'Pages Sum', 
			'version' => 1, 
			'summary' => 'Adds a $pages->sum($selectorString, $fieldName) function to sum the value of a specific field over a list of pages selected by a selector string.',
			'singular' => true, 
			'autoload' => true
			);
	}

	public function init() {
		$this->addHook('Pages::sum', $this, 'sum'); 
	}

	public function sum($event) {
		$selectorString = $event->arguments(0);
		$fieldName = $event->arguments(1);

		// Find all the pages associated with the selector string
		$pageFinder = new PageFinder();
		$idQuery = $pageFinder->findIDs(new Selectors($selectorString), array('returnQuery' => true));
		$idQuery->set('orderby', array()); 
		$idQuery->set('groupby', array()); 
		$idQuery->set('limit', array());
		$stmt = $idQuery->execute();
		$idArray = $stmt->fetchAll(PDO::FETCH_COLUMN);
		// If no pages were found, return 0
		if (count($idArray) == 0) {
			$event->return = 0;
			return;
		}
		$idString = implode(',', $idArray);

		// Get the table name for the given field name
		$field = $this->fields->get($fieldName);
		// If no field with this name is found, return 0;
		if (!$field) {
			$event->return = 0;
			return;
		}
		$tableName = $field->getTable();

		// Run the SUM query
		$sumQuery = new DatabaseQuerySelect();
		$sumQuery->select("SUM(data)");
		$sumQuery->from($tableName);
		$sumQuery->where("pages_id IN ($idString)");
		$stmt2 = $sumQuery->execute();
		list($total) = $stmt2->fetch(PDO::FETCH_NUM);

		$event->return = $total;
	}	
}

In my tests, it is about 60 times quicker to calculate the sum with the function $pages->sum($selectorString, $fieldName) than looping over the pages (for about 12000 pages).

I would appreciate any feedback, in case there are any other optimizations to be achieved, or any errors I haven't thought about. And I hope that this might maybe be useful to others too!

  • Like 18

Share this post


Link to post
Share on other sites

Thanks for taking the time to create this, ESRCH! You should throw this on GitHub and add then add it to the modules directory.  I'll let you know if I experience any problems with it.

  • Like 1

Share this post


Link to post
Share on other sites

I've created a git repository for this useful little module here. Fully attributed (in the module) to ESRCH.

  • Like 7

Share this post


Link to post
Share on other sites

Forgot to mention: unless I hear otherwise from ESRCH (I've messaged) I plan on pushing this to the module repository as it's so useful, it needs to be in there from someone. Anyone.

  • Like 6

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...