define relation with condition

Yii 1.1 General Discussion
1

hi…

i have problem to define my relation in model class. i’m using three db tables.

group, post, group_post (cross reference table).

this is definition of relations in group model


public function relations()

{

    return array(

        'posts' => array(self::MANY_MANY, 'Post', 'group_post(group_id, post_id)',

            'order' => 'posts_posts.post_order'),

    );

}

i’m trying to create another releation to select only active/approved posts and this is not working.


public function relations()

{

    return array(

            'active_posts' => array(self::HAS_MANY, 'Post', 'group_post(group_id, post_id)',

                'order' => 'active_posts_active_posts.post_order',

                'condition' => 'active_posts.post_status = :post_status',

                'params' => array(':post_status' => 'active')

            ),

    );

}

i still got this error Unknown column ‘active_posts.group_post(group_id’ in ‘where clause’ .please can you help me define this relation.

thanks.

2

See - Is correct??


'order' => 'active_posts_active_posts.post_order',

or


'order' => 'active_posts.post_order',
3

in your config/main.php uncomment the following section


// uncomment the following to show log messages on web pages

array(

     'class'=>'CWebLogRoute',

),

it will give you the sql query dump at the bottom, you should be able to see what is the table alias for that table

4

post_order is defined in group_post cross reference table.

this is sql dump


SELECT

	`active_posts`.`post_id` AS `t1_c0`,

	`active_posts`.`post_name` AS `t1_c1`,

	`active_posts`.`post_slug` AS `t1_c2`,

	`active_posts`.`post_text` AS `t1_c3`,

	`active_posts`.`post_status` AS `t1_c4`,

	`active_posts`.`post_publish_date` AS `t1_c5`,

FROM 

	`post` `active_posts`

WHERE (active_posts.post_status = :post_status)

AND (active_posts.post_status = :post_status)

AND (`active_posts`.`group_post(group_id`=:ypl0)

AND (`active_posts`.`post_id)`=:ypl1) 

ORDER BY active_articles_active_articles.article_order
5

oh okay I see you need to add join there or add “with” clause in your criteria to include your second table right now it’s running that query against one table “active_posts”

6

I think the relation type is not correct (HAS_MANY -> MANY_MANY).




public function relations()

{

    return array(

            'active_posts' => array(self::MANY_MANY, 'Post', 'group_post(group_id, post_id)',

                'order' => 'active_posts_active_posts.post_order',

                'condition' => 'active_posts.post_status = :post_status',

                'params' => array(':post_status' => 'active')

            ),

    );

}
7

thanks argent. it’s working now.