在symfony中, 对数据库的查询一般使用Criteria对象(symfony1.2中引入了Doctrine), 下面看一个简单的查询.
$c = new Criteria(); $products = ProductPeer::doSelect($c);
上面的查询取出了product表中的全部数据, 其作用和下面的sql语句是等同的.
SELECT * FROM `product`;
可能看上去Criteria的使用显得比较复杂, 但当你进行复杂的查询的时候你就会发现Criteria是多么的强大.
$c = new Criteria(); $c->add(Product::CATEGORY, 'computer'); $c->add(Product::PRICE, 2000, Criteria::GTEATER_THAN); $c->addAnd(Product::PRICE, 3000, Criteria::LESS_THAN); $c->addAscendingOrderByColumn(Product::PRICE); $product = ProductPeer::doSelect($c);
上面的语句取出了价格在2000至3000之间的分类为computer的产品, 并按照价格升序排列, 上面的语句会生成一个类似下面的语句.
SELECT * FROM `product`
WHERE `product`.`price`>2000
AND `product`.`price`<3000
ORDER BY `product`.`price` ASC;
我们来简单分析一下上面的语句,
$c = new Criteria();
首先我们创建一个Criteria对象
$c->add(Product::CATEGORY, ‘computer’);
我们添加第一个条件, 就是取出所有分类为computer的产品, 上面的语句其实等于`product`.`category`=’computer’, 一般的add操作会有3个参数, 字段名, 值和操作, 而上面这条语句只有两个参数是因为我们使用了缺省值Criteria::EQUAL, 其实上面的语句等同于$c->add(Product::CATEGORY, ‘computer’, Criteria::EQUAL);
$c->add(Product::PRICE, 2000, Criteria::GREATER_THAN);
这条语句其实字面上已经很清楚了, 我们添加了第二个查询条件, price greater than 2000, price > 2000.
$c->addAnd(Product::PRICE, 3000, Criteria::LESS_THAN);
同上 price < 3000
$c->addAscendingOrderByColumn(Product::PRICE);
这个方法比较特殊, addAscendingOrderByColumn方法给查询添加了排序方法, 按照价格升序排列.
$products = ProductPeer::doSelect($c);
最后执行查询并将对象集合赋值给$products变量, 这里涉及到一个概念叫做化合, 也就是执行查询结果并返回对象集合的过程, 除了doSelect方法之外, Peer还提供了doCount方法用来统计查询所影响的数据个数, 同时还提供了doDelete, doDeleteAll, doInsert和doUpdate等方法进行数据库操作, 如果你想查询一个唯一的结果对象, 那么你应该使用doSelectOne方法.
化合(hydrating)
调用::doSelect()比简单的SQL查询功能强大得多.首先, SQL会针对所有使用的DBMS而优化. 其次, 任何传递给Ceiteria的值都会在正和进SQL之前被转义, 这能防止SQL注入. 最后, 此方法返回了一个对象数组而不是一个结果集. ORM 给予数据库结果集自动创建并累计对象. 这个过程叫做化合. -《 somfony 权威指南》
在上面的语句中, 我们会发现并没有使用字段名, 而是清一色的常量, 合理的使用常量是一种良好的编程习惯, 对于这种不会轻易发生改变的值, 使用常量的好处是非常多的, 假设你修改了数据库的字段名, 你就不需要对整个项目中所有用到该字段的地方进行修改而之修改常量定义就可以了, 当然, symfony的常量定义是自动生成的.
Criteria并不仅限于单表的查询, 关联查询也一样很简单.
$c = new Criteria; $c->addJoin(ProductPeer::FACTORY_ID, FactoryPeer::ID); $c->add(ProductPeer::CATEGORY, 'computer'); $c->add(FactoryPeer::NAME, 'IBM'); $products = ProductPeer::doSelect($c);
上面查询返回了所有由IBM生产的computer.
关于数据查询还有很重要的一个概念就是分页, 对于分页的处理也是ORM的职责所在, 但symfony为我们提供了更好的解决方案sfPropelPager类. 下面是一个简单的分页例子.
// apps/frontend/modules/shop/actions/actions.class.php
public function executeList ()
{
$pager=new sfPropelPager('Shop',2);
// 分页的条件在这里
$c = new Criteria();
$pager->setCriteria($c);
$pager->setPage($this->getRequestParameter('page',1));
$pager->setPeerMethod('doSelect');
$pager->init();
$this->shop_pager=$pager;
}
// apps/frontend/modules/shop/templates/listSuccess.php
<?php foreach ( $shop_pager->getResults() as $shop ){ ?>
<p>
<?php echo $shop->getName();?>
</p>
<?php }?>
<div id="shop_pager">
<?php if( $shop_pager->haveToPaginate() ): ?>
<?php echo link_to('«','shop/list?page=1') ?>
<?php echo link_to('<','shop/list?page='.$shop_pager->getPreviousPage()) ?>
<?php foreach ( $shop_pager->getLinks() as $page ): ?>
<?php echo link_to_unless($page == $shop_pager->getPage(),$page,'shop/list?page='.$page) ?>
<?php echo ($page != $shop_pager->getCurrentMaxLink()) ? '-':'' ?>
<?php endforeach; ?>
<?php echo link_to('>','shop/list?page='.$shop_pager->getNextPage()) ?>
<?php echo link_to('»','shop/list?page='.$shop_pager->getLastPage()) ?>
<?php endif; ?>
</div>
如果有足够的时间, 我强烈建议读者将下面这段看完.
**Criteria常量**
/*
* 操作符
*/
EQUAL = “=”; // 等于
NOT_EQUAL = “<>”; // 不等于
ALT_NOT_EQUAL = “!=”; // 不等于
GREATER_THAN = “>”; // 大于
LESS_THAN = “<”; // 小于
GREATER_EQUAL = “>=”; // 大于等于
LESS_EQUAL = “<=”; // 小于等于
/**
* 关键字
*/
LIKE = ” LIKE “;
NOT_LIKE = ” NOT LIKE “;
CUSTOM = “CUSTOM”;
DISTINCT = “DISTINCT “;
IN = ” IN “;
NOT_IN = ” NOT IN “;/
ALL = “ALL “;
JOIN = “JOIN”;
ILIKE = ” ILIKE “;
NOT_ILIKE = ” NOT ILIKE “;
CUSTOM_EQUAL = “CUSTOM_EQUAL”;/
BINARY_AND = “&”;
BINARY_OR = “|”; */
ASC = “ASC”;
DESC = “DESC”;
ISNULL = ” IS NULL “;
ISNOTNULL = ” IS NOT NULL “;
CURRENT_DATE = “CURRENT_DATE”;
CURRENT_TIME = “CURRENT_TIME”;
CURRENT_TIMESTAMP = “CURRENT_TIMESTAMP”;
LEFT_JOIN = “LEFT JOIN”;
RIGHT_JOIN = “RIGHT JOIN”;
INNER_JOIN = “INNER JOIN”;
**Criteria方法**
getIterator() // Implementing SPL IteratorAggregate interface. This allowsyou to foreach () over a Criteria object.
getMap() // Implementing SPL IteratorAggregate interface. This allowsyou to foreach () over a Criteria object.
clear() // 清除全部条件 Brings this criteria back to its initial state, so that it can be reused as if it was new. Except if the criteria has grown in capacity, it is left at the current capacity.
addAsColumn($name, $clause) // 等于 column as column
getAsColumns() // 返回全部as column, Get the column aliases.
getColumnForAs($as) // Returns the column name associated with an alias (AS-column).
addAlias($alias, $table) // Allows one to specify an alias for a table that can be used in various parts of the SQL.
getTableForAlias($alias) // Allows one to specify an alias for a table that can be used in various parts of the SQL.
keys() // Allows one to specify an alias for a table that can be used in various parts of the SQL.
containsKey($column) // Allows one to specify an alias for a table that can be used in various parts of the SQL.
keyContainsValue($column) // Does this Criteria object contain the specified key and does it have a value set for the key
setUseTransaction($v) // Will force the sql represented by this criteria to be executed within a transaction. This is here primarily to support the oid type in postgresql. Though it can be used to require any single sql statement
isUseTransaction() // Whether the sql command specified by this criteria must be wrapped
getCriterion($column) // Whether the sql command specified by this criteria must be wrapped
getNewCriterion($column, $value, $comparison = null) // Method to return criterion that is not added automatically to this Criteria. This can be used to chain the Criterions to form a more complex where clause.
getColumnName($name) // Method to return criterion that is not added automatically to this Criteria. This can be used to chain the Criterions to form a more complex where clause.
getTablesColumns() // Shortcut method to get an array of columns indexed by table.
getComparison($key) // Method to return a comparison String.
getDbName() // 返回数据库名
setDbName($dbName = null) // 设置数据库名
getPrimaryTableName() // Get the primary table for this Criteria.
setPrimaryTableName($tableName) // Sets the primary table for this Criteria.
getTableName($name) // Method to return a comparison String.
getValue($name) // Method to return the value that was added to Criteria.
get($key) // Method to return the value that was added to Criteria.
put($key, $value)
putAll($t)
add($p1, $value = null, $comparison = null)
addJoin($left, $right, $operator = null) // Adds “ALL” modifier to the SQL statement.
getJoins() // Adds “ALL” modifier to the SQL statement.
setAll() // Adds “ALL” modifier to the SQL statement.
setDistinct() // 返回不重复的结果集, 等同于 SELECT DISTINCT
setIgnoreCase($b) // 忽略大小写
isIgnoreCase() // 返回是否忽略大小写
setSingleRecord($b)
isSingleRecord()
setLimit($limit) // 设置查询个数 , 等同于LIMIT $offset, $limit
getLimit() // 返回查询个数
setOffset($offset) // 设置偏移 , 等同于LIMIT $offset, $limit
getOffset() // 返回偏移
addSelectColumn($name) // 等同于 SELECT `$name`
hasSelectClause()
getSelectColumns() // 返回全部select column
clearSelectColumns() // 清除全部 select column
getSelectModifiers()
addGroupByColumn($groupBy) // 添加分组条件
clearGroupByColumns() // 清除全部分组条件
getGroupByColumns() // 返回全部分组条件
addAscendingOrderByColumn($name) // 添加升序条件
addDescendingOrderByColumn($name) // 添加降序条件
getOrderByColumns() // 返回全部排序条件
clearOrderByColumns() // 清除全部排序条件
remove($key) // Adds “ALL” modifier to the SQL statement.
toString()
size() // Returns the size (count) of this criteria.
equals($crit) // Adds “ALL” modifier to the SQL statement.
addHaving(Criterion $having) // This method adds a prepared Criterion object to the Criteria as a having clause. You can get a new, empty Criterion object with the getNewCriterion() method.
getHaving()
addAnd($p1, $p2 = null, $p3 = null)
addOr($p1, $p2 = null, $p3 = null)
由于Criteria并不是symfony项目的一部分, 所以在symfony的文档中并没有Criteria这一节, 如果要查看更详细的说明可以查看Criteria的源代码, 在symfony的lib/plugins/sfPropelPlugin/lib/vender/propel/util/Criteria.php.