'MongoDB one-to-many querying

I'm having trouble with reverse lookup in MongoDB and don't have the words to express what I want to do in a google search.

For example I have a Client collection. An Order collection has a reference to Client and one to Salesman.

Is there a way to:

  • Get a list of all clients containing an array of their orders (given we don't store an array of references in the client?
  • Get a list of all clients whom have dealt with George the salesman?

Data would look like

Clients:

[
    {_id: ObjectId('c1'), name:'blow', fname: 'joe'},
    {_id: ObjectId('c2'), name:'smith', fname: 'john'},
]

Orders:

[
    {_id: ObjectId('o1'), item: 'wrench', client: ObjectId('c1'), salesman: ObjectId('s1')},
    {_id: ObjectId('o2'), item: 'monkey wrench', client: ObjectId('c1'), salesman: ObjectId('s1')},
    {_id: ObjectId('o2'), item: 'spanner', client: ObjectId('c1'), salesman: ObjectId('s2')}
]

Salesmen:

[
    {_id: ObjectId('s1'), name:'smith', fname: 'terry'},
    {_id: ObjectId('s2'), name:'wick', fname: 'john'},
]

In a query where I'm listing clients, I'd like to see:

[
    {_id: ObjectId('c1'), name:'blow', fname: 'joe', orders: [ObjectId('o1'), ObjectId('o2'), ObjectId('o3')]},
    {_id: ObjectId('c2'), name:'smith', fname: 'john', orders: []}
]

And in a list of salesmen I'd like to see:

[
    {_id: ObjectId('s1'), name:'smith', fname: 'terry', clients: [ObjectId('c1')]},
    {_id: ObjectId('s2'), name:'wick', fname: 'john', clients: [ObjectId('c1')]}
]

I've seen the recommendations from MongoDB where in a one-to-many relationship, I shouldn't keep a mutable array of orders in client and orders/clients in salesman, but I don't know how to perform the sort of "join" I'm after coming from a SQL background.

In a structure where order refers to client and salesman but not vice-versa, how do I get that?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source