Sorting based on Repeater Field Values?

[Arcturus]

Is it possible to generate a page list based on sorted values from a repeater field?

I'm working on an outdoors/travel-related website (and my first using PW) where pages using a "report" template have a repeater field called "mountains" containing metadata such as elevations and other statistics. Given that these reports can profile up to four mountains within a single report, the repeater field type seems to work perfectly for this purpose.

The problem for me is that I'm trying to generate "Top X"-type lists on the parent page based on this metadata and, after scouring both this forum and the available documentation for hours, I haven't had any luck in this endeavour. The only reference to sorting a repeater I could find was for sorting a single instance of a repeater from within its originating page. As suggested in the documentation, I tried activating "auto-joining" on the repeater and some of its sub-fields with no effect.

The following should provide a bit more context:

The following code works to generate an unsorted list... and can be made sortable from the first line by every available field (it seems) except for those in my repeater. >_<

    <h3>By Maximum Elevation</h3>
    <?php
		$peaklist = $pages->find("template=report");
		echo '<ul class="blocklist">';
		foreach($peaklist as $location) {
			$mountain = $pages->get("id=$location");
			foreach($mountain->mountains as $peak){
				if ($peak->stats_mtnName){
					$maxElevft = number_format(round($peak->stats_maxElev * 3.28084));
					echo '<li><p class="tabular"><a href="'.$mountain->url.'">'.$peak->stats_mtnName.'   '.number_format($peak->stats_maxElev).'m / '.$maxElevft.' ft.</p></li>';
				}
			}
		}
		echo '</ul>';
	?>

Any ideas? I'd really prefer to avoid having to use page fields if at all possible.

[MadeMyDay]

foreach($mountain->->mountains->sort('maxElev') as $peak){

should do it.

[Arcturus]

Thanks MadeMyDay. I tweaked the above to this...

foreach($mountain->mountains->sort('stats_maxElev') as $peak){

and it worked for single instances of the repeater (one page with three mountains sorted itself correctly), but not across multiple pages.

[MadeMyDay]

I see. So try:

$peaklist = $pages->find("template=report,sort=mountains.stats_maxElev");

[Arcturus]

That generates the following error:

Error    Exception: Unknown column '_sort_mountains_stats_maxElev. stats_maxElev' in 'order clause' (in F:\GiantsGate\ProcessWire\wire\core\Database.php line 118)

[MadeMyDay]

Haven't tried it yet, but according to the API this should work. I wonder where this _sort_mountains-thingie comes from... Could you provide your complete code? Perhaps Ryan jumps in and explains it ;-)

[MadeMyDay]

What about

$peaklist = $pages->find("template=report")->sort("mountains.stats_maxElev");

[Arcturus]

The above code (from #7) produced this error:

Error    Exception: Method RepeaterPageArray::getUnformatted does not exist or is not callable in this context (in F:\GiantsGate\ProcessWire\wire\core\Wire.php line 232)

Here's the last functioning code mentioned in #3:

 <?php
	$peaklist = $pages->find("template=report");
	echo '<ul class="blocklist">';
	foreach($peaklist as $location) {
		$mountain = $pages->get("id=$location");
		foreach($mountain->mountains->sort('stats_maxElev') as $peak){
			if ($peak->stats_mtnName){
				$maxElevft = number_format(round($peak->stats_maxElev * 3.28084));
				echo '<li><p class="tabular"><a href="'.$mountain->url.'">'.$peak->stats_mtnName.'   '.number_format($peak->stats_maxElev).'m / '.$maxElevft.' ft.</p></li>';
			}
		}
	}
	echo '</ul>';
?>

I think that part of the problem is that I can't read any values from a repeater until that second loop. I've come up with a couple of new ideas on how to approach this issue and will see where I get.

[Arcturus]

Eureka!

It would be nice if there was a more direct way to do this using PW, and perhaps there is, but I eventually succeeded by dumping the repeater fields I needed into a separate array and then performing a sort on that array. Here's the code with some additional commenting:

<?php
	$peaklist = $pages->find("template=report"); // returns relevant page IDs
	$dest = array();

	foreach($peaklist as $location) {
		$mountain = $pages->get("id=$location"); // grabs pages to allow access to fields
	
		foreach($mountain->mountains as $peak){
			if ($peak->stats_mtnName){ // pages create 4 blank repeater "value sets" by default, this is a test to grab only populated ones
				$maxElevft = number_format(round($peak->stats_maxElev * 3.28084)); // to convert a field value in metres to feet; using number_format() to add thousands separators (ie. 3200 -> 3,200)

				++$i;					
				$dest[$i]->url = $mountain->url; // field from page
				$dest[$i]->name = $peak->stats_mtnName; // field from repeater 
				$dest[$i]->maxElevM = $peak->stats_maxElev; // field from repeater 
				$dest[$i]->maxElevF = $maxElevft;
			}
		}
	}

	usort($dest, function($a, $b) {
		return $b->maxElevM - $a->maxElevM; // sorts in descending order, reverse A & B for ascending order
	});

	echo '<ul class="blocklist">';
	foreach($dest as $item) {
		echo '<li><p class="tabular"><a href="'.$item->url.'">'.$item->name.'   '.number_format($item->maxElevM).'m / '.$item->maxElevF.' ft.</a></p></li>';
	}
	echo '</ul>';
?>

[Arcturus]

One additional note is that "usort" seems to have some issues with decimal values. One of my lists is distance related and allows a single decimal place for values such as "10.7". The output I was getting was about 95% correct with perplexing errors that seemed to have no particular pattern. After a bunch of experiments I found that using a simple multiplication to eliminate the decimal value for the comparison function resolved the problem.

usort($dest, function($a, $b) {
	$a = $a->distanceKm * 10; // example: 10.7 becomes 107
	$b = $b->distanceKm * 10;
	return $b - $a;
});

[Pete]

Just shortening your earlier code:

<?php
	$dest = array();

	foreach($pages->find("template=report") as $location) {
		foreach($location->mountains as $peak){
			if ($peak->stats_mtnName){ // pages create 4 blank repeater "value sets" by default, this is a test to grab only populated ones
				$maxElevft = number_format(round($peak->stats_maxElev * 3.28084)); // to convert a field value in metres to feet; using number_format() to add thousands separators (ie. 3200 -> 3,200)

				++$i;					
				$dest[$i]->url = $mountain->url; // field from page
				$dest[$i]->name = $peak->stats_mtnName; // field from repeater 
				$dest[$i]->maxElevM = $peak->stats_maxElev; // field from repeater 
				$dest[$i]->maxElevF = $maxElevft;
			}
		}
	}

	usort($dest, function($a, $b) {
		return $b->maxElevM - $a->maxElevM; // sorts in descending order, reverse A & B for ascending order
	});

	echo '<ul class="blocklist">';
	foreach($dest as $item) {
		echo '<li><p class="tabular"><a href="'.$item->url.'">'.$item->name.'   '.number_format($item->maxElevM).'m / '.$item->maxElevF.' ft.</a></p></li>';
	}
	echo '</ul>';
?>

I think there are some other savings to be made, but if you use something like WinMerge to compare your code against the above it will show you some useful tips

[Pete]

In fact, this may streamline it further:

<?php
        echo '<ul class="blocklist">'; // Start the list here as I don't want to loop through things twice

	foreach($pages->find("template=report") as $location) {
		foreach($location->mountains->sort('-stats_maxElev') as $peak){ // we prefix the field name with a minus sign to sort descending rather than ascending
			if ($peak->stats_mtnName){ // pages create 4 blank repeater "value sets" by default, this is a test to grab only populated ones
				$maxElevft = number_format(round($peak->stats_maxElev * 3.28084)); // to convert a field value in metres to feet; using number_format() to add thousands separators (ie. 3200 -> 3,200)

				echo '<li><p class="tabular"><a href="'.$peak->url.'">'.$peak->stats_mtnName.'   '.number_format($peak->stats_maxElev).'m / '.$maxElevft.' ft.</a></p></li>';
			}
		}
	}

	echo '</ul>';
?>

But without a copy of the structure and data I suspect it might fall foul of repeater issues

[Soma]

Trying since a long time to understand the issue and what structure you're using. It seems a little strange you're using repeater to add mountains to reports, so if there's mountains multiple times in yours routes you enter the same information again and again. I tend to see this data as page references where you have mountains and route pages and add them. This might also would lead to a simpler solution to your problem at the end, not sure. Also you'll be able to add more fields and images, description and meta data on those mountains to maybe create a nice detail info for each mountain. Use page fields to simply select mountains you manage separate on their own page. 

(1 hour later... some coffee and some smoke...)

I just made a little test and with page field you can do this to get you're top x list.

$routes = $pages->find("template=route")->sort("-select_mountain.max_height");

foreach($routes as $route){
    echo "<p>$route->title<br/>";
    $peak = $route->select_mountain->sort("-max_height")->first(); // get heighest mountain
    echo $peak->title . " / " . $peak->max_height;
}

Route2 Mount Everest / 8900
Route3 K18 / 7890
Route1 MOunt Doom / 6132

And the structure is like this.

Just examples

Also something just noticed in your code:

Anyway. In some of your earlier on page level code you use $pages->get("id=$location"); inside foreach to get the page, but you already got the page.... $location is the mountain page.

foreach($peaklist as $location) {
        $mountain = $pages->get("id=$location"); // not needed!
        foreach($location->mountains->sort('stats_maxElev') as $peak){
 

[Soma]

Ok back to the code pete also thrown around, I wanted to take part in the competition and provide a much cleaner solution to do this if you really want to go with this:

// create new empty page array for storing pages
$dest = new PageArray();

foreach($pages->find("template=report") as $location) {
    $max = $location->mountains->sort('-stats_maxElev')->first()->stats_maxElev;
    $location->max = $max; // save value to page temporarly for sorting later
    $dest->add($location); // add location to page array
}

echo '<ul class="blocklist">';

// sort by temporary field and output destinations
foreach($dest->sort('-max') as $item) {
    // sort and get mountain with biggest stats_maxElev
    $mo = $item->mountains->sort("-stats_maxElev")->first();
    echo "<li><p class='tabular'>";
    $ft = number_format( round($mo->stats_maxElev * 3.28084) );  
    $mt = number_format( $mo->maxElevM );
    echo "<a href='$item->url'>$mo->name - $mt m / $ft ft</a>";
    echo "</p></li>";
}

echo '</ul>'; 

It's still a little akward and not for large large scale as it needs to loop all to get max heights. Only some custom sql query could be taken here to to get page id and use limit. It would be so nice to do it with the code I wrote previous post with using page field, but it doesn't work as of the nature of repeaters.

[Pete]

@soma I was going to go the PageArray route for one suggestion but then thought it might not even be needed and ended up with my last example. Hard to tell without seeing the structure as I'm sure you'll agree

[Soma]

@pete I see but your last example isnt doing the same as previous. I see the structure in his screens but wasn't sure first what the desired result really is. Though I think his last code stated what I was thinking.

[Arcturus]

Thanks for the optimizations Pete!

One thing though, similar to MadeMyDay's suggestions your sort only works on individual pages. So if page A has one item, page B three items, and C one item... the three items on B will sort correctly amongst themselves, but the overall list just follows the default page sequence. This is what lead me to the workaround of dumping everything out to an array before performing the sort. Nonetheless, I was still able to cut out a number of lines of code.

Soma, if the site were the type where dozens of people would be submitting their experiences about a particular destination, then for sure the pagearray would make sense. But the trips are unique and the mountains are only referenced from their report page and, if the stats are remarkable in some fashion, from the parent page in one of these generated lists. Because of this, the repeater gives me what I need while saving a TON of work on the administrative side. I'm using pagearrays for my activity and geographic region related metadata because they're reused frequently and have allowed me to crank out "category" type pages in mere minutes (very nice!).

However, thanks for raising the point about redundancy. On reflection, I have no need for the time and distance in the repeater as those fields should be entered once as part of the report template. I didn't get a chance to work through and test your code examples, but will do so when I get a chance tomorrow. Thanks for the help!

[Soma]

You mean page field not pagearray. The page array in my last example is instead of your array youre filling which isnt really nice and not needed. And the page array is handy to then sort it later. So I also dont understand petes thoughts as the page array has nothing to do with the structure.

[Arcturus]

Soma, your code works but only collects the tallest peak from each report while ignoring others that could still make a "top" list. Seems everyone's running into the same problem on how to access and sort all of the repeater field values at once. Dumping to an array may not be pretty or super efficient, but it seems to be one way to overcome the issues presented by the nested data.

And you were right, I was mixing up page fields with pagearrays earlier.

[nik]

I'm going to jump in, hopefully not just to mess things up .

One dirty way to achieve what you're trying to do would be going straight to the repeater items. This gives you the top 5 highest mountains across all reports:

$topFiveMaxElevations = $pages->find("template=repeater_mountains, sort=-stats_maxElev");

Technically repeater items are pages and thus you can use find() as you'd do for normal pages. But as I said, I'd consider this a dirty way because it relies on the internals of the repeater fieldtype. It does get you there but at what cost, that I don't really know. And if you'd need any data from the parent page (the report itself), you'd need to get it via getForPage() which returns the page a repeater item belongs to.

And like Soma pointed out, there's some redundancy. That could affect the top 5 lists the very moment some mountain ends up in two different reports (unless it's absolutely impossible scenario?). No matter which method you use to collect for example the altitude data, you'd probably need some check to filter out duplicates. And that would make things a bit more difficult as you wouldn't be able to limit to 5 results right away. Oh well, just wanted to mention it.

One additional note is that "usort" seems to have some issues with decimal values. One of my lists is distance related and allows a single decimal place for values such as "10.7". The output I was getting was about 95% correct with perplexing errors that seemed to have no particular pattern. After a bunch of experiments I found that using a simple multiplication to eliminate the decimal value for the comparison function resolved the problem.

Actually that's what the manual says it will do . See http://php.net/manual/en/function.usort.php where it says "The comparison function must return an integer less than, equal to, or greater than zero" with an additional caution on using for example floats. Your solution is fine though, now you've got it returning integers.

Hope I was being helpful, rather that confusing. Time to get some sleep now.

Edited March 2, 2013 by nik

[Soma]

Ah then I didnt really get what the desired result was. Nevermind. I think nik is spot on here. You can easily get top of all repeaters and if you add limit to the selector it will be good. Yeah the doublicates youd have to work out if there could be any at all.

[ryan]

Technically repeater items are pages and thus you can use find() as you'd do for normal pages. But as I said, I'd consider this a dirty way because it relies on the internals of the repeater fieldtype.

Actually I think you've got a fine solution here. Part of the reason that repeaters are built on top of the pages system is so that they could be accessible in a manner like this. So while it is relying on internals of the repeater fieldtype, these particular internals are meant to be exposed when you need them. Repeaters are pages so that they can benefit from that accessibility. The only thing that might need to be added to the selector is an "include=all" or better yet, "check_access=0", since those repeater items are technically access protected. 

[Ovi]

Hi guys, i came across a similar problem, and i just wanted to add my solution here since it doesn't use usort.

It uses the Quicksort algorithm which is a lot faster, and seeing as PW is built to be lightning fast and scale very well, i think speed is an important factor here. 

My (supposedly faster) solution here:  http://processwire.com/talk/topic/3788-sorting-pages-by-repeater-fields-first-entry/?p=37032

[nik]

@Ovi, I have to take a closer look to your solution, it sure sounds interesting. PW uses usort for in-memory sorting because it's stable (=doesn't change order of equal items relative to each other) while quicksort isn't. But it seems I've got to dig a bit deeper on this sometimes (and make some speed comparisons) to see if PW could benefit from a stable variation of quicksort - thanks for sharing your findings on that.

Then the actual point I was supposed to make: the solution in my previous post (last one on first page) doesn't use usort at all but leaves the sorting to MySQL. And that's the preferred way both for speed and memory consumption. But as it didn't fit your needs (the thread you linked to) the solution you came up with seems like a very good one too.

[Ovi]

Thanks for looking into this Nik!

It'm curious if usort is faster in PW. See this link for a performance test done by someone on StackOverflow http://stackoverflow.com/questions/1462503/sort-array-by-object-property-in-php. They're saying:

I tried a usort, and sorted 15000 Person objects in around 1.8 seconds.

As you are concerned about the inefficiency of the calls to the comparison function, I compared it with a non-recursive Quicksort implementation. This actually ran in around one third of the time, approx 0.5 seconds.

Of course this may not be the case at all for PW.