mongodb + node/expressで、join結合のような例 #mongodb #node #express #javascript
■ 概要:
前のmongodb aggregateで、collection結合の
express 実装となります。
■ 環境
mongoDb : 3.6.3
node 14
・npm
mongodb: 3.6.3
express : 4.16.1
ejs : 2.6.2
■ 前の、aggregate関連はこちら
https://note.com/knaka0209/n/n0bf3851b62ae
■ データの構成
・orders.book_id と、books collection結合
■ データの追加
・マスタ側(book) は、手作業で追加する例です
db.books.insertMany([
{ code: "0101", name: "book-1", price: 120 },
{ code: "0102", name: "book-2", price: 130 },
{ code: "0201", name: "book-3", price: 180 },
]);■ 参考のコード
■ express 実装など
・orders ,add
routes/orders.js
get( /add )
books取得し、画面にrender
<select>等で、選択入力する例です。
router.get('/add',async function(req, res, next) {
try{
LibCsrf.set_token(req, res)
const collection = await LibMongo.get_collection("books" )
collection.find({} ).toArray(function(err, result) {
if (err) throw err;
console.log(result);
res.render('orders/new', {books: result });
});
} catch (err) {
console.log(err);
res.status(500).send();
}
});
/********************************
*
*********************************/
/********************************
*
*********************************/
router.post('/add', async function(req, res, next) {
try{
if(LibCsrf.valid_token(req, res)== false){ return false; }
var data = req.body
console.log(data )
var item = {
book_id: new ObjectID( data.book_id) ,
order_num: data.order_num,
created_at: new Date(),
};
const collection = await LibMongo.get_collection("orders" )
await collection.insertOne(item);
req.flash('success', 'Complete, save item');
res.redirect('/orders')
} catch (e) {
console.log(e);
req.flash('err', 'Error ,save item');
res.redirect('/orders')
}
});post ( /add )
画面からの、book.idを、new ObjectID で変換し
orders追加
・ordersリスト、
routes/api_orders.js
collection.aggregateで、 books結合し
sort指定等で、API出力
router.get('/index', async function(req, res) {
try{
const collection = await LibMongo.get_collection("orders" )
var page = req.query.page;
LibPagenate.init();
var page_info = LibPagenate.get_page_start(page);
console.log( "page=", page, page_info );
collection.aggregate([
{$sort: {created_at: -1} },
{
$lookup: {
from: "books",
localField: "book_id",
foreignField: "_id",
as: "book"
}
}]).toArray().then((docs) => {
// console.log(docs);
var param = LibPagenate.get_page_items(docs )
res.json(param);
})
} catch (err) {
console.log(err);
res.status(500).send();
}
});
■ Update
・2020/12/10 update,
結合するcollectionを増やす場合の例を追加しました
・参考コード
bread_orders: 注文
breads : 商品の種類
上位マスタ：
category, tags, type
・データの追加
db.category.insertMany([
{ name: "食パン" },
{ name: "調理パン"},
{ name: "通常品"},
{ name: "その他"},
]);
db.type.insertMany([
{ name: "hight-price" },
{ name: "normal-price"},
{ name: "low-price"},
]);
db.tags.insertMany([
{ name: "年末品" },
{ name: "お盆向け"},
{ name: "正月向け"},
]);・注文の、追加
routes/bread_orders.js
/********************************
*
*********************************/
router.get('/add',async function(req, res, next) {
try{
LibCsrf.set_token(req, res)
const bread_collection = await LibMongo.get_collection("breads" )
var breads = await bread_collection.find({} ).toArray();
res.render('bread_orders/new', {
// categories: categories, tags: tags, types: types,
breads: breads
});
} catch (err) {
console.log(err);
res.status(500).send();
}
});
/********************************
*
*********************************/
router.post('/add', async function(req, res, next) {
try{
if(LibCsrf.valid_token(req, res)== false){ return false; }
var data = req.body
console.log(data )
var item = {
bread_id: new ObjectID( data.bread_id) ,
order_num: data.order_num,
created_at: new Date(),
};
const collection = await LibMongo.get_collection("bread_orders" )
await collection.insertOne(item);
req.flash('success', 'Complete, save item');
res.redirect('/bread_orders')
} catch (e) {
console.log(e);
req.flash('err', 'Error ,save item');
res.redirect('/')
}
});・api / 注文
routes/api_bread_orders.js
collectionを、複数読込
.findOne, .aggregate で結合など。取得するcollection が増えて。複雑になりました
router.get('/show/:id', async function(req, res) {
console.log(req.params.id );
try{
var bread_order = []
var bread = []
const collection = await LibMongo.get_collection("bread_orders" )
var where = { _id: new ObjectID(req.params.id) }
var bread_order = await collection.findOne(where)
console.log("bread_id=", bread_order.bread_id );
//
const bread_collection = await LibMongo.get_collection("breads" )
await bread_collection.aggregate([
{ $match: { "_id": new ObjectID(bread_order.bread_id) } },
{
$lookup: {
from: "category",
localField: "category_id",
foreignField: "_id",
as: "categories"
},
},
{
$lookup: {
from: "type",
localField: "type_id",
foreignField: "_id",
as: "types"
},
},{
$lookup: {
from: "tags",
localField: "tag_id",
foreignField: "_id",
as: "tags"
},
}
]).toArray().then((docs) => {
bread = docs
})
console.log(bread);
var param = {
"bread_order": bread_order ,bread: bread
};
res.json(param);
} catch (err) {
console.log(err);
res.status(500).send();
}
});・show 画面、 下側に複数の上位マスタcollection
を結合して取得
....