javascript - Best design for user leaderboard scores in mongodb -

i'm having trouble design of database game, users of game have 3 score parameters weighted differently, scores results of specific actions (did p, or did q, or did l) within app. user this:

{   "_id": { "$oid" : "531f14324fe3ba6360335230" },   "p": 88,   "q": 0,   "l": 10,   "totalscore":8.9 } 

where p, q, l different scores have different weights.

i want query information based on weekly/monthly activity of user, means users got highest score in p, q, l scores top users of current week.

i totally failing understand how make proper architecture both support date querying weeks/months , support weighted calculation of scores in efficient way assuming want make leaderboard surrounding users , have position of current user

-----------------q--------p--------l------ 4. user121      25       5         0 5. currentuser  5        7        28 6. user77       3        2        43 ----- 

would appreciate lot.

upd: rough draft of design

i have actions collection, contains documents each usergenerated action, smth this:

{     id: objectid,     type:'q',     time: date,     userid: user_objectid,     entityid: entity_objectid } 

the same goes other types.

so when action takes places in game - create such document , increment proper type field in scores document user. after calculate totalscore field based on formula:

totalscore = q + p/10 + l/100 

now can query , sort total score, doesnt have data whether score last 7 days or not, still have no clue how achieve this.

this seems on right track per last update, might offer few "tweeks" design address date range part of question.

while can nice things populate using mongoose, still worth remembering sort of "joining" simulated, in happens additional queries made database in order retrieve "related" items.

as such, when using mongodb idea keep information want use in same collection. simple example here, i'm going go keeping "username" in these "event" type of entries. because information used when summarizing this. documents "events" (what calling them ) appear this:

{      "userid": 123,      "username": "user1",      "type": "q",      "time": isodate("2014-04-07t03:56:33.488z") }, {      "userid": 123,      "username": "user1",      "type": "p",      "time": isodate("2014-04-07t03:56:33.488z") }, {      "userid": 456,      "username": "user2",      "type": "p",      "time": isodate("2014-04-07t03:56:33.488z") } 

of course additional fields these ones focus on.

to bring these within date range can this:

db.events.aggregate([     // match date range required     { "$match": {         "time": {              "$gte": new date("2014-04-07"),              "$lt": new date("2014-04-14")          }     }},      // group , reshape matching documents     { "$group": {         "_id": "$userid",         "username": { "$first": "$username" },         "q" : { "$sum": { "$eq": [ "$type", "q" ] } },         "p" : { "$sum": { "$eq": [ "$type", "p" ] } },         "l" : { "$sum": { "$eq": [ "$type", "l" ] } }     }},      // project "score" value     { "$project": {         "username": 1,         "p": 1,         "q": 1,         "l": 1,         "score": { "$add": [             "$q",             { "$cond": [                 "$p",                 { "$divide": [ "$p", 10 ] },                 0             ]},             { "$cond": [                 "$l",                 { "$divide": [ "$l", 10 ] },                 0             ]},         ]}      }},      // sort results "score" descending     { "$sort": { "score": -1 } },      // optionally limit results     { "$limit": 10 }  ]) 

so of groups results per user "event" based entries within time period, calculates total score ( being careful not divide 0 ), , sorts results highest score on top.

you can similar query find highest placed user each week or other time interval:

db.events.aggregate([     // match date range required - 1 year sample     { "$match": {         "time": {              "$gte": new date("2014-01-01"),              "$lt": new date("2015-01-01")          }     }},       // group , reshape matching documents     { "$group": {         "_id": {              "week": { "$week": "$time" },             "userid": "$userid"         },         "username": { "$first": "$username" },         "q" : { "$sum": { "$eq": [ "$type", "q" ] } },         "p" : { "$sum": { "$eq": [ "$type", "p" ] } },         "l" : { "$sum": { "$eq": [ "$type", "l" ] } }     }},      // project "score" value     { "$project": {         "username": 1,         "p": 1,         "q": 1,         "l": 1,         "score": { "$add": [             "$q",             { "$cond": [                 "$p",                 { "$divide": [ "$p", 10 ] },                 0             ]},             { "$cond": [                 "$l",                 { "$divide": [ "$l", 10 ] },                 0             ]},         ]}      }},      // sort highest score each week     { "$sort": { "_id.week": 1, "score": -1 } },      // group again highest value in each week     { "$group": {         "_id": "$_id.week",         "userid": { "$first": "$_id.userid" },         "username": { "$first": "$username" },         "q": { "$first": "$q" },         "p": { "$first": "$p" },         "l": { "$first": "$l" },         "score": { "$first": "$score" },      }}  ]) 

if require running totals best off "pre-aggregating" results collection , keeping discreet weeks in terms of overall performance, or indeed keeping running totals week or whatever timeframe suits best.

so reference, there bit take in here, main documentation can viewed various aggregation framework operators , date aggregation operators these allow "group" on different components of date, being either week, month, day, hour or year.