复杂的CDbCriteria使用'with'子句

Question

I have 3ish tables (there's actually 4 because a joining table for a many-many relationship).

Lets say they are: grandparent(g), parent(p), child(c), with gp_join(gp) for the joining table for the grandparent-parent many-many relation. With the following relations:

Grandparent Relations:

'parent' => array( self::MANY_MANY, 'Parent', 'gp_join(g_id, p_id)' )

Parent Relations:

'grandparent' => array( self::MANY_MANY, 'Grandparent', 'gp_join(p_id, g_id)' ),
'child' => array( self::HAS_MANY, 'Child', 'p_id' )

Child Relations:

'parent' => array( self::BELONGS_TO, 'Parent', 'p_id' )

I want to find all grandchildren of one grandparent. I have an SQL query that does this successfully, but I need to know how to do this using the Yii framework (preferably using the 'with' property because it's cool).

Working SQL statement:

SELECT c.name
FROM grandparent `g`
JOIN gp_join `gp` ON gp.g_id=g.g_id
JOIN parent `p` ON p.p_id=gp.p_id
JOIN child `c` ON c.p_id = p.p_id
WHERE g.g_id=12;

Here is what I have been trying (it returns every child regardless of whether they have the right grandparent):

$criteria = new CDbCriteria();
$criteria->with['parent'] = array (
    'with' => array(
            'grandparent' => array( 
            'alias' => 'g',
            'condition' => 'g.g_id='.$this->g_id
        )
     ),
    'alias' => 'p',
    'condition' => 'p.p_id=t.list_id'
);
return Child::model()->findAll($criteria);

Answer 1

I would recommend dropping gp_join table and connecting those three tables with MANY_MANY relation becouse you have such relation between parent and child already. Its better to make it consistent.
I dont see your relations but I see the problem where you are trying to connect parent and grandparent without using gp_join table which will not work given conditions you have.
Here's how it should look like

$criteria->with['parent'] = array (
        'with' => array(
            'gpJoin' => array(
                'with'=> array(
                    'grandparent'=> array(
                        'alias' => 'g',
                        'condition' => 'g.g_id=:g_id',
                        'params'    => array(':g_id' => $this->g_id),
                    )
                ),
            )
        ),
    );

I added gpJoin relation and changed condition so it's not prone to SQL injection.

Child relations

'parent'    => array(self::MANY_MANY, "Parents", 'parent(p_id, p_id)'),

Parent relations

'gpJoin'    => array(self::HAS_MANY, 'GpJoin', 'p_id'),

GpJoin relations

'parent'    => array(self::HAS_ONE, 'Parents', 'p_id'),
'grandparent'   => array(self::BELONGS_TO, 'Grandparent', 'g_id'),