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

[ESRCH]

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

[kongondo]

@ESRCH...welcome to the forums.

Read you question quickly. Maybe this topic can help? https://processwire.com/talk/topic/5441-kind-of-calculated-field/ It's about creating a hidden field to store a calculated value on the fly. See posts #2 and 3

[ESRCH]

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!

[Hani]

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.

[netcarver]

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

[netcarver]

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.

[netcarver]

I've had the go-ahead from @ESRCH and have now pushed this to the module repository.

Repository Link