theoretic

Selectors and aggregation functions

Recommended Posts

Hi there! And thanks for Processwire!

This is rather a question than a feature request, maybe there's a well known solution which i missed.

So what about aggregation in selectors? Let's suppose we have a PW-powered shop where any product has a rating made by customers. Every time the user polls two data fields are updated: points and votes. The rating is calculated as this:

$rating = $page->points / $page->votes;

It was easy, yeah. But what about sorting products by their rating? Basically there are two ways of doing it:

  1. Creating one more field called rating and updating it after every vote. For sure it will work, but we have to create an extra data field which in fact stores data based on two another fields. Bad practice i think.
  2. Getting all products into array and applying a cutom sort function. Will work again, but this demands much more coding than using the native PW selectors.

But how could it be done in the best possible way? Let's imagine this:

$products = $page->children("sort=points/votes");

Or maybe this:

function rating($a,$b){
	return $a->points/$a->votes > $b->points/$b->votes;
	}

$products = $page->children("sort=rating()");

Okay, let's go even deeper. Maybe we need "virtual" fields which are not stored in DB tables like other PW fields but represent the dynamically calculated values?

$page->rating = function(){
	return $page->points/$page->votes;
	}

$products = $page->children("sort=rating");

Will be glad to see advice on the best practices. Thanks in advance!

Share this post


Link to post
Share on other sites

You have two good answers here 

 

Soma's answer on Adrian's link does more or less what you propose in your last pseudo-code.

  • Like 2

Share this post


Link to post
Share on other sites
5 hours ago, diogo said:

Soma's answer on Adrian's link

Soma got one of the last Green Cards and moved from Switzerland to Atlanta? I must have missed a memo :lol:

  • Like 1

Share this post


Link to post
Share on other sites
On 2/14/2018 at 6:42 AM, theoretic said:

Okay, let's go even deeper. Maybe we need "virtual" fields which are not stored in DB tables like other PW fields but represent the dynamically calculated values?

for the most part, the hidden field option i think is best, but does take some logic and page saving; If you do want to have them be virtual, then you can do a hook

wire()->addHookProperty('Page(template=product)::rating', function($event) {
    $product = $event->object;
	$event->return = $product->points / $product->votes;
});

but you will be limited to in-memory selection, since the property is not in the database

  • Like 4

Share this post


Link to post
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

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By LMD
      I don't know if this is the right place, it's not really a tutorial, just a tip based on notes I wrote myself in a recent project to get it straight in my own head. I thought it might be useful for others in a similar situation.
      Scenario:
      Create a search function that will search for keywords "foo" and "bar" in multiple fields, but the keywords do not have to be adjacent, in order, or even all in the same field. For eample, the selector must match if "foo" is in "field_a" and "bar" is in "field_b" -- so long as both keywords are present somewhere, the page match is valid.
      It is possible to just split the terms and do multiple queries on each field separately and then combine the results into a single PageArray for pagination (I believe there is a module that helps with this). However, I wanted to see if it was possible to do a basic version with a single query.
      Not The Solution:
      The following selector does not work when keywords appear separately in different fields (operator '~=' - contains all the words):
      $selector = "title|field_a|field_b~=foo bar"; What the selector is saying:
      FIND BOTH "foo" AND "bar" IN title OR FIND BOTH "foo" AND "bar" IN field_a OR FIND BOTH "foo" AND "bar" IN field_b In this case, both "foo" and "bar" have to be in the same field (but not adjacent or in order) to match.
      The Actual Solution
      What we need to use is "named selectors" to let us match each individual keyword separately while still using one selector.
      Using the same example as before:
      $selector = "selector1=(title|field_a|field_b~=foo), selector2=(title|field_a|field_b~=bar)"; What the selector is saying at its most basic level:
      FIND BOTH selector1 AND selector2 Or, to expand on this, it is saying:
      (FIND "foo" IN title OR field_a OR field_b) AND (FIND "bar" IN title OR field_a OR field_b) Crucially, "foo" and "bar" do not have to be in the same field to match.
      Practical Method
      In this example code, I am actually allowing the search for phrases (using "quoted text") as well as individual terms, so a person could enter...
      "foo bar" baz ... and it will keep "foo bar" together aa one term and "baz" as a separate term and match them as an exact phrase.
      // Keywords obtained from $input->get and cleaned (multiple spaces removed)/sanitized etc. $keywords = '"foo bar" baz'; // Split into individual search terms by space (preserve spaces in quoted text) $terms = str_getcsv($keywords, " "); // array("foo bar", "baz") // Build up named selectors $ns = ""; // named selectors string $i=1; // named selector count foreach ($terms as $term) { // operator '*=' - contains the exact word or phrase $ns .= ", ns{$i}=(title|field_a|field_b*=" . trim($term) . ")"; $i++; } //$ns = ", ns1=(title|field_a|field_b*=foo bar), ns2=(title|field_a|field_b*=baz)" // Construct the whole selector (modify/add other general selectors as needed) $selector = "template=my-template, limit=20, sort=-date" . $ns; // Find pages based on selector $results = $pages->find($selector);  
      DISCLAIMER
      I haven't done any tests to see if this method is more efficient than running queries on each field separately and combining the results, I just wanted to see if it was possible!
    • By verdeandrea
      Hello girls and guys,
      i just saw that if I use a selector on a textarea, for example 
      $pages->find('body%=notes') it also find that key in html classes and attributes. For example it will find
      <p class="text-notes">Lorem ipsum dolor sit amet cosectetur.</p> Is there a way to avoid this anche check only the texts without the html tags?
      Thanks!
    • By activestate
      Hi,
      I'm having problems with seatching for a value in repeater.
      One of pages have attached repeater called 'insight_repeater'.
      This repeater has h1_tag (text field) and i'm trying to search for repeater items that contains 'lor' phrase.

      And right now i'm trying to do simple search, looking for phrase 'lor'. I've tried many approaches, but nothins seems to work, so i want to ask what i'm doing wrong here:

      <?php $query = $page->get("insight_repeater")->find('h1_tag~=Lor'); ?>

      Thanks for help.
       
    • By prestoav
      Hi all,
      I'm having an odd issue that I think is down to the selector I'm using but I can't track down the cause!
      I'm using front-end AJAX search form on a list of product adverts (pages)  where the product is made by a certain manufacturer (vendor). The manufacturer is chosen buy the person listing the adverts from a page field list of manufacturers who's parent is id=1034.
      Most manufacturers are being found without an issue. The only one that's causing a problem is called 'Proj-ect" (the "-" being part of their brand name.
      Here's my selector code:
      $thisBrandProducts = $pages->find("template=advert,vendor.title~=$q,has_parent=1034,limit=20,created_users_id!=$susDealerList"); $susDealerList is a list of advertisers that are not currently live so pages should be excluded.
      Could the "-" in the name be the issue with those pages not being returned? If so does anyone have any ideas of how I can get around this?
      Thanks in advance!
    • By theoretic
      Hello there! And thanks for Processwire!
      It may appear a noob question but what about selectors like "field1=val1||field2=val2"? I mean that the page we're looking for should have field1=val1 OR field2=val2 . There's plenty of examples for cases like "field=val1|val2" but it's another case.
      Practical example: frontpage uder registration. User should enter nick and email, and both of them should be unused by other registered users. It's logical to use something like
      $userTest = $users->get("nick={$input->post->nick}|email={$input->post->email}"); But this doesn't work.
      That's the best practice in this case? Thanks in advance!