时间戳

首页 > 解决方案 > TYPEORM 过滤复杂 JSON 的数据

node.js - TYPEORM 过滤复杂 JSON 的数据

问题描述

我正在使用 typeorm 从 Postgres 检索以下数据，并且我想过滤这些数据：

[{ "corporateid": 1001, "customerdetail": [{ "customerid": 1, 
"customertype": "CustType1", "products": [{ "productid": 1, "productcode": 
9000, "currencydetailss": [{ "currencydetailsid": 1, "ratetype": "Cash", 
"basecurrencyidFk": { "currencyid": 2, "currencyisocode": "IS0-9901", 
"currencyname": "INR" }, "currencyrates": [{ "currencyratesid": 1, 
"buyrate": 10.2, "sellrate":
11.2, "spotsell": 10.9 }, { "currencyratesid": 2, "buyrate": 98.2, 
"sellrate": 99.2, "spotsell": 98.9 }, { "currencyratesid": 3, "buyrate": 
11.2, "sellrate": 12.2, "spotsell": 11.9 }] }] }] }, { "customerid": 5, 
"customertype": "CustType5", "products": [] }, { "customerid": 4, 
"customertype": "CustType4", "products": [] }] }]

我尝试了什么：

const details = await conn .getRepository(corporate) .find({ where: [ { corporateid: "1001" ,'customerdetail.customerid'= 2} ] });

我还使用 where 条件尝试了 LeftJoin 和 InnerJoin：where: {corporateid: "1001",'customerdetail.customerid'= 2}

我尝试了多种其他方式。

关于如何通过传递可选参数从 JSON 中过滤数据的任何帮助？例如：我可能只是通过corporateid 或'corporateid 和customerid' 和许多类似的组合。

标签： node.jspostgresqlfiltertypeorm

解决方案

所以我可以通过下面的代码来做到这一点，以防有人需要它：

let requestQueryParameters: { [key: string]: string } = {
       "c2": 'CustType3',
       "c3": '2'
   };


let queryCriteriaPlaceHolders: { [key: string]: string } = {
   "c2": "t2.c2= :c2",
   "c3": "t4.c1= :c1",
};

const query = await conn
           .getRepository(t1)
           .createQueryBuilder("t1")
           .leftJoinAndMapMany("t1.t2", t2, "t2", "t1.id = t2.id_fk")
           .leftJoinAndMapMany("t2.t3", t3, "t3", "t3.id_fk=t2.id")
           .leftJoinAndMapMany("t3.t4", t4, "t4", "t4.id_fk=t3.id")
           .leftJoinAndMapOne('t4.c1', t5, 't5', 't4.c1=t5.c1')
           .where("t1.id= :id", { id: 123 })
           .andWhere("t4.c2= :c2", { c2: 456 });

       Object.getOwnPropertyNames(requestQueryParameters).forEach(param => {
           let paramValue = requestQueryParameters[param];
           console.log("Key: " + param + " Value: " + paramValue);
           let reqObjLiteral = {} as any;
           reqObjLiteral[param] = paramValue
           query.andWhere(queryCriteriaPlaceHolders[param], reqObjLiteral)
       });

query.getMany();

推荐阅读