在symfony中使用复杂的sql语句

在symfony中使用复杂的sql语句

关键词： symfony 复杂的sql语句

比如要执行如下这样的sql:

SELECT b.id FROM article_mark a RIGHT JOIN article b ON a.article_id = b.id ORDER BY a.mark DESC,b.CREATED_AT DESC

在symfony中可用这样：

1. $c=new Criteria();
2. $c-<addAlias('a', 'article_mark'); //!!!if not using alias of table, the generated sql is not correct
3. $c-<addAlias('b', 'article');
4. $c-<addSelectColumn('b.id');
5. $c-<addSelectColumn('a.article_id'); //!!!if one table has no column added, there's no table name after FROM clause;; actually this column is not what I need
6. $c-<addDescendingOrderByColumn('a.mark');
7. $c-<addDescendingOrderByColumn('b.CREATED_AT');
8. $c-<addJoin('a.ARTICLE_ID','b.ID','RIGHT JOIN');
9. $art_marks=ArticlePeer::doSelectRS($c); //!!! doSelect or doSelectOne can't be used

又比如这样的sql:

select SUM(score) from bury group by article_id having article_id=@ARTICLE_ID

在symfony中可以这样来写：

1. $c=new Criteria();
2. $c-<addSelectColumn('SUM(score) as A'); //actually 'as A' has no use
3. $c-<addSelectColumn(BuryPeer::SCORE); //!! without this, no table name in generated sql; actually this column is not what I need
5. $c-<addGroupByColumn('article_id');
6. $crit=$c-<getNewCriterion(BuryPeer::ARTICLE_ID,$article-<getId());
7. $c-<addHaving($crit);
8. $buries=BuryPeer::doSelectRS($c); //only MySQL recordset can be used

【作者: Liberal】【访问统计:<script language="JavaScript" src="http://counter.blogchina.com/PageServlet?pageid=6393626&blogid=8561"></script>】【2007年07月30日 星期一 10:15】【注册】【打印】