'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
clientscontaining an array of theirorders(given we don't store an array of references in theclient? - Get a list of all
clientswhom have dealt with George thesalesman?
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 |
|---|
