1 Topic by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Topic: Query for position in a leaderboard

Since I just worked out the SQL required to do a query to find the position of someone in a leaderboard, I thought I'd post it here for safekeeping. I've not used it yet but I can see it coming in handy at some point.

SELECT * 
FROM ( SELECT row_number() OVER(ORDER BY count(distinct taxon_meaning_id) DESC) AS position, created_by_id 
       FROM cache_occurrences group by created_by_id ) as sub where created_by_id=#user_id#
ORDER BY position
John van Breda
Biodiverse IT

johnvanbreda's Website

2 Reply by namwebs (edited by namwebs 19-06-2015 14:38:29)

  • namwebs
  • Veteran
  • Offline
  • From: Namibia
  • Registered: 25-02-2013
  • Posts: 127

Re: Query for position in a leaderboard

I wanted something similar - a list of people ranked according to the number of records they had contributed. In case it is useful for someone else, this is how I did it:

1. Make a block with the following code in the block body, and the block text format set to php code:

<?php
iform_load_helpers(array('report_helper'));
global $auth;
if (!isset($auth))
  $auth = report_helper::get_read_auth(variable_get('indicia_website_id',''), variable_get('indicia_password',''));

echo report_helper::report_grid(array(
  'readAuth' => $auth,
  'dataSource'=>'library/alice/leaderboard',
  'itemsPerPage' => 10,
  'columns'=>array(
    array('fieldname'=>'recorders', 'display'=>'Observer'),
    array('fieldname'=>'records', 'display'=>'Records')
  ),
  'includeAllColumns' => false,
  'pager' => false,
  'sendOutputToMap'=>false,
  'rowClass'=>'',
  'extraParams' => array(
    'survey_id'=>'',
    'taxon_group_id'=>'',
    'smpattrs'=>'',
    'occattrs'=>'',
    'searchArea'=>'',
    'idlist'=>'',
    'currentUser'=>'',
    'ownData'=>'',
    'location_id'=>'',
    'ownLocality'=>'',
    'taxon_groups'=>'',
    'ownGroups'=>'')
));
?>

2. make a file called leaderboard.xml with this content and save it to the location specified in 'datasource' above:

<report
    title="Leaderboard"
    description="Leaderboard"
>
  <query>SELECT recorders, count(id) as records FROM cache_occurrences GROUP BY recorders ORDER by records DESC</query>
  <columns>
    <column name='recorders' display='Observer' sql='o.recorders' datatype='text' />
    <column name='records'  sql='o.records' datatype='integer' />   
  </columns>
</report>

3. make sure the block is set to display where you want it

That's it. Hopefully it will save somebody some time...

Alice

namwebs's Website