如何将用户可自定义的高级搜索转换为 SQL 查询？
我意识到这相当抽象，但我想做一些与 Remember The Milk 的智能列表类似的事情： http://www.rememberthemilk.com/help/answers/search/advanced.rtm
我有一组经过标记、分类等的用户故事，我发现拥有用户可自定义的“视图”或“智能列表”来返回特定的集合将具有巨大的优势的故事。这使得使用该应用程序的工作流程非常灵活，并且对于许多不可预见的场景非常有用。
我现在对简单的 OR 查询没有任何问题：
WHERE tag = "tag1" OR tag = "tag2" OR category = "category1" OR category = "category2"
但这实际上不如混合 AND/OR 条件有用：
WHERE (tag = "tag1" OR tag = "tag2") AND category = "category1"
因此，明显的问题是：
- 嵌套条件
- 混合 AND/同一查询中的 OR 条件
- 使这种“查询抽象”语法或多或少具有人类可读性、易于存储/解析、URL 查询字符串友好等。
更糟糕的是，可以在 where 子句中很好地处理简单的 OR 条件，但是 AND（复合条件）似乎需要每个条件都有一个额外的连接，我不知道如何在可持续的代码中组织它。
我正在使用 PHP/MySQL，但这似乎不是特定于平台的。
总而言之，我希望找到一种可持续的方法来解析人类生成的条件序列并将其转换为 SQL。
I realize this is fairly abstract, but I'd like to do something not unlike Remember The Milk's smart lists:
http://www.rememberthemilk.com/help/answers/search/advanced.rtm
I have a set of user stories that are tagged, categorized, etc., and I'm seeing that it would be of huge advantage to have user-customizable "views" or "smart lists" to return a specific set of stories. This makes the workflow of using the app quite flexible and useful for many unforeseeable scenarios.
I have no problem right now with simple OR queries:
WHERE tag = "tag1" OR tag = "tag2" OR category = "category1" OR category = "category2"
But that's really not as useful as mixed AND/OR conditions:
WHERE (tag = "tag1" OR tag = "tag2") AND category = "category1"
So, the apparent issues are:
- Nested conditions
- Mixing AND/OR conditions in the same query
- Making this "query abstraction" syntax more-or-less human readable, easily stored/parsed, URL query string friendly, etc.
To make matters worse, simple OR conditions can be handled quite well within a where clause, but AND (compound conditions) seem to require an additional join per condition, and I'm lost how to organize this in sustainable code.
I'm using PHP/MySQL, but this doesn't seem to be so platform specific.
To summarize, I'd like to find a sustainable approach to parsing and converting a human-generated sequence of conditions into SQL.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
抽象的问题应该有抽象的答案。 :-) 唉，我将给你举一个例子来说明另一个软件如何处理这个问题，因为我不太懂哲学。
Request Tracker 是一个历史悠久的浏览器 UI 故障单系统。它具有非常强大的票证搜索机制，知道如何读取与票证关联的每个可能的字段，并让您通过 Web 界面有效地构建整个 SQL 查询。
我找不到搜索页面的任何屏幕截图，所以也许您可能想查看在线RT 演示。登录，然后从窗口左上角的菜单中选择“票证”，然后选择“新搜索”。
或者更好的是，要查看如何在此搜索工具上构建所有搜索，请单击主演示屏幕（登录后立即显示）右侧列表中的队列。当您显示一组门票时，请点击窗口顶部的“门票”。 （不是出现的菜单选项。只需单击票证即可。）
此处的查询生成器在右侧显示查询的结构，并允许您使用左侧的控件混合搭配要包含在其中的内容。如果您是“高级”用户，并且想要手动编写自己的查询，或者清理 Web UI 出现问题的内容，则可以单击顶部的“高级”链接，然后编辑一些看起来非常像 SQL 的内容。
我不会讨论如何加入其他表。我回答这个问题就好像这是一个 UI/UX 问题，而不是数据建模问题。 :-)
An abstract question deserves an abstract answer. :-) Alas, I'm going to give you an example of how another piece of software handles this problem, because I'm not very philosophical.
Request Tracker is a long-established browser-UI trouble ticketing system. It has a tremendously powerful ticket search mechanism that knows how to read every possible field associated with a ticket, and lets you effectively craft your entire SQL query via the web interface.
I couldn't find any screenshots of the search page, so perhaps you might want to look at the online demo of RT. Log in, then from the menu at the top-left of your window, select "Ticket" then "New Search".
Or better yet, to see how ALL searches are built on this search tool, click on a queue from the list on the right-hand side of the main demo screen (which shows up right after you log in). When you have a set of tickets showing, click on "Tickets" at the top of your window. (Not a menu option that comes up. Just click Tickets.)
The query builder here shows the structure of your query on the right, and lets you mix-and-match things to include in it using controls on the left. And if you're an "advanced" user and want to write your own query manually, or clean up something that the web UI is having problems with, you can click the Advanced link at the top and edit something that looks remarkably like SQL.
I won't get in to how to JOIN other tables in. I'm answer this as if it's a UI/UX question, not a data modelling one. :-)