Fix search in CGridview when having a double-dbrelation

Hi, I have a CGridview displaying information about Swimteams

The CGridview has table relations to connect an id with a name. I have fixed the search and sorting functionality that disappears when you create the relations(se below). There is one problem tho, one column in the CGridview has a relation to two tables instead of one, how can I fix the search and sorting functionality in this column? I guess the syntax is pretty much the same, but I can’t get it to work.

This is how I have solved the search and sorting for a CGridview-column with a single relation.

In my model:




public $club_search;


public function rules()

	{

		// NOTE: you should only define rules for those attributes that

		// will receive user inputs.

		return array(

			array('..., club_search', 'safe', 'on'=>'search'),

		);

	}


public function relations()

	{

		return array(

			'swimmer' => array(self::BELONGS_TO, 'Swimmer', 'swimmer_id'),

		);

	}


public function search()

	{

		$criteria=new CDbCriteria;

		

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

		          

		$criteria->compare('swimmer.name', $this->club_search, true );

		

		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}



And in the CGridview:




'columns'=>array(

array(

            'header' => 'Swimmer',

            'name'=>'club_search',

            'value' => '$data->swimmer->name',

        ),

)



Can you elaborate on the relations the table has? Many models have multiple relations, so I am not sure what you mean.

The Club model has a relation to the Swimmer(swimmer) model, the Swimmer model has a relation to the Result(result) model.

So in the CGridview I show the result like this:




array(

            'header' => 'Result',

            'value' => '$data->swimmer->result->time',

        ),



how can i make the search and sorting functionality work in this column in the CGridview?

Isn’t it essentially the same?


public function search()

        {

                $criteria=new CDbCriteria;

                

                $criteria->with = array('swimmer', 'swimmer.result');     

            

                $criteria->compare('swimmer.name', $this->club_search, true );

                

//note that you do not define the relation as a chain (swimmer.result.time).

//You define only the relation and the attribute

$criteria->compare('result.time', $this->result_search, true );

                

                return new CActiveDataProvider($this, array(

                        'criteria'=>$criteria,

                ));

        }



(with the rules updated accordingly and the public variable added)

This line seems to provide some sort of sql error:




$criteria->with = array('swimmer', 'swimmer.result');



maybe I should edit the search() method in the swimmer model?

What sort of sql error? I don’t think editing the swimmer search function helps.

The syntax is correct. Is the relation name correct?

I changed the relation to:




$criteria->with = array('clubs', 'swimmer.clubs');



both are spelled correctly

Here is the error:




CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1011 General SQL Server error: Check messages from the SQL Server [1011] (severity 15) [(null)]. The SQL statement executed was: SELECT COUNT(DISTINCT [t].[id]) FROM [dbo].[change_club] [t] LEFT OUTER JOIN [dbo].[Swim_Club] [clubs] ON ([t].[club_id]=[clubs].[swim_club_id]) LEFT OUTER JOIN [dbo].[Swimmer] [swimmer] ON ([t].[swimmer_id]=[swimmer].[swimmer_id]) LEFT OUTER JOIN [dbo].[Swim_Club] [clubs] ON ([swimmer].[swim_club_id]=[clubs].[swim_club_id])



Your naming is confusing … aren’t you starting with the “club” model? What is the “change_club” in the query? So you don’t want the swimmer result anymore, but the club?

Can you post the relations of all your models?

Im sorry for the confusion, I wanted to make it as easy as possible for you to help me so I used "easy-to-understand" relations.

I start in the model "Change_Club" with the relation


'swimmer' => array(self::BELONGS_TO, 'Swimmer', 'swimmer_id'),

in the "swimmer" model I declare the relation like this:


'clubs' => array(self::BELONGS_TO, 'Club', 'swim_club_id'),

And the error looks like this:




CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1011 General SQL Server error: Check messages from the SQL Server [1011] (severity 15) [(null)]. The SQL statement executed was: SELECT COUNT(DISTINCT [t].[id]) FROM [dbo].[change_club] [t] LEFT OUTER JOIN [dbo].[Swim_Club] [clubs] ON ([t].[club_id]=[clubs].[swim_club_id]) LEFT OUTER JOIN [dbo].[Swimmer] [swimmer] ON ([t].[swimmer_id]=[swimmer].[swimmer_id]) LEFT OUTER JOIN [dbo].[Swim_Club] [clubs] ON ([swimmer].[swim_club_id]=[clubs].[swim_club_id])



edit: after you changed your with condition, you are not getting the "swimmer" relation anymore. But this is mandatory for getting "swimmer.clubs". Also, have you another relation for Club_Change, that is "clubs". If not, this is wring in the relational query.

Hmmm, you might think I am not helping at all, but I really think there is something weird going on with your relations, which I can’t quite point out.

I recreated the models and the table strucure I assumed, and can find no problem with the relational active record. Are you sure you have modelled the relations correctly? Is belongs_to always correct? If you have belongs_to, the reference to the related model must be in the model / table you start with. So I suspect your three tables have these elements:

changeClub: id, swimmer_id

swimmer: id, swim_club_id

club: id

If this is not the case, then your relations are wrong. The relation "clubs" sounds like you want to reference many clubs, is that correct? Then belongs_to is worng. In any case, your relations should have names that easily identify if they are single or many.

I appreciate your help!

Maybe it’s the “BELONGS_TO” that’s causing the problem then.

The Change_Club table has id(pk), swimmer_id

Swimmer has swimmer_id(pk), swim_club_id

Swim_club has swim_club_id(pk)

Change_club have many swimmer_ids

Each Swimmer_id can only have one club,

but each club can have many swimmers

I tried to change BELONGS_TO to HAS_MANY but that throws an error.

It’s wierd because the CGridview displays the relations correctly




array(

            'header' => 'Club',

            'value' => '$data->swimmer->clubs->name',

        ),