node.js - 如何使用expressjs加入Firebase中的两个集合？

Question

我正在使用 Node-express JS 和 Firebase 数据库编写 Rest API。在 Firebase 中，我有两个集合，例如:

    Bin (binName, binLocaton, hardwareId)

    BinsInformation (date, payloadFields { hardwareId, levle })

如何根据字段 hardwareId 连接这两个集合？

I would like to join to collections like two tables in MySQL. 

Ex. "SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id "
 Like the above MySQL query i would like join my two collections "Bin" and "BinInformation".

如果我加入具有特定 id 的单个文档，它会给出正确的输出。但是当我尝试加入两个集合中的所有文档时 使用单独的查询给出“异步错误”。

这是我的收藏:

垃圾箱:

[
    {
        AreaID: "ZYwQHIrZPEDd359e6WRf"
        Capacity: "123"
        Latitude: "17.658745"
        LocationName: "testing"
        Longitude: "78.9874545"
        hardwareid: "4321"
    },
    {
        AreaID: "ZYwQHIrZPEDd359e6WRf"
        Capacity: "123"
        Latitude: "17.658745"
        LocationName: "testing"
        Longitude: "78.9874545"
        hardwareid: "5432"
    }
]

Bin信息:

[
    {
        date: September 24, 2018 at 12:00:00 AM UTC+5:30,
        payload_fields: { 
            hardwareid: "4321"
            level : 60
        }
    },
    {
        date: September 24, 2018 at 12:00:00 AM UTC+5:30,
        payload_fields: { 
            hardwareid: "5432"
            level : 23
        }
    }
]

这里我需要获取具有相应 BinInformation 的所有 Bins 。我的代码是这样的

app.get('/twoColectionJoin', asyncHandler( async (req, res, next) => {

    console.log('await');
    try {

        let allDocs = [];

        const snapshot = await db.collection('Bins').get();

        let i = 0;
         snapshot.forEach( async (doc) => {
            let dummyDoc = doc.data();
            const details = await getBinDet(dummyDoc.hardwareid);
            dummyDoc.id = doc.id;  
            dummyDoc.det = details;              
            allDocs.push(dummyDoc);
            i++;
            console.log(allDocs);
        });

            res.status(200).send(allDocs);


    }
    catch(e){

        next(e);
    }
}) );


async function getBinDet(hardwareid){
    let dummyDoc = {};
    return new Promise(function(resolve, reject){

        try{

            setTimeout(function(){ 
             db.collection('BinsInformation')
             .where('payload_fields.hardwareid', '==', hardwareid)
             .limit(1).get()
                .then(snap => {                 
                    snap.forEach(element => {

                        //return element.data();
                        dummyDoc = element.data();

                    });   

                    resolve(dummyDoc);
                })
                .catch(err2 => {

                        reject(err2);
                });
            }, 300);

        }catch(err){

            console.log(err);
        }
    });
}

预期输出:

[
    {
        AreaID: "ZYwQHIrZPEDd359e6WRf"
        Capacity: "123"
        Latitude: "17.658745"
        LocationName: "testing"
        Longitude: "78.9874545"
        hardwareid: "4321",
        det: {
            date: September 24, 2018 at 12:00:00 AM UTC+5:30,
            payload_fields : { 
                hardwareid: "4321"
                level : 60
            }
        }
    },
    {
        AreaID: "ZYwQHIrZPEDd359e6WRf"
        Capacity: "123"
        Latitude: "17.658745"
        LocationName: "testing"
        Longitude: "78.9874545"
        hardwareid: "5432",
        det: {
            date: September 24, 2018 at 12:00:00 AM UTC+5:30,
            payload_fields: { 
                hardwareid: "5432"
                level : 23
            }
        }
    }
] 

但结果输出是:[]

Answer 1

将 SQL 查询转换为 Firebase 查询并不是 1:1 的。如果您尝试在 Firebase 上执行典型的 SQL 查询，那么 Firebase 是错误的数据库 (NoSQL)，不适合您。

使用您上面发布的数据结构，一个完整的示例如下:

const express = require("express");
const admin = require("firebase-admin");
const serviceAccount = require("./serviceAccountKey.json");

const app = express();

admin.initializeApp({
  credential: admin.credential.cert(serviceAccount),
  databaseURL: "https://example.firebaseio.com"
});

const db = admin.firestore();

async function joinsCollectionsHandler(req, res) {
  const binsRef = await db.collection("bins").get();
  const binData = binsRef.docs.map(doc => doc.data());

  const binsInfoRef = await db.collection("bin-information").get();
  const binInfoData = binsInfoRef.docs.map(doc => doc.data());

  const data = binData.map(bin => {
    const { hardwareId } = bin;
    const det = binInfoData.filter(
      doc => doc.payloadFields.hardwareId === hardwareId
    );
    return { ...bin, det };
  });
  res.json(data);
}

app.get("/twoColectionJoin", joinsCollectionsHandler);

app.listen(3000, () => console.log("Started on 3000"));

最终结果是:

[
    {
        "latitude": "17.658745",
        "locationName": "testing",
        "longitude": "78.9874545",
        "areaId": "ZYwQHIrZPEDd359e6WRf",
        "hardwareId": "5432",
        "capacity": "123",
        "det": [
            {
                "date": "2018-09-23T18:30:00.000Z",
                "payloadFields": {
                    "level": 23,
                    "hardwareId": "5432"
                }
            }
        ]
    },
    {
        "capacity": "123",
        "areaId": "ZYwQHIrZPEDd359e6WRf",
        "latitude": "17.658745",
        "hardwareId": "4321",
        "locationName": "testing",
        "det": [
            {
                "date": "2018-09-23T18:30:00.000Z",
                "payloadFields": {
                    "level": 60,
                    "hardwareId": "4321"
                }
            }
        ]
    }
]

我已经在本地测试过这个并且工作正常。如果您有任何疑问，请告诉我。