CGridView - Display related table

Yii 1.1 General Discussion
1

I have the following:


tbl_user

========

id

name

etc




tbl_jobs

========

id

job_details




tbl_job_applications

====================

id

user_id

job_id

applied_date

I am trying to display a CGridView that returns all jobs that the current user has applied to. I need data from both the ‘jobs’ table and ‘job_applications’ table. I get the user_id using Yii::app()->user->id

I can’t get my head around this - it should be very simple to do but I’m struggling. There are 4 parts to this:

The controller action

The relation(s) in the model

The search() function in the model

The dataProvider reference in the CGridView

It’s all in a mess at the moment.

2

In my site I have a table Stories, which has a column called ‘eid’, a reference to a table called Engines. I needed to display the engine name associated with each story in the CGridView.

Stories Model




//Relations

'engine' => array(self::BELONGS_TO, 'Engines', 'eid')


//Search

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

$criteria->compare('engine.name', $this->eid);

...

Engines Model




//Relations:

'stories' => array(self::HAS_MANY, 'Stories', 'eid')

Stories Controller




//actionIndex (or whatever action your CGridView is for)

$model=new Stories('search');

	$model->unsetAttributes();  // clear any default values

	if(isset($_GET['Stories']))

		$model->attributes=$_GET['Stories'];


	$this->render('index',array(

		'model'=>$model,

	));

Stories Index View (or whatever view you are using)




$dataProvider = new CActiveDataProvider("Stories", array(

				'criteria'=>$criteria

			));


$this->widget('zii.widgets.grid.CGridView', array(

		'id'=>'stories-grid',

		'dataProvider'=>$dataProvider,

		'filter'=>Stories::model(),

		'columns'=>array(

			array(

				'name' => 'eid',

				'header' => 'Engine',

				'value' => '($data->engine->name)',

				'filter' => $this->getEngineFilter(), //custom filter for engines, doesn't work yet...not important in displaying the engines

			),

			array(

				'name' => 'date',

				'value' => '($data->date)',

			),

			array(

				'name' => 'summary',

				'value' => 'substr($data->summary,0,100)'

			),

			$cbutton, //custom buttons, not really important

		)

	));

So that seemed to work for me. Unfortunately my filters are not working properly, but I am still working on that. At the very least, this displays all the appropriate information. Hope this helps.

3

I forgot to mention that for


 

new CActiveDataProvider($name, array(

	'criteria'=>$criteria

));

the $criteria variable above, I am using my own criteria that I created, which is probably why the filter isn’t working. I’m not sure what you would put there, sorry.

4

Thanks for the example rymonator - it makes sense but unfortunately I’m having trouble applying this approach to my set up.

Anybody else got any ideas?

5

tbl_user

========

id

name

etc

tbl_jobs

========

id

job_details

tbl_job_applications

====================

id

user_id

job_id

applied_date

It is a many-many relationship between user and jobs, and the job_applications is the middle table, right?

if so, here is the example.

User model




public function relations()

{

	return array(

            'jobs'=>array(

                self::MANY_MANY,

                'Jos',

                'JobApplications(user_id, job_id)'

            ),

            'jobApplications'=>array(

                self::HAS_MANY,

                'JobApplications',

                'user_id'

            ),

	);

}

JobApplications model




public function relations()

{

	return array(

            'users'=>array(

                self::BELONGS_TO,

                'User',

                'user_id',

            ),

            'jobs'=>array(

                self::BELONGS_TO,

                'Jobs',

                'job_id',

            ),

	);

}

Jobs model




public function relations()

{

	return array(

            'users'=>array(

                self::MANY_MANY,

                'User',

                'JobApplications(user_id, job_id)'

            ),

            'jobApplications'=>array(

                self::HAS_MANY,

                'JobApplications',

                'job_id'

            ),

	);

}

In the controller




$criteria = new CDbCriteria;

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

$criteria->together = true;

$models = User::model()->findAll($criteria);
6

Hi David.

No there is no relation between User and Jobs. The only relations are:

User -> Job_Applications (1 to many)

Jobs -> Job_Applications (1 to many)

All I want to do is display in a CGridView the data from Job_Applications for the current user, along with the job_details field from the Jobs table.

I think its an inner join rather than a left outer join.

7

sorry, i dont understand~~~

since the structure of Job_Applications has both job_id and user_id, why arent they many-many?

8

Yeah that’s right - I suppose it is many-many relation between User and Job_Applications … but there is no relation between User and Jobs

9

from the structure, i still consider it to be many-many between User and Jobs…囧

about User and Job_Applications, that is only can say has-many between User and Job_Applications.

but i dont know what you really want, so, maybe i am wrong, you are right in your situation. :)