Search code examples
phpormcriteriapropel

Propel ORM - SELECT ... WHERE col1 = col2

I have been trying to port project im working on to PropelORM. So far so everything has been great.

However I've ran into problems with a tree structure that we have setup in one of our tables.

Pretty much if ID = PARENTID then its a root. eg.

ID | NAME | PID
0  | ZERO | 0  
1  | ONE  | 1  
2  | TWO  | 1  
3  | THREE| 3

One and two are actually roots.

I tried something like this

$res_crit = new Criteria();  
$res_crit->add(PropertyTypePeer::ID, PropertyTypePeer::CONVERTEDID, Criteria::EQUAL);  
$result = PropertyTypePeer::doSelect($res_crit, Propel::getConnection('system'));

but it only returns one row where where ID = 0 and parent id = 0.

Any ideas?

Solution

  • You need to use a custom criteria to achieve such a query with propel:

    $res_crit->add(PropertyTypePeer::ID, PropertyTypePeer::ID.' = '.PropertyTypePeer::CONVERTEDID, Criteria::CUSTOM);

    A custom criteria allows you to write custom code in your WHERE clause. In such a case, the first argument of Criteria#add does not matter (it won't be used by propel at all) but we're putting in the column we're querying on for readability's sake.