Blog Detail
27 Jul
Symfony Criteria for table alias with join of same table
Symfony Criteria for table alias and join a quick example.
Happy Programming.
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.
1 |
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 |
2 |
FROM tbl_test |
3 |
LEFT JOIN tbl_users u1 ON ( tbl_test.TEST_USER1_ID = u1.USR_ID ) |
4 |
LEFT JOIN tbl_users u2 ON ( tbl_test.TEST_USER2_ID = u2.USR_ID ) |
5 |
LEFT JOIN tbl_users u3 ON ( tbl_test.TEST_USER3_ID = u3.USR_ID ) |
6 |
ORDER BY tbl_test.TST_ID ASC |
7 |
LIMIT 10 |
01 |
$c = new Criteria); |
02 |
$c->clearSelectColumns(); |
03 |
$c->addAlias(\'u1\',\'tbl_users\'); |
04 |
$c->addAlias(\'u2\',\'tbl_users\'); |
05 |
$c->addAlias(\'u3\',\'tbl_users\'); |
06 |
$c->addSelectColumn(TblProjectPeer::TST_NAME); |
07 |
$c->addAsColumn(\'a\',\'u1.USR_FNAME\'); |
08 |
$c->addAsColumn(\'b\',\'u1.USR_LNAME\'); |
09 |
$c->addAsColumn(\'c\',\'u2.USR_FNAME\'); |
10 |
$c->addAsColumn(\'d\',\'u2.USR_LNAME\'); |
11 |
$c->addAsColumn(\'e\',\'u3.USR_FNAME\'); |
12 |
$c->addAsColumn(\'f\',\'u3.USR_LNAME\'); |
13 |
$c->addJoin(TblTestPeer::TEST_USER1_ID,\'u1.USR_ID\',Criteria::LEFT_JOIN); |
14 |
$c->addJoin(TblTestPeer::TEST_USER2_ID,\'u2.USR_ID\',Criteria::LEFT_JOIN); |
15 |
$c->addJoin(TblTestPeer::TEST_USER3_ID,\'u3.USR_ID\',Criteria::LEFT_JOIN); |
Or take another example
1 |
SELECT a.id FROM article a RIGHT JOIN article b ON a.article_id = b.id ORDER BY a.name DESC, b.date DESC |
1 |
$c=new Criteria(); |
2 |
$c->addAlias(\'a\', \'article\'); |
3 |
$c->addAlias(\'b\', \'article\'); |
4 |
$c->addSelectColumn(\'b.id\'); |
5 |
$c->addSelectColumn(\'a.article_id\'); |
6 |
$c->addDescendingOrderByColumn(\'a.name\'); |
7 |
$c->addDescendingOrderByColumn(\'b.date\'); |
8 |
$c->addJoin(\'a.arcticle_id\',\'b.id\',\'RIGHT JOIN\'); |
9 |
$rs = ArticlePeer::doSelectRS($c); |
