pg-promise为select查询创建自定义过滤器

The function that I am working on is getting an input object that has 7 different key-values and each of them could be undefined or not. I want to filter my database based on those key-values that exists in the input. For example if only input.userID exists I want to run this query:

我正在处理的函数是获取一个输入对象，它有7个不同的键值，每个键值都可以是无定义的。我想根据输入中存在的键值来过滤我的数据库。例如，如果只输入。userID存在，我想运行这个查询:

db.query("...WHERE userID = ${userID}", {userID: input.userID});

else if both input.userID and input.startTime exist, I want to do this:

如果两个输入。标识和输入。我想做的是:

db.query("...WHERE userID = ${userID} and startTime= ${startTime}", {userID: input.userID, startTime: input.startTime});

What I have done is I created a params and keys object like this:

我创建了一个params和keys对象，如下所示:

if(input.userID) {
   keys.push('userID');
   params.push(input.userID);
   query = addFilterToTheQuery(query, 'userID', input.userID, filteredItemsCount);
   filteredItemsCount = filteredItemsCount +1;
}

addFilterToTheQuery is a simple function I implemented myself. I basically make 7 if cases. Then I have to use those keys and param values to pass to the query function in a way that might need another huge switch case code.

addFilterToTheQuery是我自己实现的一个简单函数。我做了7个if情况。然后，我必须使用这些键和参数值，以可能需要另一个大型开关用例代码的方式传递给查询函数。

Is this the only way to do this? Is there a better way to get rid of the redundancies in this code?

这是唯一的方法吗?是否有更好的方法消除代码中的冗余?

1 个解决方案

#1

Custom Type Formatting is the most suitable here.

自定义类型格式化在这里是最合适的。

For example, if we want to convert an object with properties - filter values, we could do it like this:

例如，如果我们想要转换一个具有属性-筛选值的对象，我们可以这样做:

var pgp = require('pg-promise')(/* initialization options */);

function FilterSet(filters) {
    if (!filters || typeof filters !== 'object') {
        throw new TypeError('Parameter \'filters\' must be an object.');
    }
    this._rawDBType = true;
    this.formatDBType = function () {
        var keys = Object.keys(filters);
        var s = keys.map(function (k) {
            return pgp.as.name(k) + ' = ${' + k + '}';
        }).join(' AND ');
        return pgp.as.format(s, filters);
    };
}

TEST

测试

var filter = new FilterSet({
    first: 1,
    second: 'two'
});

var test = pgp.as.format('WHERE $1', filter);

console.log(test);

This outputs:

这个输出:

WHERE "first" = 1 AND "second" = 'two'

If your filters are to be used as %value% with LIKE or ILIKE, then you would need to change your custom type accordingly.

如果您的筛选器使用LIKE或ILIKE的%值%，那么您需要相应地更改定制类型。

See related questions: 42, 49, 89, 90,

参见相关问题:42 49 89 90，

UPDATE

Below is the same example re-written for the latest pg-promise (version 8.x or newer):

下面是为最新的pg-promise(第8版)重写的相同示例。x或更新):

const pgp = require('pg-promise')(/* initialization options */);

class FilterSet {
    constructor(filters) {
        if (!filters || typeof filters !== 'object') {
            throw new TypeError('Parameter \'filters\' must be an object.');
        }
        this.filters = filters;
        this.rawType = true; // do not escape the result from toPostgres()
    }

    toPostgres(/*self*/) {
        // self = this
        const keys = Object.keys(this.filters);
        const s = keys.map(k => pgp.as.name(k) + ' = ${' + k + '}').join(' AND ');
        return pgp.as.format(s, this.filters);
    }
}

See Custom Type Formatting.

看到自定义类型格式。