Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mongoose/mongoDB query joins.. but I come from a sql background

Tags:

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!

like image 276
nwkeeley Avatar asked Jan 16 '13 16:01

nwkeeley


People also ask

Why joins are not used in MongoDB?

One order and 8 line items are 9 rows in relational; in MongoDB we would typically just model this as a single BSON document which is an order with an array of embedded line items. So in that case, the join issue does not arise.

Are joins and subquery supported in MongoDB?

MongoDB Joins: Conditions and Subqueries on Joined Collections. MongoDB 3.6 has added support to: Execute a pipeline on a joined Collection. Perform multiple join collections.

Can MongoDB support joins?

Join Collections MongoDB is not a relational database, but you can perform a left outer join by using the $lookup stage. The $lookup stage lets you specify which collection you want to join with the current collection, and which fields that should match.

Can I use MongoDB and Mongoose at the same time?

Connecting to MongoDBMongoose requires a connection to a MongoDB database. You can require() and connect to a locally hosted database with mongoose. connect() , as shown below. You can get the default Connection object with mongoose.


2 Answers

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);      }) 
like image 140
Michael Yin Avatar answered Oct 07 '22 13:10

Michael Yin


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.
like image 42
joscas Avatar answered Oct 07 '22 13:10

joscas