Order collection by an attribute that is not part of the collection

help
1

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:

groupexample
groupexample1231×203 23.6 KB

Example of the document in Team collection:

teamexample
teamexample1160×85 11.3 KB

2

How big are these arrays of teams? It would be relatively simple in code I think.

3

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.

4

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
5

That’s a nice workaround that I never thought of. Thanks! :slight_smile:

1 Like
6

You’re welcome - untested though! :wink:

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
7

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
8

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
9

Thanks for your help! Will try it out! :slight_smile:

1 Like