SelfJoin using Symfony 1.4/propel 1.4

Question

I need to do self join using Symfony 1.4/Propel 1.4. My tables/db are too big to put here but an example table is given below to replicate the issue I'm facing.

Consider following example table with example data

Table Employee
----------------------------------------
|id    | name                    | mid |
----------------------------------------
|1     | CEO                     |NULL |
|2     | CTO                     |1    |
|3     | CFO                     |1    |
|4     | PM1                     |2    |
|5     | TL1                     |4    |
----------------------------------------

Here first column is employee, second is employee name and 3rd is manager id. mid is link to another row in same table. For example, CTO(2) reports to CEO(1) so mid in second row is 1.

I need following output:

---------------------
|ename    | manager |
---------------------
|CTO      | CEO     |
|CFO      | CEO     |
|PM1      | CTO     |
|TL1      | PM1     |
---------------------

The SQL query will be:

SELECT e.name,m.name
FROM employee e, employee m
WHERE e.mid=m.id
AND e.mid NOT NULL;

My problem is, how do I write same query in Symfony/Propel 1.4? I try following

$c = new Criteria();
$c->clearSelectColumns();
$c->addSelectColumn(EmployeePeer::NAME.' as ename');
$c->addSelectColumn(EmployeePeer::NAME.' as manager');
$c->setPrimaryTableName(EmployeePeer::TABLE_NAME);
$c->addJoin(EmployeePeer::MID, EmployeePeer::ID, Criteria::INNER_JOIN);
$c->add(EmployeePeer::MID, NULL, Criteria::EQUAL);

Even I know this query do not make any sense and as per my expectation, I got PropelException.

But self join is one of the common database operation and I'm sure Propel must support that. Can someone please tell how to achieve above requirements in Symfony/Propel 1.4

Answer 1

Well I never tried so not sure if that help you or not but there is no other answers so you might try/further search addAlias method, if you are stuck.

$notifCrit->addAlias("A", ThreadsPeer::TABLE_NAME);
$notifCrit->add("A.father_id", ThreadsPeer::FATHER_ID."=A.father_id", Criteria::CUSTOM);

Taken from last comment of old symfony forums

Not sure but Propel 1.4 might not support self join with build in methods as it need to set alias. So you need custom query as in above example.

Answer 2

According to this SQLFiddle, the SQL you want to perform is:

SELECT e.name as ename, m.name as manager
FROM employee e
LEFT JOIN employee m ON e.mid = m.id WHERE e.mid IS NOT NULL;

Like YouthPark, I think addAlias is the solution and I will do something like that:

$c = new Criteria();
$c->clearSelectColumns();
$c->addSelectColumn(EmployeePeer::NAME.' as ename');
$c->addSelectColumn(EmployeePeer::NAME.' as manager');

$c->addAlias('c2', EmployeePeer::TABLE_NAME);

$c->addJoin(EmployeePeer::ID, EmployeePeer::alias('c2', EmployeePeer::MID), Criteria::LEFT_JOIN);

$c->add(EmployeePeer::MID, Criteria::ISNOTNULL);

I'm not sure about the addSelectColumn part, by the way.

Answer 3

$c = new Criteria();
$c->addJoin(ArticlePeer::AUTHOR_ID, AuthorPeer::ID);
$c->add(AuthorPeer::NAME, 'John Doe');
$articles = ArticlePeer::doSelect($c);