目前常用的数据库查询采用的是Criteria。
常用的sql如下：
WHERE column = value ->add(column, value);
WHERE column <> value ->add(column, value, Criteria::NOT_EQUAL);
Other Comparison Operators
> , < Criteria::GREATER_THAN, Criteria::LESS_THAN
>=, <= Criteria::GREATER_EQUAL, Criteria::LESS_EQUAL
IS NULL, IS NOT NULL Criteria::ISNULL, Criteria::ISNOTNULL
LIKE, ILIKE Criteria::LIKE, Criteria::ILIKE
IN, NOT IN Criteria::IN, Criteria::NOT_IN
Other SQL Keywords
ORDER BY column ASC ->addAscendingOrderByColumn(column);
ORDER BY column DESC ->addDescendingOrderByColumn(column);
LIMIT limit ->setLimit(limit)
OFFSET offset ->setOffset(offset)
FROM table1, table2 WHERE table1.col1 = table2.col2 ->addJoin(col1, col2)
FROM table1 LEFT JOIN table2 ON table1.col1 = table2.col2 ->addJoin(col1, col2, Criteria::LEFT_JOIN)
FROM table1 RIGHT JOIN table2 ON table1.col1 = table2.col2 ->addJoin(col1, col2, Criteria::RIGHT_JOIN)
使用或语句查询的例子：
<?php
$c = new Criteria();
$c1 = $c->getNewCriterion(TableAPeer::COLUMN_A, 1);
$c2 = $c->getNewCriterion(TableAPeer::COLUMN_A, 2);
$c1->addOr($c2);
$c->add(TableAPeer::COLUMN_B, ‘xxx’);
$c->add($c1);
?>
等价于
SELECT * FROM table_a WHERE (column_a = 1 OR column_a = 3) AND column_c = ‘xxx
使用group语句查询的例子：
<?php
$c=new Criteria();
$c->addSelectColumn('SUM(score) as A');
$c->addSelectColumn(BuryPeer::SCORE);
$c->addGroupByColumn('article_id');
$crit=$c->getNewCriterion(BuryPeer::ARTICLE_ID,$article->getId());
$c->addHaving($crit);
$buries=BuryPeer::doSelectRS($c);
?>
等价于select SUM(score) from bury group by article_id having article_id=@ARTICLE_ID
Criteria可以用doSelect,doUpdate,doInsert,doDelete方法.
例：
doDelete($obj)操作
<?php
ShopTagPeer::doDelete($tag);
?>
doInsert操作
<?php
$b = new Criteria();
$b->add(ShopTagPeer::SHOP_ID, $shop_id);
$b->add(ShopTagPeer::TAG_ID, $tag);
ShopTagPeer::doInsert($b);
?>
还有一个查询不重复的方法
<?php
$c = new Criteria();
$c->setDistinct();
?>
<?php
//新建
$user = new User();
$user->setName('symfony');
$user->setAge(3);
$user->save();
//查询
$user = UserPeer::retrieveByPk(1);
echo $user->getName() . ' ' . $user->getAge() . '<br /> ';
$users = UserPeer::retrieveByPks(array(1, 2, 3, 4));
foreach ($users as $user) {
echo $user->getName() . ' ' . $user->getAge() . '<br /> ';
}
// 更新
$user = UserPeer::retrieveByPk(1);
$user->setName('new Name');
$user->save();
// 删除
UserPeer::doDelete(1);
$user = UserPeer::retrieveByPk(1);
$user->delete();
?>
<?php
// 查询 name = 'symfony' 的记录
$c = new Criteria();
$c->add(UserPeer::NAME, 'symfony');
$users = UserPeer::doSelect($c);
//只查询一条记录
$user = UserPeer::doSelectOne($c);
//...也支持 group by、limit、table join 等等特性
?>
使用 Criteria::CUSTOM 可使 Criteria 实现自定义条件查询
SELECT *
FROM users
WHERE CONCAT(first_name, ' ', last_name) = '$username';
<?php
$c = new Criteria();
$c->add(UserPeer::FIRST_NAME,
"CONCAT(" . UserPeer::FIRST_NAME . ", ' ', " . UserPeer::LAST_NAME . ") = '" . $username . "'",
Criteria::CUSTOM);
$users = UserPeer::doSelect($c);
?>
当遇到复杂语句查询的时候，可以采取以下方法:
<?php
$connection = Propel::getConnection();
$query = 'SELECT MAX(%s) AS max FROM %s'; //写你要查询的sql
$query = sprintf($query, ArticlePeer::CREATED_AT, ArticlePeer::TABLE_NAME);
$statement = $connection->prepare($query);
$statement->execute();
$resultset = $statement->fetch(PDO::FETCH_OBJ);
$max = $resultset->max;
?>
2008-12-15
关于数据查询的使用
评论暂缺 »
还没有任何评论。
这篇文章上的评论 RSS feed TrackBack URL
