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.😎

5 Comments

  1. Posted December 23, 2009 at 1:18 pm | Permalink | Reply

    Thank you. You help me with

    $c->addDescendingOrderByColumn(”);

  2. rio
    Posted March 7, 2010 at 9:20 am | Permalink | Reply

    hi, i have a problem.

    how to get the result of “code” with criteria:
    $c->addSelectColumn(“b.id as code”);

    ?

    :((

    thanx’s

    • jebari
      Posted March 19, 2011 at 4:56 pm | Permalink | Reply

      you can use thie function $c ->addAsColumn(‘code’, tablePeer::alias(‘b’,tablePeer::ID));

  3. cedrix73
    Posted February 27, 2012 at 4:19 pm | Permalink | Reply

    Thank you, it was very useful, althrough I think that Propel 1.0 do not manage correctly alias tables.

  4. Posted November 21, 2014 at 6:02 pm | Permalink | Reply

    Really good post…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: