Cycling a lot of pages

[valan]

Normally if you'd like to sum values of some page field for required pages you do smth like this:

$mypages = wire('pages')->find($myselector);
foreach ($mypages as $mypage) $sum += $mypage->amount;

Assume you have 1000000 pages returned from selector. In this case, approach above doesn't work due to server memory limits. In my case PHP 5.5 just silently stops w/o any error notification))

How this code should be transformed in order to cycle huge number of pages and do not hit memory limit?

[adrian]

If you didn't have so many pages I would say go for something like this where you populate the pageids from your selector in the IN clause.

SELECT SUM(data) AS sum FROM `field_amount` WHERE pages_id IN ( 1234, 2412, 1234 )

But with 1 million page ids in your "IN", that's not going to work

What about this:

SELECT SUM(data) AS sum 
FROM `field_amount` 
INNER JOIN `pages` ON `field_amount`.`pages_id` = `pages`.`id`
WHERE `pages`.`templates_id` = 29

The problem with this is that you need to reproduce your initial pages selector - in my example I am just limiting to a template. Do you think you could make this work? What is your actual selector?

[Jan Romero]

If you want to avoid converting the selector to SQL you can let ProcessWire do it for you as teppo has shown in this post:

$selectors = new Selectors("template=blog-post, limit=1000000");
$pagefinder = new PageFinder();
$sql = $pagefinder->getQuery($selectors->getArray(), array())->getQuery();

Then you can modify that or wrap your own select around it:

select sum(data) as sum_amount
from field_amount
where pages_id in ($sql)

[adrian]

Nice one Jan Romero - I hadn't seen the use of the Selectors class and PageFinder like that.

Putting it all together:

$selectors = new Selectors("template=basic-page");
$pagefinder = new PageFinder();
$sql = $pagefinder->getQuery($selectors->getArray(), array("returnVerbose" => false))->getQuery();
$result = $database->query("SELECT SUM(data) AS sum_amount FROM field_amount WHERE pages_id IN ($sql)");
$sum = $result->fetch(PDO::FETCH_COLUMN);

echo $sum;

Note that I had an SQL error regarding "SQL_CALC_FOUND_ROWS" when I included a limit in the selector. If I removed the SQL_CALC_FOUND_ROWS then it complained of not allowing limit in IN. Remove the limit from the selector and things work just fine.

Also, note that you need to specify "returnVerbose" => false - otherwise you get a php NOTICE: https://github.com/ryancramerdesign/ProcessWire/issues/1734

[WillyC]

$sum=0;

$start=0;

$limit=1000;

do{

  $mypages=wire('pages')->find("start=$start, limit=$limit, $myselector");

  print "processng pages.frm $start<BR>";

  foreach ($mypages as $mypage) $sum += $mypage->amount;

  wire('pages')->uncacheAll();

  $start+=$limit;

}while($mypages->count());

[LostKobrakai]

@WillyC

Why would one shovel all the data from mysql into the memory if the db can calculate the sum on it's own? And it's probably faster, because it's querying pages only once.

[WillyC]

lost koberoaki bcoz he ask how.to do w/pw

i.ansor more for "cylng lots pages" then "sum lotso pages"

uses.either for sum

butt uses my codes if u.might needs more todo

[valan]

Wasn't aware of uncacheAll() - sounds as what's needed if you cycle a lot of pages and do smth with page fields (e.g. modifying/saving/etc).

Also never used PageFinder + direct SQL call. It's probably better option if you need to perform simple get operation (like sum).

Good post - will be useful for all who develop sites with a lot of pages.

Does anybody know if it is possible to use PageActions introduced with ProcessLister - in background (w/o frontend)? It works perfectly in frontend and I use it for mass changes, but don't know if the same can be done w/o frontend.

[LostKobrakai]

PageActions can easily be called from the api and with the IftRunner you can even queue them for later (or external) processing.

wire('modules')->get("SendEmail") // Get PageActionModule
  ->set("email", "registration")  // Set config values (if needed)
  ->action($page);                // Run action on $page

[valan]

@LostKobrakai - what if you need to cycle a lot of pages and run PageAction inside cycle? Should @WillyC approach be used or there exists some special method for this?

[Soma]

PageActions are run either in one step or if more than hundreds in batches. You could batch millions of pages without running into timeout or memory issues.

[valan]

@Some - action($item) method gets only one item as an argument. There is executeMultiple($items) but populating $items beforehand may lead to memory issues.

Is there some PW core $class->method($selector) that can get items selector as argument and run PageAction in batches? Or, how to wrap @LostKobrakai code in a cycle that handles items in batches? There is "custom" solution, proposed by @WillyC. Is there another solution that already exists in PW core/how code looks like?

[Soma]

PW runs the actions not the PageAction module. It sends each item to the action(). 

[Martijn Geerts]

Batches take up memory to build, saving over 200.000 pages will build up so many batches that it will fail. Thats my experience i had half a year ago.

[LostKobrakai]

You'd probably mix WillyC's code with the option to run the PageAction on a batch of pages if you want to circumvent memory issues.