Jump to content

Cycling a lot of pages


valan
 Share

Recommended Posts

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?

Link to comment
Share on other sites

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?

  • Like 3
Link to comment
Share on other sites

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)
  • Like 9
Link to comment
Share on other sites

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

  • Like 5
Link to comment
Share on other sites

$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());
  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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
  • Like 3
Link to comment
Share on other sites

@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?

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...