I want execute multiple selfjoins with Doctrine 1.2
I have 2 tables
"objects":
----------------------------------------------------
| id | user_id | objectname
|--------|-----------|------------------------------
| 1 | 2 | object1
| 2 | 2 | object2
| 3 | 3 | object3
| 4 | 4 | object4
| 5 | 5 | object5
----------------------------------------------------
and "users"
----------------------------------------------------
| id | parentuser_id | username
|--------|---------------|--------------------------
| 2 | 1 | firstname
| 3 | 2 | firstname2
| 4 | 2 | firstname3
| 5 | 4 | firstname4
----------------------------------------------------
Any user can have many subusers, and subusers can also many subsubusers, and so on ...
I execute selfjoins to get objects of user.id=2 and of there subusers but only for one sublevel (user and there subuseres, not the subsubusers).
BaseUser.php looks like this:
abstract class BaseCustomer extends Doctrine_Record
{
public function setTableDefinition()
{
$this->setTableName('user');
$this->hasColumn('id', 'integer', 4, array(
'type' => 'integer',
'length' => 4,
'fixed' => false,
'unsigned' => false,
'primary' => true,
'autoincrement' => true,
));
$this->hasColumn('parentuser_id', 'integer', 4, array(
'type' => 'integer',
'length' => 4,
'fixed' => false,
'unsigned' => false,
'primary' => false,
'notnull' => false,
'autoincrement' => false,
));
$this->hasColumn('username', 'string', 100, array(
'type' => 'string',
'length' => 100,
'fixed' => false,
'unsigned' => false,
'primary' => false,
'notnull' => false,
'autoincrement' => false,
));
}
public function setUp()
{
parent::setUp();
$this->hasMany('User', array(
'local' => 'id',
'foreign' => 'parentuser_id'));
$this->hasOne('User as Subuser', array(
'local' => 'parentuser_id',
'foreign' => 'id'));
}
In ObjectController.php I can generate a Object-query with
$query-> ...;
$query->andWhere('user_id = ? OR Object.User.Subuser.id = ? ', array($user->id, $user->id));
This generate a SQL-Query like this:
SELECT *
FROM object o
LEFT JOIN user u
ON o.user_id = u.id
LEFT JOIN user u2
ON u.parentuser_id = u2.id
WHERE ( o.user_id = 2
OR u2.id = 2
)
Right, but this put out only the objects of the user with id=2 and the user with parent_user_id=2.
Result:
----------------------------------------------------
| id | user_id | objectname
----------------------------------------------------
| 1 | 2 | object1
| 2 | 2 | object2
| 4 | 4 | object4
----------------------------------------------------
But I need also the object of subusers of the subusers (In Example also object.id 3 and 5). How Can I do it? How can I modify the BaseUser.php and the query?
The result of sql should be look like this:
SELECT *
FROM object o
LEFT JOIN user u
ON o.user_id = u.id
LEFT JOIN user u2
ON u.parentuser_id = u2.id
LEFT JOIN user u3
ON u2.parentuser_id = u3.id
WHERE ( o.user_id = 2
OR u2.id = 2
OR u3.id = 2
)
Thanks for helping.