2

I am trying to build a leaderboard page, and I'm having difficulties finding a way to group the entries by Author.

I was able to create a plugin to get the sql sum function in there, but I now need to add a group by Author and order by Score DESC to that.

First steps were taken from here: Ability to SUM() or GROUP BY from the template or ElementCriteriaModel

What I ultimately want is a query that would look like this (very simplified of course):

SELECT author, SUM(content.score) as score FROM content Group By Author Order By score DESC

Is there a way to do this "natively" or do I need to write my own query?

2

Not sure if this is the best way to do it, but I created this little function in a Variable class. I get all the necessary info (username and score) in an array. Works fine for what I needed.

public function leaderboard(ElementCriteriaModel $criteria)
   {
       $query = craft()->elements->buildElementsQuery($criteria);

   if ($query)
   {
      $query
          ->select("authorId, SUM(field_score) as score, users.username as username")
          ->join("users", "users.id = authorId" )
          ->group("authorId")
          ->order("score DESC");

               return $query->queryAll();
       }
   }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.