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  },
]);

■ 参考のコード

kuc-arc-f/app11_3mongo_join

■ 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を増やす場合の例を追加しました

・参考コード

kuc-arc-f/app11_3mongo_join2

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
　を結合して取得

....