处理复杂：如果不进行PHP查询生成器条款 [英] Handling Complex WHERE clauses with a PHP Query Builder

问题描述

有几种风格的ActiveRecord查询生成器的图书馆在那里。有些是<一href="http://stackoverflow.com/questions/1049617/complex-where-clauses-using-the-php-doctrine-orm">stand建成框架单独，部分客房 的。但是，他们真的有WHERE和HAVING子句，当涉及到复杂的SQL麻烦。设置其他数据库放在一边 - 我想拿出一个MySQL和PostgreSQL兼容WHERE（）方法，可以解决目前的这些方法挫折

There are several ActiveRecord styled query builder libraries out there. Some are stand alone and some come built into frameworks. However, they really have trouble with WHERE and HAVING clauses when it comes to complex SQL. Setting other databases aside - I am trying to come up with a MySQL and PostgreSQL compatible WHERE() method that could fix these current method downfalls.

下面是一长串的观点和实例展示的最好我能走了这么远了。不过，我似乎无法来解决的所有的用例的，我觉得我的部分解决方案是马虎不得。任何人都可以用的东西，解决所有这些问题的回答不仅会回答这个问题 - 但将负责修复已捕杀PHP实现了好几年的一个问题。

What follows is a long list of ideas and examples showing the best I could come up with so far. However, I can't seem to solve all of the use cases and I feel my partial solution is sloppy. Anyone that can answer with something that solves all of these problems will not only answer this question - but a will be responsible for fixing a problem that has hunted PHP implementations for several years now.

常用的运算符

    =	Equal
    <>	Not Equal
    >	Greater Than
    <	Less Than
    >=	Greater Than Or Equal
    <=	Less Than Or Equal
    BETWEEN between values on right 
    NOT logical NOT 
    AND logical AND 
    OR	logical OR

示例Where子句

SELECT ... FROM table...
    WHERE column = 5
    WHERE column > 5
    WHERE column IS NULL
    WHERE column IN (1, 2, 3)
    WHERE column NOT IN (1, 2, 3)
    WHERE column IN (SELECT column FROM t2)
    WHERE column IN (SELECT c3 FROM t2 WHERE c2 = table.column + 10)
    WHERE column BETWEEN 32 AND 34
    WHERE column BETWEEN (SELECT c3 FROM t2 WHERE c2 = table.column + 10) AND 100
    WHERE EXISTS (SELECT column FROM t2 WHERE c2 > table.column)

有许多共同的ActiveRecord格式的where（）子句中使用不同的电流的库。

There are many common ActiveRecord formats that the where() clause uses in the different current libraries.

$this->db->where(array('session_id' => '?', 'username' => '?'));
$this->db->fetch(array($id, $username));

// vs with is_int($key)
$this->db->where(array('session_id', 'username'));
$this->db->fetch(array($id, $username));

// vs with is_string($where)
$this->db->where('session_id', '?');
$this->db->where('username');
$this->db->fetch(array($id, $username));

// vs with is_array($value)
$this->db->where('session_id', '?');
$this->db->where('username', array('Sam', 'Bob'));
$this->db->fetch(array($id));

下面是最终的格式，我有这么远。它应该处理分组（...）AND（...）以及prepared声明界PARAMS（AMP？;：名）。

Here is the final format that I have so far. It should handle grouping (...) AND (...) as well as prepared statement bound params ("?" & ":name").

function where($column, $op = '=', $value = '?', $group = FALSE){}


// Single line

$this->db->where('column > 5');
$this->db->where('column IS NULL');

// Column + condition

$this->db->where('column', '=');
// WHERE column = ? 	(prepared statement)
$this->db->where('column', '<>');
// WHERE column <> ?    (prepared statement)

// Column + condition + values

$this->db->where('column', '=', 5);
// // WHERE column = 5
$this->db->where('column', 'IN', '(SELECT column FROM t2)');
// WHERE column IN (SELECT column FROM t2)
$this->db->where('column', 'IN', array(1,2,3));
// WHERE column IN (1, 2, 3)
$this->db->where('column', 'NOT IN', array(1,2,3));
// WHERE column NOT IN (1, 2, 3)

// column + condition + values + group
$this->db->where(
    array(
    	array('column', '<', 20), 
    	array('column', '>', 10)
    ),
    NULL,
    NULL,
    $group = TRUE
);
// WHERE (column < 20 AND column > 10)

：更新：

在我的问题过程中，我逐渐认识到，WHERE和HAVING条件只得到更复杂更深你去。试图抽象甚至80％的功能，将导致巨大的图书馆只是WHERE和HAVING。正如比尔指出，这仅仅是不合理的像PHP脚本语言。

Over the course of my question I came to realize that WHERE and HAVING conditions only get more complex the deeper you go. Trying to abstract even 80% of the features would result in a massive library just for WHERE and HAVING. As Bill points out, that just isn't reasonable for a scripting language like PHP.

解决方法就是手工写您的查询的WHERE部分。只要你使用在你的栏目，你可以使用相同的WHERE查询Postgre，SQLite和MySQL的，因为他们使用几乎相同的SQL语法。（对于MySQL，你必须 str_replace（）函数他们用tick`）。

The solution is just to hand craft the WHERE portion of your query. As long as you use " around your columns you can use the same WHERE query in Postgre, SQLite, and MySQL since they use almost the same SQL syntax. (For MySQL you must str_replace() them with a tick`).

有那么一刻，抽象伤害比它的帮助，那里的条件是这样的一个地方。

There comes a point where abstraction hurts more than it helps, WHERE conditions are one such place.

推荐答案

我的工作相当多的 Zend_Db的库，其中包括一个PHP的类构造SQL查询的。我决定踢的努力来处理，其中和 HAVING 条款，有以下几个原因每一个可以想象的SQL语法：

I worked quite a bit on the Zend_Db library, which includes a PHP class for constructing SQL queries. I decided to punt on trying to handle every imaginable SQL syntax in WHERE and HAVING clauses, for several reasons:

• PHP是一种脚本语言，解析和编译code在每次请求（除非你使用一个字节code高速缓存）。所以PHP环境是笨重code库敏感的 - 更何况比Java或C＃或Python或你有什么。因此，它是一个高优先级，以保持库精益，我们可以。

• PHP is a scripting language that parses and compiles code on every request (unless you use a bytecode cache). So the PHP environment is sensitive to bulky code libraries -- more so than Java or C# or Python or what have you. It's therefore a high priority to keep libraries as lean as we can.

所有 Zend_Db的库我从事的是约2000行PHP code。相比之下，Java的Hibernate的是118K线code的顺序。但是，这与其说是一个问题，因为Java库是precompiled并没有在每次请求被加载。

All of the Zend_Db library I worked on was about 2,000 lines of PHP code. By contrast, Java Hibernate is on the order of 118K lines of code. But that's not so much of an issue since a Java library is precompiled and doesn't have to be loaded on every request.

SQL EX pressions遵循生成语法是更紧凑，更易于阅读和维护，任何你给的基于PHP的建设。学习SQL EX pression语法是远远比学习，可以模拟它的API更加容易。你最终会支持一个简化的语法。否则你开始这种方式，并发现自己被强迫用户社区为特性蔓延，直到你的API是unusably复杂的。

SQL expressions follow a generative grammar that is more compact, and easier to read and maintain that any of the PHP-based construction you showed. Learning the SQL expression grammar is far easier than learning an API that can simulate it. You end up supporting a "simplified grammar." Or else you start out that way, and find yourself coerced by your user community into Feature Creep until your API is unusably complex.

要调试使用这种API的应用程序，你必然需要进入到最后的SQL EX pression，所以它是关于的leakiest 你可以有抽象。

To debug an application that used such an API, you'd inevitably need access to the final SQL expression, so it's about the leakiest abstraction you can have.

要使用一个基于PHP的界面，用于SQL EX pressions是，这有助于code-完成智能编辑器和IDE的唯一优势。但是，当如此多的运营商和操作数的使用字符串常量，比如＆GT; ='，你破坏任何code-完成智能

The only advantage to using a PHP-based interface for SQL expressions would be that it assists code-completion in smart editors and IDE's. But when so many of the operators and operands use string constants like '>=', you spoil any code-completion intelligence.

---

更新：我刚刚看了一个好的博客文章一个告别奥姆斯的。作家阿尔Cortesi的，建议使用 SQL防爆pression语言Python中的 SQLAlchem​​y的。语法糖和操作符重载是在Python标准（但不支持PHP）使这是一个非常有效的查询产生的解决方案。

update: I just read a good blog article "A Farewell to ORMs." The writer, Aldo Cortesi, suggests using the SQL Expression Language in Python's SQLAlchemy. Syntactic sugar and operator overloading that is standard in Python (but not supported in PHP) make this a very effective query-generating solution.

您也可以看看Perl的DBIx ::类，但它最终被pretty的难看。

You might also look at Perl's DBIx::Class, but it ends up being pretty ugly.

这篇关于处理复杂：如果不进行PHP查询生成器条款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！