problem with criteria and relations

Yii 1.1 General Discussion
1

This are the relations:


/**

     * @return array relational rules.

     */

    public function relations()

    {

        // NOTE: you may need to adjust the relation name and the related

        // class name for the relations automatically generated below.

        return array(

            'crawlers' => array(self::HAS_MANY, 'UserCrawler', 'user_id'),

            'crawlersCount' => array(self::STAT, 'UserCrawler', 'user_id'),

            'user_groups'=>array(self::MANY_MANY, 'Group', 'user_group(user_id, group_id)'),

            'groups'=>array(self::HAS_MANY, 'UserGroup', 'user_id'),

        

        );

    }

This is the search function:


public function searchGroup( $groupID, $inGroup )

    {

        // Warning: Please modify the following code to remove attributes that

        // should not be searched.


        $criteria=new CDbCriteria;

        $criteria->compare('username',$this->username,true);

        $criteria->with = array('groups');

        

        if ( $inGroup ) $criteria->addCondition('groups.group_id!='.$groupID);

        else $criteria->addCondition('groups.group_id='.$groupID);


        return new CActiveDataProvider('User', array(

            'criteria'=>$criteria,

        ));

        

        

    }

This is the error:

CDbCommand ha riportato un errore nell’esecuzione della query SQL: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘groups.group_id’ in ‘where clause’

How can I solve?

2

It looks like your relations are backwards. The ‘groups’ relation points to the UserGroup table and the ‘user_groups’ relation points to the Groups table. So, when you do CDbCriteria with(‘groups’) you’re including the UserGroups table. That’s why your conditions, which reference ‘groups.group_id’ aren’t working.

3

Sorry I added the relation ‘user_groups’=>array(self::MANY_MANY, ‘Group’, ‘user_group(user_id, group_id)’) but that’s not the real problem.

I’ve tryed disabling it but the error is still the same :(

4

You mean, you’re doing this now?:




$criteria->with = array('groups','user_groups');

//or you might only need

$criteria->with = array('user_groups');

//if you're only using the groups.x condition

I think you need to include the relation that gets you to the Group table if that’s the one you want to reference in the condition.

5

Like you can see the real problem is that Yii think that groups.group_id is a column of the User table instead of looking for the column group_id in the table group.

6

This is what it says when the secondary table is not being joined properly. In this case, it appears the groups table is not being joined in, so it defaults to assuming you are looking for a column within the main table.

7

How can I force loading it?

At the moment I’ve only the relation group, but it seems that $criteria->with = array(‘groups’) doens’t allow me to do what i’m trying to do :(

8

When you say $criteria->with = array(‘groups’), what this does is grabs the relationship that you labeled as “groups” which according to what you have shown is actually for the UserGroups table, not the Groups table as you seem to require. This is what I was saying originally. You should have labeled your relationships differently.

Your relationships should look like this:




'user_groups'=>array(self::HAS_MANY, 'UserGroup', 'user_id'),

'groups'=>array(self::MANY_MANY, 'Group', 'user_group(user_id, group_id)'),

Then when you do your with = array(‘groups’) I would imagine it would work. So, either change your relationships or change the $criteria->with part to use ‘user_groups’ instead of ‘groups’.