26

I coming from a sql background so writing queries in sql where I join tables is quite simple but I guess I am missing that in mongoose/mongodb

Basically I know the Subscriber_ID (which maps to a document in the User Collection)

I want to pull the project group, with all the projects that the user belongs to so if I was to write this in pseduo sql it would be like

Select 
  ProjectGroup.title, 
  Project.Title 
FROM 
  ProjectGroup, 
  Project, 
  User 
WHERE 
  User.id = req.body.subscriber_id 
  AND Project.subscriber_id = User.id 
  AND  ProjectGroup.project_id = Project.id

There must be a way to do similiar joins in mongoose/mongodb because the type is mapping to a schema right?

My Schemas.....

Project Group Schema

var ProjectGroupSchema = new Schema({
    title             : String
  , projects          : [ { type: Schema.Types.ObjectId, ref: 'Project' } ]
});

Project Schema

var ProjectSchema = new Schema({
    title         : {type : String, default : '', required : true}
  , subscribers   : [{ type: Schema.Types.ObjectId, ref: 'User' }]
});

User Schema

var UserSchema = new Schema({
    first_name    : {type: String, required: true}
  , last_name     : {type: String, required: true}
});

Thank you!

nwkeeley
  • 1,397
  • 5
  • 18
  • 28

2 Answers2

60

You are just one step away!

Project Group Schema:

var ProjectGroupSchema = new Schema({
    title             : String
});

Project Schema:

var ProjectSchema = new Schema({
    title         : {type : String, default : '', required : true},
    group         : {type: Schema.Types.ObjectId, ref: 'ProjectGroup' },
    _users    : [{type: Schema.Types.ObjectId, ref: 'User' }]
});

User Schema:

var UserSchema = new Schema({
    first_name    : {type: String, required: true},
    last_name     : {type: String, required: true},
    subscribing   : [{type: Schema.Types.ObjectId, ref: 'Project' }]
});

Then you can do the following: 

user.findById(req.userId)
     .populate('subscribing')
     .exec(function(err, user){
          console.log(user.subscribing);
     })

Or: 

project.find({
        subscriber : req.userId
      })
     .populate('subscriber')
     .populate('group')
     .exec(function(err, projects){
          console.log(projects);
     })
Alan
  • 9,167
  • 4
  • 52
  • 70
Michael Yin
  • 1,764
  • 13
  • 13
  • 2
    Very interesting I will apply this and see how it works but the second one you mention looks like exactly what I need. – nwkeeley Jan 18 '13 at 22:28
  • 1
    Okay I just updated my schema references and used these populates works perfectly! – nwkeeley Jan 18 '13 at 22:57
  • This is awesome, but can you modify the return result from the exec, say if i dont want to include group field. – Bill Jun 29 '15 at 02:49
  • 2
    I am a bit at a loss for how it knows what to "join" on where. Is that what the `ref` property is for? Does mongoose's `populate` look for `ProjectSchema` based upon the ref being `Project`, and does that mean that a strict naming convention needs to be kept? – Randy Hall Nov 20 '15 at 19:32
  • God answer !! very clever – Rolly Jun 26 '19 at 23:34
4

There are no joins in Mongodb. This question I think is a good reference:

MongoDB and "joins"

To summarize, different strategies have to be adopted with mongodb for problems that would be addressed via joins in relational DBs. I would say you mainly end-up doing one of these two things:

  • Embedding: You embed information in a single document that would in a relational DB be distributed amongst different tables.
  • Joining information client-side: When you need to use information from several places, you query many times and then put the pieces together in your client.
Community
  • 1
  • 1
joscas
  • 7,474
  • 5
  • 39
  • 59