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;
}
}