Saving Position From Loop for Each Result

[quickjeff]

Hi Guys! 

I have a bit of a puzzle for you. I have been mapping this one out in my brain and on paper all week. I haven't come up with the right solution yet. 

Here is the project. I am working on a directory site similar to http://demo.processwire.com/ but for another subject.

Let's say I am building the skyscraper site but let's say I am doing this for restaurants in cities.

I am building a parent page called cities and then a parent page called restaurants. 

Goal is to display the restaurants according to popularity.
Therefore, if I am storing the pages under their appropriate parent. Restaurants are under restaurants and cities are under cities.  

Visual  Page Tree:

Cities

• New York
• Chicago
• New Orleans

Restaurants

• Big Toms Burgers
• McDonalds
• Dominos

 

Now a restaurant may have 10 locations therefore, I am using a foreach loop inside of the city page to call all restaurants that pertain to this city and sorting by popularity. ? 

Here is the kicker, how the heck do I document the position the restaurant appears in under the city? Do I save a value in the database? 
I know I can use a counter to count the loops and assign a value then save that value to the database to the pertaining Restaurant but this relies on page loads and imagine 1000 restaurants all updating at the same time on the database on page load. WOW! No go. 

How the heck do I save this value without ever having to load a page? ALL IDEAS WELCOME PLEASE! ? 

 

[Robin S]

I don't understand the question - perhaps you can clarify more...

How are you measuring and storing the popularity score? Some sort of "likes" system?

If the popularity is per location rather that per restaurant brand then you'll need some kind of entry (page most likely, but technically it could be something like a Profields Table row also) for each restaurant location.

Let's say it's a page, and it uses template "restaurant_location". Each restaurant location page is a child of the restaurant brand (or the restaurant locations could be under a separate parent and use a Page Reference field to link them with a restaurant brand - with PW there is always lots of flexibility to set things up in a way that suits you). Template restaurant_location has a Page Reference field for location that selects from the City pages, and a "popularity_score" integer field that gets incremented every time someone "likes" it.

The listing page for each city gets all restaurant locations relating to that city sorted by popularity_score.

[quickjeff]

Hey Robin, 

 

Popularity score I am using is based on star rating, using the comments field for this one and requiring stars. 

Then when I use a selector and then foreach loop I am sorting by average number of stars.

However I want to be able to show the restaurant owner the ranking of their restaurant per city. Thats the issue. The position is not being stored anywhere, it's happening on the fly with the loop. If I decided to store it on page load, then I would have to rely on the page being loaded for an update as new restaurants are added. 

 

[Robin S]

I understand now. There are a couple of ways you could do this - a simpler way and a more performant way. In the demo code below my selector is for sorting news items by title - you would adjust the selector as needed to find your restaurants. This code would go in the template file for your restaurant location template.

1. Simpler way: probably fine if there is not a huge number of matched pages

// Function to get number plus ordinal suffix
function ordinal($number) {
    $suffixes = ['th','st','nd','rd','th','th','th','th','th','th'];
    if(($number % 100) >= 11 && ($number % 100) <= 13) {
        return $number. 'th';
    } else {
        return $number. $suffixes[$number % 10];
    }
}

// Define the selector that finds your pages
$selector = "template=news_item, sort=title";

// Get all the matched pages
$items = $pages->find($selector);
$position = $items->getItemKey($page) + 1; // add 1 to adjust for zero index
$position_str = ordinal($position); // the position with ordinal suffix

echo "This is the $position_str item.";

 

2. More performant way: if there are a large number of matched pages you can use an SQL query which avoids the overhead of loading all the pages

First work out the SQL query for matching your pages:

$query = $pages->getPageFinder()->find(new Selectors($selector), ['returnQuery' => true])->getQuery();
bdb($query); // Use Tracy barDumpBig() to dump the SQL query 

Now tweak the query a bit because you only need the IDs of the matched pages.

The finished example for my sorted news items is below:

$sql = "SELECT pages.id 
FROM `pages` 
LEFT JOIN field_title AS _sort_title ON _sort_title.pages_id=pages.id 
WHERE (pages.templates_id=45) 
AND (pages.status<1024) 
ORDER BY _sort_title.data";
$query = $database->query($sql);
  
// Get the IDs in an array, flipped so the ID is the key and the position is the value
$results = array_flip($query->fetchAll(\PDO::FETCH_COLUMN));
$position = $results[$page->id] + 1; // add 1 to adjust for zero index
$position_str = ordinal($position); // the position with ordinal suffix

echo "This is the $position_str item.";

 

Credits: 1, 2, 3

[quickjeff]

Robin, awesome thank you! 

 

Here is the thing, I can certainly get the position on the actual page that contains the foreach loop and selector but I also have an account page for the restaurant owner to see data on his restaurant such as what cities he is appearing under. However this page I would like to show the position his restaurant is displayed at. Right now, the only way to do this is to write the foreach loop again inside of the account page. I think the way I have it now is a poor way of doing it, Ideally I would like to somehow find a nice way to display the position the restaurant listing appears in. Any other input is appreciated. Thank you!

[Robin S]

1 hour ago, quickjeff said:

However this page I would like to show the position his restaurant is displayed at. Right now, the only way to do this is to write the foreach loop again inside of the account page.

Yes, that's what the code in my previous post is for. There's no foreach loop involved. Did you try it?

That code provides a way of working out the position of any page relative to a set of pages it is a part of.  When you put the code in the template file of a restaurant location page then the page is represented by the API variable $page. If you want to use it in some other template file (e.g. your account template) then you would substitute $page with $p where $p is a restaurant location page.

[LostKobrakai]

Your posts sound like you have two problems here: One is code organization and the other is performant calculation of leaderboards. 

7 hours ago, quickjeff said:

Right now, the only way to do this is to write the foreach loop again

This is why people usually use some kind of separation between core domain logic (ranking of restaurants) and the website layer (actually serving a website) in their code. Duplicating such essential code as calculating the position of a restautrant because it‘s needed in multiple places of your website is a bad idea. The risk of both places getting out of sync over the time of development is one I'd not like to take.

7 hours ago, quickjeff said:

I can certainly get the position on the actual page that contains the foreach loop and selector

For the leaderboard calculation you seem to currently use the most simple but inefficient way: Load all the information needed into php and calculate the positions for the set of data in memory. There are two types of optimizations from here: 

1. Move the calculation logic nearer to the data it needs to work on, a.k.a. do the calculation in mysql instead of in php;
   This saves on the need to move all the data from mysql to php, which scales very poorly. 
2. Persist positions for as long as possible, a.k.a. caching;
   It seems you currently recalculate positions each time someone visits that page. But they do only change when a new ranking / comment is posted on your website. Also usually a little bit of delay between new comments and updating the ranks is not a problem.

Given those two optimizations there are three concrete ways to handle things:

• Just Opt. 1: Write an SQL query, which can just return e.g. restaurantID + position in sorted order for all the leaderboards you have: global / per city / ….
• Just Opt. 2: Use some of ProcessWire's caching options to save your various leaderboards still calculated in php.
• Both Opt. 1 and Opt. 2: Write an SQL query and create a materialized view in the db to query. This way the calculation and caching happens in mysql, which is probably as performant as it can be.

Edit:

7 hours ago, quickjeff said:

How the heck do I save this value without ever having to load a page?

The missing puzzle piece here is treating those leaderboards as the data to store.