在过去的一周里，我发现自己有两次需要在某些API中创建或更改数据库搜索功能才能工作。在做这项工作时，我发现了一些使这个过程更容易的技术。与ThePragmaticProgrammer一致，我发现自己在探索DRY原则（不要重复自己），并研究代码生成（这次是SQL）——基本上是为了利用我与生俱来的懒惰并完成我的工作更容易。
我想我应该分享一些我自己发现的原则，因为我没有阅读太多关于这个主题的信息。其中一些对于某些读者或使用更高级抽象层的读者来说可能是初级的（我怀疑DB_DataObject可能会做很多），但希望这些信息可以对其他人（包括我自己）有用。
1。将单个条件和连接存储在类数组中
确定您希望在搜索中允许的所有不同类型的条件和连接，并将它们存储在一个类数组中，WHERE和JOIN子句各一个。例如：
var $criteria = array(
'title' => 'title = ?',
'author' => 'user.username = ?',
'keyword' => 'MATCH(body) AGAINST (?)',
'date' => 'when_modified BETWEEN ? AND ?',
'category' => 'category IN (!)'
);
var $join = array(
'author' => 'INNER JOIN user ON user.id = article.author_id',
'category' => 'INNER JOIN category ON category.id = article.category'
);
这会将您的所有条件放在一个地方，稍后将允许您循环遍历每组条件以构建您的SQL。稍后会详细介绍。
2。将搜索条件作为关联数组传递
第一个技巧是将您的搜索条件作为关联数组传递给您的方法。数组的键应该是您希望搜索的字段（或元字段），值是用于比较的实际值。例如，假设我想搜索“作者”字段和标题字段；然后我可能会像下面这样传递一个关联数组：
$criteria = array('title' => 'Cgiapp', 'author' => 'weierophinney');
通过将条件作为关联数组传递，您可以为系统构建一些自动灵活性。您可以检查某些键是否存在，并仅根据存在的内容构建WHERE子句。您还可以设置仅在传递键（或该键的值超出特定范围）时才覆盖的默认值。
此外，如果您将密钥设置为与上面(1)中的密钥相匹配，您将能够遍历这些密钥以确定要使用的条件。下面将显示一个完整的示例。
3。设置默认条件
如(2)中所述，您可能需要一些默认条件。例如，您可能只想选择活动记录——除非传递了inactive键。或者您可能希望通过引入限制和偏移量来降低对数据库服务器的性能影响——因此您将为offset和limit指定默认值。另一个可能的选择是确定排序顺序.
当需要为您的API同时执行列表和搜索功能时，默认值会特别方便；您可以使用相同的方法，但在列出时，您只需使用默认值而不是传递搜索条件。
最有可能保留这些默认值的地方再次出现在类数组中。您可能会注意到我们开始在这些数组中出现一些重叠——我们有一个条件数组、一个连接数组，现在一个用于默认值。如何组合它们（并为日期、偏移量、限制和排序添加默认约束）：
var $criteria = array(
'title' => array(
'where' => 'title => ?'
),
'author' => array(
'where' => 'user.username = ?',
'join' => 'INNER JOIN user ON user.id = article.author_id'
),
'keyword' => array(
'where' => 'MATCH(body) AGAINST (?)'
),
'date' => array(
'where' => 'when_modified BETWEEN ? AND ?',
'default' => array(date('Y-m-d', strtotime('-1 week')), date('Y-m-d'))
),
'category' => array(
'where' => 'category IN (!)'
),
'offset' => array(
'default' => 0
),
'limit' => array(
'default' => 25
),
'sort' => array(
'default' => 'title ASC'
)
);
4。在处理期间将条件和值存储在数组中
当您解析标准的关联数组时，您将通过从$constraint数组（或’where’$constraint数组的键）。通常这些语句如下：
author=?title=?类别IN(!)MATCH(关键字)AGAINST(?)
将每个WHERE子句作为单独的条目存储在本地$where数组中。此外，如果您使用占位符（您确实使用占位符，对吧？），将它们推入一个单独的$params数组：
$where = array();
$params = array();
// A simple example
if (!empty($criteria['author'])) {
$where[] = 'user.username = ?';
$params[] = $criteria['author'];
}
// More complex, but more automated; $search is the passed in associative
// array of search criteria
foreach ($this->criteria as $field => $info) {
if (!empty($search[$field])) {
$where[] = $info['where'];
if (!is_array($search[$field]) {
$params[] = $search[$field];
} else {
foreach ($search[$field] as $value) {
if (is_scalar($value) {
$params[] = $value;
}
}
}
}
}
然后，当你完成后，你可以通过内爆数组来构建你的WHERE子句：
$whereSQL = implode(' AND ', $where);
5。跟踪JOIN
在我工作的地方，我们经常对链接表执行JOIN操作。有时，多组条件需要相同的JOIN，有时不使用连接，有时我们需要多个JOIN。明显的挑战是您只想执行每种类型的一个JOIN，并且您不想执行不必要的JOIN。
您可以通过定义一个数组来简化情况，该数组跟踪已执行的JOIN。然后，有一个方法，您将JOIN语句传递给该方法，并让该方法尝试查找；如果在数组中找不到匹配项，则添加它。
然后，当您通过条件进行解析时，只要需要JOIN就调用该方法。例如：
// Private class array property to keep track of joins
var $_joined = array();
// Private function to keep track of JOINs
function _joinTables($join)
{
if (in_array($join, $this->_joined)) {
return true;
}
$this->_joined[] = $join;
return true;
}
// in search method:
if (!empty($info['join'])) {
$this->_joinTables($info['join']);
}
6。仅允许检索记录数
出于分页或提供信息的目的，您通常需要计算匹配的记录数。您可以使用相同的搜索方法通过检查搜索数组中的COUNT_ONLY键来生成此信息：
$select = 'id, user.username, title, body, category';
if (isset($search['COUNT_ONLY'])) {
$select = 'COUNT(id)';
}
如果您正在使用LIMIT子句，您可能还想在同一步骤中覆盖LIMIT子句($limitSql='';).
7.放在一起
那么，所有这些放在一起会是什么样子？这是一个例子：
class Articles
{
// Array of criteria; each element key points to an array that contains one
// or more of the following keys: 'where', 'join', and 'default'.
var $criteria = array(
'title' => array(
'where' => 'title => ?'
),
'author' => array(
'where' => 'user.username = ?',
'join' => 'INNER JOIN user ON user.id = article.author_id'
),
'keyword' => array(
'where' => 'MATCH(body) AGAINST (?)'
),
'date' => array(
'where' => 'when_modified BETWEEN ? AND ?',
'default' => array(date('Y-m-d', strtotime('-1 week')), date('Y-m-d'))
),
'category' => array(
'where' => 'category IN (!)'
),
'offset' => array(
'default' => 0
),
'limit' => array(
'default' => 25
),
'sort' => array(
'default' => 'title ASC'
)
);
// Array for keeping track of JOINs
var $_joined = array();
// Function for adding JOINs to the stack
function _joinTables($join)
{
if (in_array($join, $this->_joined)) {
return true;
}
$this->_joined[] = $join;
return true;
}
// Actual search method
function search($search)
{
$where = array();
$join = array();
$params = array();
// Get criteria
foreach ($this->criteria as $field => $info) {
if (!empty($search[$field])) {
// Get WHERE clause, if necessary
if (!empty($info['where'])) {
$where[] = $info['where'];
// Get placeholder values:
$value = $search[$field];
if (is_scalar($value)) {
$params[] = $value;
} elseif (is_array($value)) {
// Sometimes we need multiple placeholders for a single
// piece of criteria
foreach ($value as $val) {
if (is_scalar($val)) {
$params[] = $val;
}
}
}
}
// Get JOIN clause, if necessary
if (!empty($info['join'])) {
$this->_joinTables($info['join']);
}
}
}
$joinSql = implode("\n", $this->_joined);
$whereSql = implode(' AND ', $where);
// How many records should we get, starting from where?
$offset = $criteria['offset']['default'];
$limit = $criteria['limit']['default'];
if (!empty($search['offset'])) {
$offset = $search['offset'];
}
if (!empty($search['limit'])) {
$limit = $search['limit'];
}
$limitSql = "LIMIT $offset,$limit";
if ($limit < 1) {
// If limit is less than one, assume we want to pull all records
$limitSql = '';
}
// sort order?
$sort = $criteria['sort']['default'];
if (!empty($search['sort'])) {
$sort = $search['sort'];
}
// Is this a count operation?
$select = 'id, title, user.username, body, when_modified, category';
if (isset($search['COUNT_ONLY'])) {
$select = "COUNT(id)";
$limitSql = ''; // No limit necessary for a count operation
}
// Build SQL
$sql =<<<EOQ
SELECT
$select
FROM
articles
$joinSql
WHERE
$whereSql
ORDER BY $sort
$limitSql
EOQ;
// And now we can pull our records or count...
// This example uses PEAR::DB, and assumes a DB connection stored in the
// $db class property.
if (isset($search['COUNT_ONLY'])) {
$results = $this->db->getOne($sql, $params);
} else {
$results = $this->db->getAll($sql, $params, DB_FETCHMODE_ASSOC);
}
if (PEAR::isError($results)) {
return "Error!";
}
return $results;
}
}
最后的笔记
以上方法不完整。它不对传入的值进行任何验证（例如，offset和limit应该是唯一的；sort应该可能验证该字段是有效，并且传递了方向指令；并且应该清理其他标准的各种值）。它只执行AND样式标准；如果你想做OR怎么办？或者混合搭配AND和OR？如果您有一个IN()样式语句的值列表，应该如何将这些值传递给API并进行处理？
但是，对于我遇到的大多数情况，上述功能已经绰绰有余（在某些情况下，有点矫枉过正）。它提供了一种简单、通用的搜索解决方案，非常灵活。如果有特殊情况，可以根据需要设置回调系统。
此示例可能重复了PEAR的DB_DataObjects等项目的工作。我几乎没有时间研究那个项目；如果有人阅读这篇文章可以发表评论，我将不胜感激。
最后，我想谈谈验证问题。我个人使用的是MVC模式，上面的搜索算法是Model的一部分。因此，我通常不会在应用程序的这个级别上浪费太多精力进行验证；验证是控制器的领域，它应该在将请求传递给模型之前对其进行过滤。然而，在某些情况下，这并不理想（例如，我可以看到一个AJAX风格的应用程序通过ReST直接与API通信），应该根据这种情况采取预防措施。
感谢PaulM.Jones鼓励我写这篇文章。
