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