CODEWITHSUNDEEP
mongodbbson
nuvio
nuvio

Reputation: 2625

MongoDB query sort count

Hi I have 2 Game records:

game 1 with a list of players p1(alice), p2(bob), p3(matt), p4(tom)

game 2 with a list of players p1(alice), p2(bob), p3(jack), p4(tom)

I'd like to query my MongoDB collection games to sort the most recurrent people that appear in the same game as BOB. A good result in this case would be: 

Alice: appear 2 times
Tom:  appear 2 time
Matt: appear 1 time
Jack: appear 1 time

In SQL I did: 

             SELECT idplayer, COUNT(idplayer) AS countplayer 
             FROM (SELECT b.idgame, b.idplayer 
             FROM associations a, associations b 
             WHERE a.idplayer="BOB" 
             AND b.idgame=a.idgame 
             AND b.idplayer <> "BOB"
             ORDER BY b.idgame DESC LIMIT 1000) as c
             GROUP BY idplayer 
             ORDER BY countplayer DESC LIMIT 5;

With mongo I am trying something like: 

gamesCollection.find("{playerid : #}","BOB").sort(//counter).limit(5)...

Can someone help me to fix this query for MongoDB? Thank you

EDIT:

I think I am getting closer what I want, but still wrong query:

> db.games.aggregate("[{ $match: {playerid : 'bob'} }, {$group : { _id:null, cou
nt: { $sum: 1}} } ]")

please suggest a solution. thanks

EDIT 2

An example of a game document :

{ "_id" : ObjectId("514d9afb6e058b8a806bdbc0"), "gameid" : 1, "numofplayers" : 3,
 "playersList" : [{"playerid" : "matt" },{"playerid" : "bob"},{"playerid" : "alice"} ] }

Upvotes: 0

Views: 1610

Answers (1)

user20140268
user20140268

Reputation: 1288

If original document looks like:

{
   _id: ObjectId(...),
   players: [...]
}

Then you can do it using aggregate:

var playerId = 'bob';
db.records.aggregate([
  { $match: { players: playerId }},
  { $unwind: '$players' },
  { $match: { players:  { $ne: playerId } } },
  { $group: { _id: { player: '$players' }, times: { $sum : 1} } },
  { $sort: { times: -1 } }
])

Upd:

For document:

{ 
  "_id" : ObjectId("514d9afb6e058b8a806bdbc0"), 
  "gameid" : 1, "numofplayers" : 3,
  "playersList" : [
    {"playerid" : "matt" },
    {"playerid" : "bob"},
    {"playerid" : "alice"} 
   ] 
}

The query is:

var playerId = 'bob';
db.records.aggregate([
  { $match: { 'playersList.playerid': playerId }},
  { $unwind: '$playersList' },
  { $match: { 'playersList.playerid':  { $ne: playerId } } },
  { $group: { _id: '$playersList.playerid', times: { $sum : 1} } },
  { $sort: { times: -1 } }
])

// I also found out that $group operator could be simpler

Upvotes: 1

Related Questions