如何根据用户的选择动态生成SQL查询? [英] How to dynamically generate SQL query based on user's selections?

问题描述

我需要创建一个GUI，用户可以使用该GUI选择几个属性，这些属性将用于查询数据库以找到合适的人.我正在寻找有关如何根据用户的选择动态生成数据库查询的想法.

I need to create a GUI, using which users can select several attributes which will be used to query the database to find suitable persons. I'm looking for ideas how to dynamically generate the database query according to user's choices.

查询将包含多个字段，但为便于理解，我仅以以下三个示例为例:

Query will contain several fields, but to get the idea I will include only three of those below as an example:

• 职业-可以有0到n个职业字符串.如果给出了职业字符串，则其中之一必须匹配.

• Occupation - there can be 0 to n occupation strings. If occupation strings are given, one of them have to match.

年龄-年龄可以指定为:

1. 完全匹配(30)
2. 范围(例如30-40)
3. 小于一个值(-40)
4. 多于一个值(30-)

年龄参数在查询中是可选的.另外，用户可以指定年龄是否为必填参数.如果不需要，并且一个人没有年龄是他/她的个人资料，则该人的年龄标准将被忽略.

Age parameter is optional in the query. In addition, user can specify whether age is a required parameter. If it's not required, and a person does not have age is his/her profile, age criteria is ignored for this person.

• 身高-与年龄相似

• Height - similar as age

查询示例:

没有给出标准:

select * from persons

只有职业:

select * from persons where occupation = 'dentist'

已获得多项职业:

select * from persons where (occupation = 'dentist' or occupation = 'engineer')

年龄被赋予大于值的价值，并且必须存在于个人资料中:

Age has been given as a greater than value, and it's required to exist on person's profile:

select * from persons where age >= 30

高度已指定为范围，并且不需要在个人资料中存在高度:

Height has been given as a range, and it's not required to exist on person's profile:

select * from persons where (height is null or (height >= 30 and height <= 40))

不同标准的组合:

select * from persons where occupation = 'dentist' and age >= 30 and (height is null or (height >= 30 and height <= 40))

我已经实现了能够以字符串形式生成查询的代码，但是它当然不是很漂亮.我正在寻找实现这一目标的最有效，最漂亮的方法.

I have already implemented code which is capable of generating queries as strings, but it certainly is not too pretty. I'm looking for ideas what would be the most efficient and prettiest way to achieve this.

推荐答案

尝试类似

Try something like Zend_Db_Select. It provides a (fluent) interface for generating queries and handles the syntax creation for you, e.g.

$select = $db->select();
$select->from( /* ...specify table and columns... */ )
       ->where( /* ...specify search criteria... */ )
       ->where( /* ...specify other criteria... */ )
       ->order( /* ...specify sorting criteria... */ );

在我最近的一个项目中，我有一个类似的要求，在该项目中，用户拥有一个配置文件，该文件包含自动应用于数据库中某些表的过滤条件，从而限制了用户看到的内容，例如，限制固定客户的产品但仍然允许通过GUI进行动态过滤，例如产品类别.

I had a similar requirement in one my recent projects where users have a configuration file containing filter criteria that are automatically applied to certain tables in the database, thus limiting what the users are allowed to see, e.g, limit Products by fixed Customer but still allow dynamic filtering through GUI, e.g. for a Product category.

我解决了这一问题，方法是让模型返回基本查询，然后通过装饰器运行该基本查询，该装饰器将应用用户在其配置中具有的所有条件，例如(仿造代码).

I solved it by having my model return a base query and then running this base query through a decorator that would apply all criteria the user has in his config, so for instance (faux code).

    request = Request->getParams()            // selection criteria set from GUI
    sql     = Products->getBaseQuery(request) // basic query for requested View
    sql     = Decorator->applyUserConfig(sql) // custom fixed user filter
    results = sql->execute()

这篇关于如何根据用户的选择动态生成SQL查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！