multiple selfjoins doctrine 1.2

43 Views Asked by At

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.

0

There are 0 best solutions below