mongodb implements an example of a query method that is linked to a library

  • 2020-06-19 11:59:12
  • OfStack

preface

Recently, I encountered a problem in my work, which required the joint table query operation of mongodb database. I found that there were few data in this aspect on the Internet, so I had to realize it by myself. Let's have a look at the detailed introduction:

Note: Only database linked table queries are covered here, and cross-library linked table queries may be covered later (they may be joined later due to corporate architecture changes)

I used two types of linked table queries, one for mongoose's populate and one for $lookup

1. populate

populate is associated with child tables using foreign keys

For example, you now have an order table structure (dynamic foreign key) :


var orderSchema = new mongoose.Schema({
 uid: { type: String, required: true }, //  The user id
 amount: { type: Number, required: true },
 oType: { type: Number, required: true }, //  Order type 
 status: { type: Number, required: true }, //  Order status :1 complete  2 unfinished  3 failure 
})

The users table:


var userSchema = new mongoose.Schema({
 phone: String,
 status: String,
 createdAt: Date,
 updatedAt: Date
})

Now I want to query the order table and return the corresponding user phone field


order.find().populate({path: 'uid', model: User, select: '_id real_name phone bankcard'}).exec(function(err, order) {
 // order: {
 // uid: {
 // phone: '15626202254',
 // status: "expand",
 // createdAt: Date,
 // updatedAt: Date
 // },
 // amount: 5000,
 // oType: 2, //  Order type 
 // status: 1, //  Order status :1 complete  2 unfinished  3 failure 
 // }
});

Here, uid of order points to the _id field of user, but you can also define a foreign key when you create a new table, but I won't go into details here

2. $lookup

lookup is the use of aggregate's $lookup attribute, directly to the website example is very easy to understand

orders table


{ "_id" : 1, "item" : "abc", "price" : 12, "quantity" : 2 }
{ "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1 }
{ "_id" : 3 }

inventory table


{ "_id" : 1, "sku" : "abc", description: "product 1", "instock" : 120 }
{ "_id" : 2, "sku" : "def", description: "product 2", "instock" : 80 }
{ "_id" : 3, "sku" : "ijk", description: "product 3", "instock" : 60 }
{ "_id" : 4, "sku" : "jkl", description: "product 4", "instock" : 70 }
{ "_id" : 5, "sku": null, description: "Incomplete" }
{ "_id" : 6 }

db.orders.aggregate([
 {
 $lookup:
 {
  from: "inventory",
  localField: "item",
  foreignField: "sku",
  as: "inventory_docs"
 }
 }
])

Is to use the item field of order as the query condition of inventory table {sku: item} and assign the value to the inventory_docs field, but it should be noted that the two fields must be of the same type (3.5 above seems to be able to be turned, not tried).

conclusion

Refer to the article

Associated table queries in Mongoose & & Aggregation query

Populate mongoose with foreign keys


Related articles: