Hello everyone!
I have a problem with sorting my collection by an attribute that is not part of it. I have two collections that I get the data from. First I have a collections “Group” which stores information about group and also array of team IDs and order attribute. I want these teams to be in order as I add them. After I extract the array of IDs from specific group I use teamCollection.find({"_id":{ "$in": arrayOfTeams }});
to get only these teams from the “Team” collection. The problem is that teams in “Team” collection are in different order than those team IDs in “Group” collection and I can’t use the order attribute from the array of teams because the “Team” collection doesn’t have the order attribute. Can I somehow put the order attribute from the array that I get from “Group” collection into sort function for “Team” collection? Or should I make an intermediate table and denormalize the tables? Or is there any other way that you would advise me to do it? Any help would be helpful.
EDIT: The array that I set in the collection.find() function has IDs in the right order. If there is a was to tell the $in operator to select the documents in the same order as array, that would solve my problem.
Example of the document in Group collection:
Example of the document in Team collection:
How big are these arrays of teams? It would be relatively simple in code I think.
The array of teams would be approximately 4 - 6 probably never more than 6. I just had an idea. What if I take a different approach - I would remove the array of teams in the Group collection and create an array of groups in Team collection. That way I could access the order attribute if I put it in the same collection. I think that would solve it, just need to think through it for any problems that I could encounter changing the arrays.
I came up with this:
// Remap groupDoc.teams [{teamid:id1,order:o1}, {teamid:id2,order:o2}, ...] into hash {id1:o1, id2:o2, ...}
// Only works if ids are unique
// groupDoc.teams is the unaltered array from the document from Group
const hash = {};
groupDoc.teams.array.forEach(team => {
hash[team.teamid] = team.order;
});
// Now sort the result of the find using the hash
// Note, we no longer have a cursor in result
const result = teamCollection.find({"_id":{ "$in": arrayOfTeams }}).fetch().sort((a, b) => {
if (a[hash[a._id]] > b[hash[b._id]]) return 1;
if (a[hash[a._id]] < b[hash[b._id]]) return -1;
return 0;
});
So, result
would be an array containing the Teams documents sorted accordingly.
Edit: fixed typo in hash generation.
1 Like
That’s a nice workaround that I never thought of. Thanks! 
1 Like
You’re welcome - untested though! 
Just an aside: you don’t really need an order
property in your embedded teams
object. Arrays are already ordered, so you could simplify [ { id: 'abc', ord:2}, { id:'xyz', ord:0 }, { id:'pqr', ord:1 } ]
to [ 'xyz', 'pqr', 'abc' ]
.
1 Like
First I had like you said. I had only IDs of the teams. I added order attribute later because the $in operator didn’t sort like I wanted too, but I soon realized that order would not help me because I’m retrieving from team and not group collection.
1 Like
Agreed, but by using an intermediate hash, you can eliminate the order
property:
const hash = {};
groupDoc.teams.array.forEach( (teamid, index) => {
hash[teamid] = index;
});
Edit: fixed code to match what I said. Not doing too well for typos today!
1 Like
Thanks for your help! Will try it out! 
1 Like