如何在可选参数上查询postgres? [英] How to query postgres on optional params?
问题描述
我正在设置 REST
服务,并且使用 postgres
作为数据存储。我想知道如何设置 postgres
查询以使用可选参数。即:
I am setting up an REST
service and I am using postgres
as the data store. I want to know how to set up a postgres
query to use optional parameters. ie:
SELECT * from users
where hair_color = $1
and eye_color = $2
其中$ 1和$ 2来自请求:[req.body.hair_color,req.body.eye_color]
Where $1 and $2 come from the request: [req.body.hair_color, req.body.eye_color]
如果用户未通过 eye_color
,该怎么办?在这种情况下,我需要所有的眼睛颜色。我假设我不必在这里做一堆 if / else
语句。创建此查询的简洁方法是什么?
What if the user didn't pass in eye_color
and in that case I wanted all eye colors. I assume that I don't have to make a bunch of if/else
statements here. What is the concise way of creating this query?
推荐答案
在这里,我都做了 hair_color
和 eye_color
可选。 (传递与您的语言等效的NULL)。
Here I've made both hair_color
and eye_color
optional. (Pass your language's equivalent of NULL).
SELECT * from users where
($1 is null or hair_color = $1) and
($2 is null or eye_color = $2);
这篇关于如何在可选参数上查询postgres?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!