Tao

Tao obeys its own inherent Nature

How To: Using symfony to add a join between tables

1. simple join two tables

purpose:
generate sql like:   

select * from photo p
    left join artist a on p.artist_id = a.artist_id
      where a.genre = 'something' and p.genre = 'something'

code:           

if(!CriteriaUtil::hasJoin($criteria, ArtistPeer::TABLE_NAME)){
    $criteria->addJoin(PhotoPeer::ARTIST_ID, ArtistPeer::ARTIST_ID, Criteria::LEFT_JOIN);
}
$criteria->add(ArtistPeer::GENRE, $genre);    
$criteria->add(PhotoPeer::GENRE, $genre);

2. join two tables, add AND OR between conditions
purpose:
generate sql like:    

select * from photo p
    left join artist a on p.artist_id = a.artist_id
      where (a.genre = 'some' or p.genre='something')
        and a.name = 'something'

code:   

if(!CriteriaUtil::hasJoin($criteria, ArtistPeer::TABLE_NAME)){
   $criteria->addJoin(PhotoPeer::ARTIST_ID, ArtistPeer::ARTIST_ID, Criteria::LEFT_JOIN);
}
$criteria->add(ArtistPeer::GENRE, $genre);
$c = $criteria->getCriterion(ArtistPeer::GENRE);
if($c != null){
   $c->addOr($criteria->getNewCriterion(PhotoPeer::GENRE, $genre));
}
$criteria->add(ArtistPeer::NAME, $name);

 

Note:
It's a good habit to check if we have joined the table already. to check this, you can use the following util class, it get all the joined tables, and check if the table exists in them.

class CriteriaUtil{
    public static function hasJoin($c, $table_name){
        $joins = $c->getJoins();
        if($joins != null){
            foreach($joins as $join){
                if($join->getRightTableName() == $table_name){
                    return true;
                }
                if($join->getLeftTableName() == $table_name){
                    return true;
                }
            }
        }
        return false;
    }
}

posted on 2007-12-05 17:26 wade 阅读(437) 评论(0)  编辑  收藏 所属分类: PHPSymfony


只有注册用户登录后才能发表评论。


网站导航:
 

导航

<2007年12月>
2526272829301
2345678
9101112131415
16171819202122
23242526272829
303112345

统计

常用链接

留言簿(7)

随笔分类

随笔档案

相册

Photo

搜索

最新评论

阅读排行榜

评论排行榜