先說一下場景,產品中用到了簡單的表單構造器,開始提供了一系列的控制項,例如單行文本框、多行文本框、單選、覆選、時間等,之後你可以拖拽控制項自己組裝你想要的表單……網上有很多的表單構造器,這裡就不細說了,可能功能有多有少,但是原理類似。因為這種表單的這種實現方式,我們不單單要存儲表單的真實數據,還要存儲對 ...
先說一下場景,產品中用到了簡單的表單構造器,開始提供了一系列的控制項,例如單行文本框、多行文本框、單選、覆選、時間等,之後你可以拖拽控制項自己組裝你想要的表單……網上有很多的表單構造器,這裡就不細說了,可能功能有多有少,但是原理類似。因為這種表單的這種實現方式,我們不單單要存儲表單的真實數據,還要存儲對應表單的配置數據來渲染表單的樣子。這裡不細說配置相關的事情,細說一下表單數據的存儲和查詢。
對於一條記錄有很多共有屬性:主鍵ID、創建用戶ID、創建時間、表單ID(用於讀取表單配置)等一些業務數據,這裡著重說一下表單ID也就是下麵演示中提到的TableCode,我們這種存儲方式可以將業務上的多種表都存儲在現在的一張表中,所以必須要一個欄位來區分這條記錄存儲的具體是什麼信息,以方便在查詢時找到具體的數據;還有一些就是具體的表單項了。我們採用的是Mongodb存儲,不用想肯定是複雜結構了,數組或者鍵值對對象……也不知道什麼原因了,我們採用的是數組,這裡記作FormItems,FormItems列中存儲著所有的表單項,具體的看一下資料庫的存儲結構:
{ "_id" : "1", "CreateUserName" : "ddz1", "CreateDate" : "2018-02-07", "FormItems" : [ { "key" : "CarBrand", "value" : "紅旗" }, { "key" : "VehicleType", "value" : "H7" }, { "key" : "CarNum", "value" : "京A00001" }, { "key" : "PurchaseDate", "value" : "2019-01-01" } ], "TableCode" : "CarInfo" }
上面看到是一個縮減版的結構(為了方便演示),簡單的介紹一下:TableCode-表單名稱,FormItems數組中存儲著所有的表單項,key本來是唯一值,這裡僅僅為了演示所以如此,value就是具體的表單項的值了。查詢演示用到了三張表,第一張表如上,車輛信息表:車牌子、車型、牌照、購買日期等;車輛維修表:車輛ID、花費、內容;出車記錄表:車輛ID、收入、里程、備註。首先插入一些數據:
try{ db.t1.insertMany([{ "_id": "1", "CreateUserName": "ddz1", "CreateDate": "2018-02-07", "FormItems": [{ "key": "CarBrand", "value": "紅旗" }, { "key": "VehicleType", "value": "H7" }, { "key": "CarNum", "value": "京A00001" }, { "key": "PurchaseDate", "value": "2019-01-01" }], "TableCode": "CarInfo" },{ "_id": "2", "CreateUserName": "ddz1", "CreateDate": "2018-02-08", "FormItems": [{ "key": "CarBrand", "value": "紅旗" }, { "key": "VehicleType", "value": "H5" }, { "key": "CarNum", "value": "京A00002" }, { "key": "PurchaseDate", "value": "2019-02-01" }], "TableCode": "CarInfo" },{ "_id": "3", "CreateUserName": "ddz1", "CreateDate": "2018-02-09", "FormItems": [{ "key": "CarBrand", "value": "紅旗" }, { "key": "VehicleType", "value": "L5" }, { "key": "CarNum", "value": "京A00003" }, { "key": "PurchaseDate", "value": "2019-03-01" }], "TableCode": "CarInfo" }, { "_id": "4", "CreateUserName": "ddz1", "CreateDate": "2018-02-10", "FormItems": [{ "key": "CarBrand", "value": "長城" }, { "key": "VehicleType", "value": "哈弗H6" }, { "key": "CarNum", "value": "京A00004" }, { "key": "PurchaseDate", "value": "2018-03-01" }], "TableCode": "CarInfo" },{ "_id": "5", "CreateUserName": "ddz1", "CreateDate": "2018-02-09", "FormItems": [{ "key": "CarBrand", "value": "長城" }, { "key": "VehicleType", "value": "哈弗H5" }, { "key": "CarNum", "value": "京A00005" }, { "key": "PurchaseDate", "value": "2018-03-01" }], "TableCode": "CarInfo" },{ "_id": "6", "CreateUserName": "ddz1", "CreateDate": "2018-03-09", "FormItems": [{ "key": "CarBrand", "value": "長城" }, { "key": "VehicleType", "value": "哈弗H4" }, { "key": "CarNum", "value": "京A00006" }, { "key": "PurchaseDate", "value": "2018-03-01" }], "TableCode": "CarInfo" },{ "_id": "7", "CreateUserName": "ddz100", "CreateDate": "2018-06-06", "FormItems": [{ "key": "CarId", "value": "1" }, { "key": "Cost", "value": 100 }, { "key": "Contents", "value": "噴漆" }], "TableCode": "MaintenanceRecord" },{ "_id": "8", "CreateUserName": "ddz100", "CreateDate": "2018-08-06", "FormItems": [{ "key": "CarId", "value": "1" }, { "key": "Cost", "value": 25 }, { "key": "Contents", "value": "洗車" }], "TableCode": "MaintenanceRecord" },{ "_id": "9", "CreateUserName": "ddz101", "CreateDate": "2018-06-03", "FormItems": [{ "key": "CarId", "value": "2" }, { "key": "Cost", "value": 1560 }, { "key": "Contents", "value": "換輪胎" }], "TableCode": "MaintenanceRecord" },{ "_id": "10", "CreateUserName": "ddz102", "CreateDate": "2018-06-26", "FormItems": [{ "key": "CarId", "value": "3" }, { "key": "Cost", "value": 36 }, { "key": "Contents", "value": "爆胎" }], "TableCode": "MaintenanceRecord" },{ "_id": "11", "CreateUserName": "ddz103", "CreateDate": "2018-09-08", "FormItems": [{ "key": "CarId", "value": "3" }, { "key": "Cost", "value": 1630 }, { "key": "Contents", "value": "換加速器" }], "TableCode": "MaintenanceRecord" },{ "_id": "12", "CreateUserName": "ddz10", "CreateDate": "2018-06-06", "FormItems": [{ "key": "CarId", "value": "1" }, { "key": "Income", "value": 106 }, { "key": "Mileage", "value": 50 }, { "key": "Remarks", "value": "123" }], "TableCode": "DispatchRecord" },{ "_id": "13", "CreateUserName": "ddz11", "CreateDate": "2018-06-16", "FormItems": [{ "key": "CarId", "value": "1" }, { "key": "Income", "value": 250 }, { "key": "Mileage", "value": 100 }, { "key": "Remarks", "value": "123" }], "TableCode": "DispatchRecord" },{ "_id": "14", "CreateUserName": "ddz12", "CreateDate": "2018-06-16", "FormItems": [{ "key": "CarId", "value": "2" }, { "key": "Income", "value": 1000 }, { "key": "Mileage", "value": 630 }, { "key": "Remarks", "value": "2345sfgfg" }], "TableCode": "DispatchRecord" },{ "_id": "15", "CreateUserName": "ddz12", "CreateDate": "2018-08-16", "FormItems": [{ "key": "CarId", "value": "3" }, { "key": "Income", "value": 1213 }, { "key": "Mileage", "value": 569 }, { "key": "Remarks", "value": "12asdfasdfasdf3" }], "TableCode": "DispatchRecord" }]); }catch(e){ print(e); }View Code
這種存儲方式有一定的好處,但是弊端也很明顯:查詢、排序等很費勁,如果伺服器端以這樣的方式返回客戶端,客戶端還得解析FormItems,非常不友好(感覺自己在給自己找麻煩)……所以我就想看看能不能在所有的查詢操作之前,首先處理一下數據,將FormItems中的表單項都提到文檔的最外層和公共欄位一個層次,之後在進行查詢排序似乎就簡單點了,為了實現這個目標真是廢了九牛二虎之力,道路相當坎坷,經歷的時間也很長,不過最終好在找到一個例子解決了我們的問題:https://jira.mongodb.org/browse/SERVER-5947 。在此表示感謝,非常感謝……在這之後又遇到了一個問題就是:在關聯查詢時mongodb的處理方式是將所有的相關聯的數據存儲在一個屬性中,因為我們所有的“表”都存在這一個表中,所以關聯的也是這一張表,也就是關聯自己查詢,結果得到關聯的數據還是沒有處理之前的樣子,相同的數據最後的結構都不一樣,都不好意思返回客戶端,這個問題比之前的問題更費勁……不知道度過了多少天,我的一個同事說弄出來了,我就有點……所以我也想嘗試一下,最後廢了半天勁兒終於弄出來,不知道和同事弄的一樣不一樣,這裡只能吐槽mongodb你怎麼能這樣……看一下代碼:
db.getCollection('t1').aggregate([ {$addFields: { FormValueObj:{ $arrayToObject:{ $map: { input: "$FormItems", as: "field", in: ["$$field.key","$$field.value"] } } } } }, { $addFields:{ "FormValueObj._id":"$_id", "FormValueObj.CreateUserName":"$CreateUserName", "FormValueObj.CreateDate":"$CreateDate", "FormValueObj.TableCode":"$TableCode" } }, { $replaceRoot: { newRoot: "$FormValueObj" } }, { $lookup:{ from: "t1", localField: "_id", foreignField: "FormItems.value", as: "RelationData1" } }, { $addFields:{ RelationData1:{ $map: { input: "$RelationData1", as: "tr", in: { $arrayToObject:{ $map: { input:{ $concatArrays: [ [ {key:"_id",value:"$$tr._id"}, {key:"CreateUserName",value:"$$tr.CreateUserName"}, {key:"CreateDate",value:"$$tr.CreateDate"}, {key:"TableCode",value:"$$tr.TableCode"}, ], "$$tr.FormItems" ] }, as: "field", in: ["$$field.key","$$field.value"] } } } } } } } ])查詢語句
/* 1 */ { "CarBrand" : "紅旗", "VehicleType" : "H7", "CarNum" : "京A00001", "PurchaseDate" : "2019-01-01", "_id" : "1", "CreateUserName" : "ddz1", "CreateDate" : "2018-02-07", "TableCode" : "CarInfo", "RelationData1" : [ { "_id" : "7", "CreateUserName" : "ddz100", "CreateDate" : "2018-06-06", "TableCode" : "MaintenanceRecord", "CarId" : "1", "Cost" : 100.0, "Contents" : "噴漆" }, { "_id" : "8", "CreateUserName" : "ddz100", "CreateDate" : "2018-08-06", "TableCode" : "MaintenanceRecord", "CarId" : "1", "Cost" : 25.0, "Contents" : "洗車" }, { "_id" : "12", "CreateUserName" : "ddz10", "CreateDate" : "2018-06-06", "TableCode" : "DispatchRecord", "CarId" : "1", "Income" : 106.0, "Mileage" : 50.0, "Remarks" : "123" }, { "_id" : "13", "CreateUserName" : "ddz11", "CreateDate" : "2018-06-16", "TableCode" : "DispatchRecord", "CarId" : "1", "Income" : 250.0, "Mileage" : 100.0, "Remarks" : "123" } ] } /* 2 */ { "CarBrand" : "紅旗", "VehicleType" : "H5", "CarNum" : "京A00002", "PurchaseDate" : "2019-02-01", "_id" : "2", "CreateUserName" : "ddz1", "CreateDate" : "2018-02-08", "TableCode" : "CarInfo", "RelationData1" : [ { "_id" : "9", "CreateUserName" : "ddz101", "CreateDate" : "2018-06-03", "TableCode" : "MaintenanceRecord", "CarId" : "2", "Cost" : 1560.0, "Contents" : "換輪胎" }, { "_id" : "14", "CreateUserName" : "ddz12", "CreateDate" : "2018-06-16", "TableCode" : "DispatchRecord", "CarId" : "2", "Income" : 1000.0, "Mileage" : 630.0, "Remarks" : "2345sfgfg" } ] } /* 3 */ { "CarBrand" : "紅旗", "VehicleType" : "L5", "CarNum" : "京A00003", "PurchaseDate" : "2019-03-01", "_id" : "3", "CreateUserName" : "ddz1", "CreateDate" : "2018-02-09", "TableCode" : "CarInfo", "RelationData1" : [ { "_id" : "10", "CreateUserName" : "ddz102", "CreateDate" : "2018-06-26", "TableCode" : "MaintenanceRecord", "CarId" : "3", "Cost" : 36.0, "Contents" : "爆胎" }, { "_id" : "11", "CreateUserName" : "ddz103", "CreateDate" : "2018-09-08", "TableCode" : "MaintenanceRecord", "CarId" : "3", "Cost" : 1630.0, "Contents" : "換加速器" }, { "_id" : "15", "CreateUserName" : "ddz12", "CreateDate" : "2018-08-16", "TableCode" : "DispatchRecord", "CarId" : "3", "Income" : 1213.0, "Mileage" : 569.0, "Remarks" : "12asdfasdfasdf3" } ] } /* 4 */ { "CarBrand" : "長城", "VehicleType" : "哈弗H6", "CarNum" : "京A00004", "PurchaseDate" : "2018-03-01", "_id" : "4", "CreateUserName" : "ddz1", "CreateDate" : "2018-02-10", "TableCode" : "CarInfo", "RelationData1" : [] } /* 5 */ { "CarBrand" : "長城", "VehicleType" : "哈弗H5", "CarNum" : "京A00005", "PurchaseDate" : "2018-03-01", "_id" : "5", "CreateUserName" : "ddz1", "CreateDate" : "2018-02-09", "TableCode" : "CarInfo", "RelationData1" : [] } /* 6 */ { "CarBrand" : "長城", "VehicleType" : "哈弗H4", "CarNum" : "京A00006", "PurchaseDate" : "2018-03-01", "_id" : "6", "CreateUserName" : "ddz1", "CreateDate" : "2018-03-09", "TableCode" : "CarInfo", "RelationData1" : [] } /* 7 */ { "CarId" : "1", "Cost" : 100.0, "Contents" : "噴漆", "_id" : "7", "CreateUserName" : "ddz100", "CreateDate" : "2018-06-06", "TableCode" : "MaintenanceRecord", "RelationData1" : [] } /* 8 */ { "CarId" : "1", "Cost" : 25.0, "Contents" : "洗車", "_id" : "8", "CreateUserName" : "ddz100", "CreateDate" : "2018-08-06", "TableCode" : "MaintenanceRecord", "RelationData1" : [] } /* 9 */ { "CarId" : "2", "Cost" : 1560.0, "Contents" : "換輪胎", "_id" : "9", "CreateUserName" : "ddz101", "CreateDate" : "2018-06-03", "TableCode" : "MaintenanceRecord", "RelationData1" : [] } /* 10 */ { "CarId" : "3", "Cost" : 36.0, "Contents" : "爆胎", "_id" : "10", "CreateUserName" : "ddz102", "CreateDate" : "2018-06-26", "TableCode" : "MaintenanceRecord", "RelationData1" : [] } /* 11 */ { "CarId" : "3", "Cost" : 1630.0, "Contents" : "換加速器", "_id" : "11", "CreateUserName" : "ddz103", "CreateDate" : "2018-09-08", "TableCode" : "MaintenanceRecord", "RelationData1" : [] } /* 12 */ { "CarId" : "1", "Income" : 106.0, "Mileage" : 50.0, "Remarks" : "123", "_id" : "12", "CreateUserName" : "ddz10", "CreateDate" : "2018-06-06", "TableCode" : "DispatchRecord", "RelationData1" : [] } /* 13 */ { "CarId" : "1", "Income" : 250.0, "Mileage" : 100.0, "Remarks" : "123", "_id" : "13", "CreateUserName" : "ddz11", "CreateDate" : "2018-06-16", "TableCode" : "DispatchRecord", "RelationData1" : [] } /* 14 */ { "CarId" : "2", "Income" : 1000.0, "Mileage" : 630.0, "Remarks" : "2345sfgfg", "_id" : "14", "CreateUserName" : "ddz12", "CreateDate" : "2018-06-16", "TableCode" : "DispatchRecord", "RelationData1" : [] } /* 15 */ { "CarId" : "3", "Income" : 1213.0, "Mileage" : 569.0, "Remarks" : "12asdfasdfasdf3", "_id" : "15", "CreateUserName" : "ddz12", "CreateDate" : "2018-08-16", "TableCode" : "DispatchRecord", "RelationData1" : [] }查詢結果
你可能看到了查詢語句太長了,這還是最基本的,幸好3.4之後提供了視圖的功能,我們可以創建一個視圖,弄成我們想要的樣子:
db.createView("t1view","t1",[ {$addFields: { FormValueObj:{ $arrayToObject:{ $map: { input: "$FormItems", as: "field", in: ["$$field.key","$$field.value"] } } } } }, { $addFields:{ "FormValueObj._id":"$_id", "FormValueObj.CreateUserName":"$CreateUserName", "FormValueObj.CreateDate":"$CreateDate", "FormValueObj.TableCode":"$TableCode" } }, { $replaceRoot: { newRoot: "$FormValueObj" } } ])創建視圖
之後我們就是在視圖中查詢了
db.getCollection('t1view').aggregate([ {$match:{_id:"1"}}, { $lookup:{ from: "t1view", localField: "_id", foreignField: "CarId", as: "RelationData1" } } ])視圖查詢
/* 1 */ { "CarBrand" : "紅旗", "VehicleType" : "H7", "CarNum" : "京A00001", "PurchaseDate" : "2019-01-01", "_id" : "1", "CreateUserName" : "ddz1", "CreateDate" : "2018-02-07", "TableCode" : "CarInfo", "RelationData1" : [ { "CarId" : "1", "Cost" : 100.0, "Contents" : "噴漆", "_id" : "7", "CreateUserName" : "ddz100", "CreateDate" : "2018-06-06", "TableCode" : "MaintenanceRecord" }, { "CarId" : "1", "Cost" : 25.0, "Contents" : "洗車", "_id" : "8", "CreateUserName" : "ddz100", "CreateDate" : "2018-08-06", "TableCode" : "MaintenanceRecord" }, { "CarId" : "1", "Income" : 106.0, "Mileage" : 50.0, "Remarks" : "123", "_id" : "12", "CreateUserName" : "ddz10", "CreateDate" : "2018-06-06", "TableCode" : "DispatchRecord" }, { "CarId" : "1", "Income" : 250.0, "Mileage" : 100.0, "Remarks" : "123", "_id" : "13", "CreateUserName" : "ddz11", "CreateDate" : "2018-06-16", "TableCode" : "DispatchRecord" } ] }視圖查詢結果
這樣在操作就簡單多了,還避免了上面的第二個問題,