Group By

[briangroce]

Hi there,

I am trying to get a group of pages from Processwire, but I want to group all of the similar pages and show a count next to them. Is there something I can do in Processwire to achieve this?

I know I can get all of the pages and sort them like this and I can add ->count() to count them, but I want to achieve something like the SQL command GROUP_BY

$pages->find("template=template, sort=sort")

Thank you for any help here...

[netcarver]

Hi Brian,

I can't give an authoritative answer on an equivalent to GROUP_BY in the API but can I ask In what ways these pages are 'similar'? It looks like it might be by template based on the code you posted but is there some other similarity you want to use in the grouping?

If it is by template and you either have a limited number of templates or a fairly limited total page count then you could iterate over the result of the find() call. Something like this...

$groups = array();
foreach( $pages->find("template=templateA|templateB|templateC") as $p ) ) {
   $groups[$p->template->name][] = $p;
}

if(!empty($groups)) {
   foreach($groups as $template => $group) {
       $count = count($group);
       echo "Pages using $template: $count\n"; // This is plaintext output, add HTML if needed
   }
}

Caveat: totally untested. YMMV.

[briangroce]

netcarver,

Thank you for your help with this. I can see how this would work. I had not thought about doing it that way. It seems that would be quite a process for the server. What I have is about 2,000 pages all under the same template, but some of the pages have a field that will have the same value (maybe like 60 different values total).

Do you think it would work OK to loop through all 2,000 pages and assign a group in this way?

[nik]

Hi Brian,

While that could work as is, you might also face memory issues by looping through those 2000 pages at once. This other recent thread may be helpful to you as well:

[netcarver]

Hello again Brian,

...What I have is about 2,000 pages all under the same template, but some of the pages have a field that will have the same value (maybe like 60 different values total)...

Doesn't really fit with the

...fairly limited total page count...

point so if you did go down the route of iterating over the results yourself, you'd probably have to take nibbles at it as Nik is suggesting.

However, given your extra information I'd try calling the DB directly. Just querying the table representing that one field should be all you need. If the field were named, say, "design" then this might work for you...

// Pull designs from DB grouped by design...
$result = wire()->db->query("SELECT *, COUNT(`data`) AS `num` FROM `field_design` GROUP BY `data` ORDER BY `num` DESC");

// Following loop should be fine for the server with ~60 iterations. If not, you might try caching the generated output.
while($r = $result->fetch_assoc()) {
   $num = $r['num'];
   $d = $r['data'];
   echo "$num pages using design $d\n"; // This is plaintext output, use HTML if needed

   // If you wanted to pull a feature from the page SQL chooses to group them on you could do it something like this...
   $page_name = wire()->pages->get($r['pages_id'])->name;
}

Again, totally untested. YMMV.

[SiNNuT]

The problem i see with your query is that it does and can not make a distinction between templates, and a field can belong to multiple templates. If that's the case your count is gonna be wrong for what Brian wants to do; count pages using templateX grouped by a fields 'unique' values.

Seeing that the only foreign key in a field table is 'pages_id' i don't see a simple way of doing a bulletproof query using only the field table.

However, given your extra information I'd try calling the DB directly. Just querying the table representing that one field should be all you need. If the field were named, say, "design" then this might work for you...

- query code here -

[netcarver]

Hi SiNNuT, yes that's true, I was assuming the field is only used in one template.

[Soma]

netcarver,

Thank you for your help with this. I can see how this would work. I had not thought about doing it that way. It seems that would be quite a process for the server. What I have is about 2,000 pages all under the same template, but some of the pages have a field that will have the same value (maybe like 60 different values total).

What field is the field you want to check the same value? If it would be a page field it would be easy with find selectors.

[Soma]

Based on netcarvers first script I tried this on my products (550) and grouping them by price field.

$pa = $pages->find("template=product,sort=sort");
$groups = array();
foreach($pa as $p) $groups["$p->sc_price"][] = $p->id;

echo "<ul>";
foreach($groups as $key => $gr){
   $count = count($gr);
   echo "<li>$key ($count)";
   if($count){
       echo "<ul>";
       foreach($gr as $key => $pid){
           $r = $pages->get($pid);
           echo "<li><a href='$r->url'>$r->title</a></li>";
       }
       echo "</ul>";
   }
   echo "</li>";
}
echo "</ul>";

echo "<br/>memory:".memory_get_peak_usage()/1024/1024;

This doesn't add much memory in my case, 1MB more and maybe takes +~1sec but haven't looked at execution times. So 2000 pages will maybe be around +2-3MB and 2-3 seconds. I can run this script along with a ton other heavy scripts easily with 16M php memory limit.

Further if you use PW's markup cache to only generate the list once every hour you would save some resources.

Try it and see what you get. If memory would be a problem you might still consider constructing 1 or 2 sql queries and see if it performs better. This surely would involve some complex sql joins over fields, fieldgroup and page tables and make sure pages are published etc.

[ryan]

Unless you are doing some one-time import/export, you usually want to avoid actions that require loading huge amounts of pages in one request. This is a non-sustainable way of building a web site in any platform, because eventually you will run out of memory. If you are making a small web site, then you don't need to worry about it. But if you are making something that will grow big, you need to place limits on API calls that could might lots of pages (as in, use the "limit=n" in your selectors). Going directly to MySQL is certainly a fine way to go if you need to perform some query that can't easily be performed in the API. Though admittedly, I almost never need to do this, but there's no harm in doing it. One other thing I want to mention is the $pages->count() function, which works exactly like $pages->find() but instead returns a count of the matching pages. It does this without actually loading the pages it's counting, so may be applicable here.

[briangroce]

WOW. You guys are awesome. I used net carvers solution of just querying the db directly. It works great.