Sorting by Related Page Count

[reed]

Hey folks, I'm attempting to order a list of subjects based on the number of pages (not child pages) that link to each subject. The end result will look like this:

portrait — 120 items

landscape — 78 items

trees — 42 items 

beards — 8 items

etc.

I've been digging through the archive here, but I can't seem to find a solution. The issue is that I'm using wire('page')->find to get the list of subjects, and then getting the number of linked items inside the foreach — here's my code:

$subjects = wire('page')->find("template=subject, sort=title");
foreach ($subjects as $subject) {
$artwork = rendersubjectArtworkList(findartworks("subjects=$subject, limit=1"));
$count = findartworks("subjects=$subject")->count;
}

I can't figure out how to either combine my queries or re-sort the list after the call. Any ideas? 

Thanks so much for any help!

[horst]

How have you linked the pages to the subjects? WIth which field(s)? Do you use an InputfieldPage for that?

What does this function do: findartworks("subjects=$subject, limit=1") ?  You call it two times.

[kixe]

Assuming your artworks have a template named 'artworks' and a select/ multiselect field 'subjects' of type page, where you define the type (subject) you could do the following:

$subjects = $pages->find("template=subject, sort=title")->each('title'); // get the titles of subjects (pages)
foreach ($subjects as $subject)
$artworks = $pages->find("template=artworks, subjects=$subject");
 echo $subject.' — '.$artworks->count;
}

[reed]

Hi Horst! Excellent questions — 

I've got pages with two different templates: artworks, and subjects. On the artwork pages, I'm using a page field (set to allow multiple pages using AsmSelect) to link to 

subjects.

kixe — that does work, but the end result is still ordered by title, and I'm attempting to reorder the list by $artworks->count; from highest to lowest. 

I'll kick it around more today, thanks for your input!

[Macrura]

you could assign a runtime parameter to them, see this topic for an example:

https://processwire.com/talk/topic/10920-remove-duplicates-in-a-foreach-loop-but-count-them/?p=102592

notice how this assigns a count as a runtime parameter:

$i->useCount = count($pages->find("interests=$i"));

[horst]

I think this must work:

$pa = new PageArray();                                     // use a PageArray to collect all subject pages
foreach($pages->find("template=subject") as $subject) {    // iterate over all subject pages
    $subject->set('myCount', $pages->find("template=artworks, subjects=$subject")->count);   // add a temporary property to it that holds the total number of linked items
    $pa->add($subject);                                    // add it to the PageArray
}
foreach($pa->sort("-myCount") as $item) {                  // sort the PageArray descending by total number and echo the title and count
    echo "<li>{$item->title} ({$item->myCount})</li>";
}

[reed]

horst — many thanks!

This worked perfectly. It's a pretty hefty database call, so I've limited the number of subjects to 20 to keep the TTFB down. But it works! And @Macrura, thanks for your thoughts as well - I'll keep that solve in mind — 

Here's my final function:

function rendersubjectlistsort($pages) {
	$out .="<div class='listWrapper'><div class='imageList'>";

	$pa = new PageArray();
	foreach($pages->find("template=subject, limit=20") as $subject) {
	    $subject->set('myCount', findartworks("subjects=$subject")->count);   
	    $subject->set('myImg', rendersubjectArtworkList(findartworks("subjects=$subject, limit=1"))); 
	    $pa->add($subject); 
	}
	foreach($pa->sort("-myCount") as $item) {
		$out .= "<a class='listItem' href='{$item->url}'>" . 
					$item->myImg .
					"<div class='itemInfo'>" .
						"<h3>{$item->title}</h3>" . 
						"<span class='dates'>{$item->myCount} images</span>" .
						"<div style='clear:both;'></div>" .
					"</div>" .
				"</a>";

	}

	$out .="<div style='clear:both;'></div></div></div>";
	return $out; 

}

Thanks again!

[Soma]

Only you have to keep in mind that it doesn't scale if you do this for more and more pages.

Also note that if only counting a $pages->count(selector) is more efficient. Instead of $pages->find()->count

What I do on a shop category tree, is calculate it every time a product is saved and write the count to a integer field on the category pages. So it's like cached.

You could also use markup cache to cache the output and only let rebuild it when a product is changed.