Selector question

[deltavik]

Hey guys, 

   I thought I had selectors under control until I ran into this situation. 

This is my structure of four templates: 

Articles
- Article

Authors
- Author

Article contains a pagefield for author. If I wanted to get all articles written by an author, I can run a selector such as "template=article, author=$authorName".  So far so good. 

Here is where I am stuck: I want to display a list of top 10 authors with the most number of articles written.  In other words, I want to write a selector that

- finds items with template=author 

- sorts descending by the count of articles written by author <-- I am unable to formulate this part

- limits items to 10

One way to solve this is: Get a count of articles by all authors in a php array. Then sort the resultset by article count and get author names for the top ten in the array. This method can be heavy on both database and php, depending on the number of authors and articles in the system.

Is there a selector-only trick to solve this problem?

thanks

 

[Can]

As far as I remember this is not yet possible as there is no "reverse" selector. This is discussed somewhere in the forums..

Think your way is the easiest. You could cache it using wirecache for example..
https://processwire.com/talk/topic/10548-my-way-of-using-wirecache-and-some-hints-to-make-life-easier-with-it/

Another possibility would be to store the needed information (number of articles) in a field e.g. "article_count", so every time an author writes an article you increment or decrement if an article gets deleted..
if authors use pw backend you would hook after Page::save (or maybe Page::saveReady) and update the value accordingly, like this you could hook into trashing or unpublishing etc of pages.
but maybe you got some front end form where authors post their content then you could just add something like $user->setAndSave('article_count', $user->article_count+1) (there might be a shorter syntax but I can't test right now..) after your existing article save logic..

 

[deltavik]

thanks @Can  for the wirecache example. That will come handy.

I am thinking of running the sql query directly to get results. This seems quite efficient. And, I will also cache the results of this. 

This is my query: 

SELECT count(pages_id), data as author FROM field_authors 
GROUP BY data
ORDER BY count(pages_id) DESC
LIMIT 10;

 

[Robin S]

Getting the authors with the most articles would be simple if the article pages were added to author pages rather than the other way around, but of course that's not so good for workflow. But there have been some posts on the forum that suggest linking two Page fields, where changing one updates the other.

• Gist by @Martijn Geerts, discussed here
• Gist by @Soma, discussed here and here

These may also be relevant:

 

[Orkun]

I would do something like you mentioned:

   $articleCountPerAuthor = array();
   foreach($pages->find("template=author") as $author){
		$articleCount = $pages->count("template=article, author=$author");
   		$articleCountPerAuthor[$author->title] = $articleCount;
   }
   arsort($articleCountPerAuthor);
 
 	// result of $articleCountPerAuthor
	/*
 	array(
		"Actor XXX" => 500, 
		"Actor XXX" => 400,
		"Actor XXX" => 200,
	)*/

$pages->count() - little overhead, since it doesn't load the pages like $pages->find() does

arsort() - Sort an array in reverse by array value

Since Processwire is extremly performant and efficient in handling pages you shouldn't have Problems.

Edited August 15, 2016 by Nukro
duplicate keys possible, changed key/value pair, replaced krsort() with arsort()

[szabesz]

5 hours ago, Nukro said:

$articleCountPerAuthor[$articleCount] = $author->title;

What if we have the same number of articles? It will overwrite the previous one, won't it?

Edited August 15, 2016 by szabesz
spelling

[Orkun]

17 minutes ago, szabesz said:

What if we have the same number of articles? It will overwrite the previous one, won't it?

Oh, missed that ^^.

I have updated the post. Thanks @szabesz. It's still possible to have duplicate keys(authors which has the same name?) but probably is less likely. You could add a random prefix or something which you can remove at output with str_replace() or exlode() or something...

[horst]

2 hours ago, Nukro said:

It's still possible to have duplicate keys(authors which has the same name?) but probably is less likely. You could add a random prefix or something which you can remove at output with str_replace() or exlode() or something...

Every thing is a page! Om! Every page has a ID! Om!

$articleCountPerAuthor[$author->id] = $articleCount;

Should be save!

[szabesz]

So we need additional steps anyway, it just depends on our personal preference and/or use case, I suppose.

[Orkun]

2 hours ago, horst said:

Every thing is a page! Om! Every page has a ID! Om!

$articleCountPerAuthor[$author->id] = $articleCount;

Should be save!

LOL, how could I miss that! 

[szabesz]

Let's suppose we are absolutely sure that $author->title is unique. In that case your example is a valid one if we do not need the ID in a particular use case (e.g. we just want to use the result once for echoing it out and throw it away afterwards). However, if we have the slightest doubt that this is not the case, then  @horst is right I think. The best practice is to always stick to IDs when we can. So, thank you horst for pointing this out!

[Robin S]

3 hours ago, horst said:

Every thing is a page! Om! Every page has a ID! Om!

$articleCountPerAuthor[$author->id] = $articleCount;

Should be save!

Another option is to set the article count as a property of the author Page object.

$authors = $pages->find("template=author");
foreach($authors as $author){
    $author->articleCount = $pages->count("template=article, author=$author");
}
$authors->sort("articleCount");

 

[szabesz]

So we use this: https://processwire.com/api/ref/wire-array/sort/ , right?

[Robin S]

1 minute ago, szabesz said:

So we use this: https://processwire.com/api/ref/wire-array/sort/ , right?

Yes. In the example...

$authors->sort("articleCount");

... $authors is the PageArray (WireArray) and "articleCount" is the property we sort on.

[deltavik]

18 hours ago, Robin S said:

Getting the authors with the most articles would be simple if the article pages were added to author pages rather than the other way around, but of course that's not so good for workflow. But there have been some posts on the forum that suggest linking two Page fields, where changing one updates the other.

• Gist by @Martijn Geerts, discussed here
• Gist by @Soma, discussed here and here

These may also be relevant:

 

helpful threads. Seems like many people have run into this situation. I thought I missed something in the tutorials. 

 

[deltavik]

12 hours ago, Nukro said:

I would do something like you mentioned:

   $articleCountPerAuthor = array();
   foreach($pages->find("template=author") as $author){
		$articleCount = $pages->count("template=article, author=$author");
   		$articleCountPerAuthor[$author->title] = $articleCount;
   }
   arsort($articleCountPerAuthor);
 
 	// result of $articleCountPerAuthor
	/*
 	array(
		"Actor XXX" => 500, 
		"Actor XXX" => 400,
		"Actor XXX" => 200,
	)*/

$pages->count() - little overhead, since it doesn't load the pages like $pages->find() does

arsort() - Sort an array in reverse by array value

Since Processwire is extremly performant and efficient in handling pages you shouldn't have Problems.

Yes, something like this was the pseudo code in my head. For this case, I went with sql query straight up. Given the high number of articles and authors, I thought it will be efficient if I just do a private, one-on-one chat with the database. 

The selector-trick will be useful for smaller datasets.

[Can]

2 minutes ago, deltavik said:

Given the high number of articles and authors

Maybe findMany could be of interest? https://processwire.com/blog/posts/find-and-iterate-many-pages-at-once/#the-new-pages-gt-findmany-method