Symfony Criteria for table alias with join of same table

Symfony Criteria for table alias and join a quick example.

Yesterday got a good situation, A kind of join with same table and with table alias.
I need to use the first query with symfony criteria.

I got this, hope this will be usefull to you too.

SELECT u1.USR_FNAME AS a, u1.USR_LNAME AS b, u2.USR_FNAME AS c, u2.USR_LNAME AS d, u3.USR_FNAME AS e, u3.USR_LNAME AS f
FROM tbl_test
LEFT JOIN tbl_users u1 ON ( tbl_test.TEST_USER1_ID = u1.USR_ID )
LEFT JOIN tbl_users u2 ON ( tbl_test.TEST_USER2_ID = u2.USR_ID )
LEFT JOIN tbl_users u3 ON ( tbl_test.TEST_USER3_ID = u3.USR_ID )
ORDER BY tbl_test.TST_ID ASC
LIMIT 10

$c = new Criteria();
   	$c->clearSelectColumns();
   	$c->addAlias('u1','tbl_users');
   	$c->addAlias('u2','tbl_users');
    $c->addAlias('u3','tbl_users');
   	$c->addSelectColumn(TblProjectPeer::TST_NAME);
   	$c->addAsColumn('a','u1.USR_FNAME');
   	$c->addAsColumn('b','u1.USR_LNAME');
    $c->addAsColumn('c','u2.USR_FNAME');
    $c->addAsColumn('d','u2.USR_LNAME');
  	$c->addAsColumn('e','u3.USR_FNAME');
   	$c->addAsColumn('f','u3.USR_LNAME');
   	$c->addJoin(TblTestPeer::TEST_USER1_ID,'u1.USR_ID',Criteria::LEFT_JOIN);
   	$c->addJoin(TblTestPeer::TEST_USER2_ID,'u2.USR_ID',Criteria::LEFT_JOIN);
  	$c->addJoin(TblTestPeer::TEST_USER3_ID,'u3.USR_ID',Criteria::LEFT_JOIN);

Or take another example

SELECT a.id FROM article a RIGHT JOIN article b ON a.article_id = b.id ORDER BY a.name DESC, b.date DESC

$c=new Criteria();
$c->addAlias('a', 'article');
$c->addAlias('b', 'article');
$c->addSelectColumn('b.id');
$c->addSelectColumn('a.article_id');
$c->addDescendingOrderByColumn('a.name');
$c->addDescendingOrderByColumn('b.date');
$c->addJoin('a.arcticle_id','b.id','RIGHT JOIN');
$rs = ArticlePeer::doSelectRS($c);

Happy Programming.

quick symfony tips.

I am learning symfony. Just got some good tips to share with you.
Symfony rocks.

I will update this regularly.

1> How to write OR condition query in symfony.
$c = new Criteria();
$c->clearSelectColumns();
$c->addSelectColumn(TablePeer::TBL_ID);
$cton1 = $c->getNewCriterion(TABLEPEER::TBL_ID1, $some_condition , Criteria::EQUAL);
$cton2 = $c->getNewCriterion(TABLEPEER::TBL_ID1, $some_condition , Criteria::EQUAL);
$cton1->addOr($cton2);
$c->add($cton1);

2> How to get query where first date greater then and less then last date.
$date1 = date('Y-m-d 00:00:00');
$date2 = date('Y-m-d 23:59:59');
$cton1 = $gUser->getNewCriterion(TABLEPEER::TBLE_CREATEDAT, $date1, Criteria::GREATER_EQUAL);
$cton1->addAnd($gUser->getNewCriterion(TABLEPEER::TBLE_CREATEDAT, $date2, Criteria::LESS_EQUAL));
$gUser->add($cton1);

3> how to run the update query in symfony.

“update table TableName set TableField1=’1′ where TableField2=$some_value.”

$update = new TableName();
$update->setNew(false);
$update->setTableField1(1);
$update->setTableField2($imageId); // this is primary key of the table. include this line.
$update->save();
?>


technorati tags:
PHP, code, sf, symfony, programming