I have 3ish tables (there’s actually 4 because a joining table for a many-many relationship).
Lets say they are: grandparent(g), parent§, child©, with gp_join(gp) for the joining table for the grandparent-parent many-many relation. With the following relations:
Grandparent Relations:
[indent]‘parent’ => array( self::MANY_MANY, ‘Parent’, ‘gp_join(g_id, p_id)’ )[/indent]
Parent Relations:
[indent] ‘grandparent’ => array( self::MANY_MANY, ‘Grandparent’, ‘gp_join(p_id, g_id)’ ),
‘child’ => array( self::HAS_MANY, ‘Child’, ‘p_id’ )[/indent]
Child Relations:
[indent]‘parent’ => array( self::BELONGS_TO, ‘Parent’, ‘p_id’ )[/indent]
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 (
[indent] ‘with’ => array(
[indent] 'grandparent' => array(
[indent] 'alias' => 'g',
'condition' => 'g.g_id='.$this->g_id[/indent])[/indent] ),
'alias' => 'p',
‘condition’ => ‘p.p_id=t.list_id’[/indent]);
return Child::model()->findAll($criteria);