Propel ORM - SELECT ... WHERE col1 = col2

I have been trying to port project im working on to PropelORM. So far so everything has开发者_StackOverflow 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?

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.

What about

$res_crit->where(PropertyTypePeer::ID.' = '.PropertyTypePeer::CONVERTEDID);

At least less to write and seems to be more readable.

Worked for me in rather complex criteria.

NOTE: $criteria->where(), combines conditions through AND.

If you need custom criteria to be combined through OR, you need to use $criteria->orWhere().