Sorting pages by repeater field's first entry

[Ovi]

Hi everyone,

My current conundrum is this: i have a list of products for a site i'm working on. They all have the same template and live in the same folder.

The products have variations based on size, and each variation has it's own price.

I want to be able to sort products by their lowest price (out of any variation), ascending and descending.

i've tried something like: 

$products = $pages->find("template=product, [more criteria here] ,sort=-product_versions[0]->price");

where product_versions is the name of the repeater and "price" is obviously the name of the repeater's field that contains the price i want to sort by. This apparently is not allowed:

Error: Exception: Unknown Selector operator: '' -- was your selector value properly escaped? (in /home/trb/public_html/wire/core/Selectors.php line 165)

How can i do this? I've seen existing posts on repeaters and sorting on the forums, but none that addressed this issue. And i think it's a pretty vital one.

Thanks!

EDIT: I tried the following with no success (just for the hell of it, to see what it would do): 

$products = $pages->find("template=product, [more criteria here] ,sort=-product_versions");

definitely not what i need (does sort the products in some weird way though) 

[kongondo]

Try this (subfield property)

$products = $pages->find("template=product, [more criteria here] ,sort=-product_versions.price");

See these examples from http://processwire.com/api/fieldtypes/repeaters/. buildings is the name of the repeater. feet_high, num_floors and year_built are the fields. 

// find all pages that have at least one building with a height > 500 feet
$buildings = $pages->find("buildings.height>500"); 

// find all pages that have a building made in 1940 with at least 20 floors
$buildings = $pages->find("buildings.year_built=1940, buildings.num_floors>20"); 

// find all pages using the basic-page template with at least 1 building
$buildings = $pages->find("template=basic-page, buildings.count>0") 

Other info (selectors)

Subfield selectors
Some field types support selection by subfield, where a subfield holds a value independent of the field's regular value. This is only used with field types that contain more complex data types. Examples include Repeaters, Files, Images, Comments and Map Markers, among others. Usage in a selector is quite simple and very similar to the count selectors, mentioned in the previous section. Other than the specification of the ".subfield", the selector is no different from any other. You may use any operators or selector features that you would use elsewhere. The format goes like this: etc......

field.subfield=value

[Ovi]

Thanks for your detailed response, I was aware of most of that, but the thing is, i need to sort by a specific entry in the repeater field (namely the first one - to keep it simple), not filter the set. This 

$products = $pages->find("template=product, [more criteria here] ,sort=-product_versions[0].price");

doesn't work.  

I would need something like:

$price = $product->product_versions->first()->price;

but usable in a selector.

Also, when trying the line below, just to see what it does (since it couldn't possibly work since it doesn't know which product version to pick):

$products = $pages->find("template=product, [more criteria here] ,sort=-product_versions.price");

i get the error:

Error: Exception: Unknown column '_sort_product_versions_price.price' in 'order clause' (in /home/trb/public_html/wire/core/Database.php line 118)

"_sort_product_versions_price.price"? Really? I don't get it, sorry...

Thanks for your help so far! 

[nik]

@Ovi, I think this could be of help to you: http://processwire.com/talk/topic/2884-sorting-based-on-repeater-field-values/

[Ovi]

Thanks Nik, but i came up with my own solution meanwhile. The good part is that i don't user uSort i use an implementation of the QuickSort algorithm, which should be 3 times faster than uSort.
 
Useful Links:

• explanation of Quicksort algorithm: http://en.wikipedia.org/wiki/Quicksort
• php implementation of Quicksort by Iskren Stoianov: http://istoyanov.blogspot.ro/2007/11/quicksort.html

My setup:

• i have an online store with products. all products have the same template
• products have price variations depending on quantity (small box of product vs big box of the same product) - it is not a matter of shipping more of the same product, the diferent sizes come from the  manufacturer (cosmetic products)
• product variations are stored in a repeater called "product_variations" which has the fields "size" and "price"
• categories and subcategories share the same template. The difference is that categories have sub-categories as children.

The challenge:

• sorting the products by price.
• Since there can be multiple prices for a product (one for each variation) i want to sort the products by the FIRST variation's price.

The solution:

function quickSortProducts( &$array, $sort_order="ASC" ){

	if ($sort_order!= "ASC" && $sort_order != "DESC") {
		trigger_error("Invalid paramenter ".$sort_order." . Expecting ASC or DESC", E_USER_ERROR);
	}

	$cur = 1;
	$stack[1]['l'] = 0;
	$stack[1]['r'] = count($array)-1;

	while( $cur != 0 ){
		$l = $stack[$cur]['l'];
		$r = $stack[$cur]['r'];
		$cur--;

		while( $l < $r ){
			$i = $l;
			$j = $r;
			$tmp = $array[(int)( ($l+$r)/2 )];

			// partion the array in two parts.
			// left from $tmp are with smaller values,
			// right from $tmp are with bigger ones
			while( $i <= $j ){
				if ($sort_order == "DESC") {
					while( $array[$i]->product_versions[0]->price > $tmp->product_versions[0]->price ) $i++;
					while( $tmp->product_versions[0]->price > $array[$j]->product_versions[0]->price ) $j--;
				}else{
					while( $array[$i]->product_versions[0]->price < $tmp->product_versions[0]->price ) $i++;
					while( $tmp->product_versions[0]->price < $array[$j]->product_versions[0]->price ) $j--;
				}
				

				// swap elements from the two sides
				if( $i <= $j){
					$w = $array[$i];
					$array[$i] = $array[$j];
					$array[$j] = $w;

					$i++;
					$j--;
				}// end if 
			};// end while ( $i <= $j )

			if( $i < $r ){
				$cur++;
				$stack[$cur]['l'] = $i;
				$stack[$cur]['r'] = $r;
			}
			$r = $j;
		}; // end while ( $l < $r )
	}// end while( $cur != 0 );
}// end function quickSortProducts 

This function does all the sorting. Next i put together my query. Since categories and sub-categories share the same template, i need to figure out what the scope of the query is and i do that below. If you don't have the same setup just do whatever query you need to. This part has nothing to do with the sorting, just putting it here for completeness:

// set the scope of the query
if ($page == $page->rootParent) {
// this is a category, we need the categories from all it's children subcategories
	$categoryScope = $page->rootParent->category_field_name;
}else{
	// this is a sub-category, we only need the products from itself
	$categoryScope = $page->rootParent->category_field_name;
}
 
// do the PW query
$products = $pages->find("template=product, ".$categoryScope."=$page->children");

And finally, after we've done the query and we have our collection of product pages, we sort it by calling the custom function:

if ($input->urlSegment1 == "price-ascending") {
	quickSortProducts( $products, "ASC")
}elseif($input->urlSegment1 == "price-descending"){
	quickSortProducts( $products, "DESC")
}

[Soma]

What I do in a similar situation is save the value ob page save to a field outside repeater. Makes for easy and fast searching with limits and pagination.

[nik]

@Soma: Agreed. I guess that's the best alternative in most scenarios - a little redundancy can make a big difference in speed and memory consumption when used wisely. There are situations when this trick isn't that good but this isn't one of them as there's only a little amount of data that would be redundant.

Ovi, drop us a line if you're interested in trying out yet another approach and need a helping hand.

[kongondo]

Ovi,

Thanks for sharing this. Lines 57 and 60 in your code are identical. Is that meant to be the case?

[Ovi]

@Soma & Nik

Thanks for the offer, i could always use any help i can get! What you're saying is more like what it first thought about when i discussed this briefly with Antti Peisa, however it seemed to me that it would be too difficult to code (i wish i knew Module Development but i don't - and this seems a lot like a custom module). I think it should probably be something similar to: http://modules.processwire.com/modules/fieldtype-concat/.

Btw if you have any tips on where to start learning module development i'd be grateful -  besides "look at the hello world module" and "see how other modules are done" , that doesn't help me unfortunately. I can write PHP up to and including creating and using my own classes, and extending existing ones - but i'm a beginner and  can't figure out how i should work with a huge thing like PW or make sense of existing modules.

@kongondo

Thanks for spotting that! i jumbled things up when i put together the code for the forums. Lines 54 to 54 should be changed from:

// set the scope of the query
if ($page == $page->rootParent) {
// this is a category, we need the categories from all it's children subcategories
	$categoryScope = $page->rootParent->category_field_name;
}else{
	// this is a sub-category, we only need the products from itself
	$categoryScope = $page->rootParent->category_field_name;
}
 
// do the PW query
$products = $pages->find("template=product, ".$categoryScope."=$page->children");

to: 

// set the scope of the query
	if ($page == $page->rootParent) {
		// this is a category, we need the categories from all it's children subcategories
		$categoryScope = "=$page->children";
		
	}else{
		// this is a sub-category, we only need the products from itself
		$categoryScope = "=$page";
	}
	// do the PW query
	$products = $pages->find("template=product, ".$page->rootParent->category_field_name.$categoryScope);

I already changed that in my original post.

Btw if you're interested: category_field_name is a hidden text field i created and attached to the category-page template and it stores the name of a Page Field which references all the subcategories of that category. It's part of the structure system i came up with to be able to have products in multiple categories and sub-categories without creating duplicate URLs, but still maintaining a sensible structure in the backend. Let me know if you need more info on this -  won't go into it any further here because this is not the topic of this thread.

[nik]

Here's an example that should get you there. No time to give a more detailed explanation right now, but go ahead and ask if there's something that seems odd =).

Put this into site/modules/PageFirstProductPrice.module and install the module from the admin side (check for new modules first).

<?php

class PageFirstProductPrice extends WireData implements Module {

    public static function getModuleInfo() {
        return array(
            'title' => 'Copy first product price',
            'version' => 100,
            'summary' => 'Copies first product price from a repeater to another field',
            'singular' => true,
            'autoload' => true,
        );
    }

    public function init() {
        $this->pages->addHookBefore('save', $this, 'copyFirstProductPrice');
    }

    public function copyFirstProductPrice($event) {
        $page = $event->arguments[0];

        // we're interested in product pages only
        if($page->template->name != 'product') return;

        // copy first product version price, if there is one with a price
        if(count($page->product_versions) > 0 && $page->product_versions->first()->price) {
            $page->first_version_price = $page->product_versions->first()->price;
        } else {
            // no items wih a price, clear the field
            // this is needed to keep the field in sync if all items get deleted from the repeater
            $page->first_version_price = '';
        }
    }
} 

*** Edit ***

A bit too hurry as it seems I forgot to mention that my example needs a field called 'first_version_price' in your 'product' template to work. That field should be left hidden as it's populated automatically. And now that you've got a regular field right in your template it's straightforward to sort the product pages in the way you described.

And horst seems to have given you the most essential pointer on hooks already, go for it! There's some pretty useful information in the wiki as well. After reading those two and looking at HelloWorld module you'll find yourself hooking away in the promised land of module development in no time.

Edited June 10, 2013 by nik

[horst]

 ...

Btw if you have any tips on where to start learning module development i'd be grateful -  besides "look at the hello world module" and "see how other modules are done" , that doesn't help me unfortunately. I can write PHP up to and including creating and using my own classes, and extending existing ones - but i'm a beginner and  can't figure out how i should work with a huge thing like PW or make sense of existing modules.

...

Hi Ovi, when looking at your code and your statement of what you can do in PHP, I think you just should start here.

And yes, you also should use the "Hello World Module" as a skeleton too.

For example, if you want to do something that isn't implemented in PW core, you can [write | implement] your

 somehow into templates, or you can create a module. As it should not be a big problem for you to write your working code, you may find it not an easy task to get the right Hook. (for me it is exactly that)

And at the beginning you can do it like we all do (we, who are not belong to the pro's, - we, who are not be able to cite the core code when waken up in middle of the night, - we, who we need to learn and have fun with it): we drop a post here and describe what we want to do and then get perfect answers on what Hook would be best to use for it!

---

Basically a module hooks into PW somewhere and somehow. You just need to kow where to hook in and when, (before or after).

And you need to know how to access objects & values and how to return your result. That's all covered in the new great API-Documentation of Hooks!

Read it and just go on

[Ovi]

@Nik

Thanks so much for that, the code is working perfectly and it all makes perfect sense now! So amazingly easy!

I will use the same method on other sorting issues i have that would not work out of the box with PW's $pages->find() method.

I still have one problem though:

I already have a lot of products in the site, and since the module hooks on the "Save" event, the field won't populate unless i go to each page and save it. There are a LOT of pages .

What would be a smart method of going about this? Can i trigger the Save event on all pages that have the product template, or can i change to hook somehow? 

@horst

Thanks for the pointers, i've taken your advice and have begun reading up on hooks and the API documentation in general, but snippets of code like Nik's one above really make a world of difference in shining some light on the whole thing.

EDIT: Eureka! I managed it myself:

<?php 
	 $allProducts = $pages->find("template=product");

	 foreach ($allProducts as $p) {
		$p->setOutputFormatting(false);
	 	$p->save();
	 	echo "page ".$p->url." saved!";
	 }
 ?>

Done and done. Now all the product pages have the 'first_version_price' field populated!

Thanks for all the help everyone, you rock!   
 

[Soma]

Little detail. Instead of

$p->setOutputFormatting(false);

you can also use

$p->of(false);