Symfony: Most efficient way of getting list of sfGuardPermissions and sfGuardGroups that have them

Question

I'm working on a custom admin interface for managing sfGuardGroupPermission records, basically allowing the user to grant/remove permissions for all user groups across the system in one interface.

The query I'm using works fine when executed directly in the database, but when being executed and hydrated via Doctrine it seems to have most of its records stripped out.

Consider the following example:

• I have one record in the sf_guard_group_permission table
• I have 62 records in the sf_guard_permission table
• I have 42 records in the sf_guard_group table

I am using a Doctrine_RawSql object to construct the query, this looks as follows:

$q->select('{p.*}, {gp.*}, {g.*}')
                ->from('sf_guard_permission p
                    LEFT OUTER JOIN sf_guard_group_permission gp ON p.id = gp.permission_id
                    LEFT OUTER JOIN sf_guard_group g ON g.id = gp.group_id')
                ->addComponent('gp', 'sfGuardGroupPermission gp')
                ->addComponent('p', 'gp.Permission p')
                ->addComponent('g', 'gp.Group g');

And if I get the raw sql using getSqlQuery() I get the following (which returns the correct result - a list of all permissions left joined by any groups that have these permissions...

SELECT gp.group_id AS gp__group_id, gp.permission_id AS gp__permission_id, gp.created_at AS gp__created_at, gp.updated_at AS gp__updated_at, p.id AS p__id, p.name AS p__name, p.description AS p__description, p.section_model_id AS p__section_model_id, p.created_at AS p__created_at, p.updated_at AS p__updated_at, g.id AS g__id, g.name AS g__name, g.description AS g__description, g.created_at AS g__created_at, g.updated_at AS g__updated_at 
    FROM sf_guard_permission p 
    LEFT OUTER JOIN sf_guard_group_permission gp 
         ON p.id = gp.permission_id 
    LEFT OUTER JOIN sf_guard_group g 
         ON g.id = gp.group_id

When I run the query through doctrine and var_dump my resulting Doctrine_Collection, I am only given 2 records. The first record corresponds with the sf_guard_group_permission record I have in my table (correct), the second record has a group_id of null, but contains some valid sf_guard_permission information. It seems a bit strange.

Can someone maybe suggest where I'm going wrong?

Answer 1

For anyone else needing this... I just had my query wrong:

$q = Doctrine_Query::create()
        ->from('sfGuardPermission p')
        ->leftJoin('p.sfGuardGroupPermission gp')
        ->leftJoin('gp.Group g')
        ->leftJoin('p.sfGuardSectionModel sm');