Show Values From Many_Many Related Table

I have three tables : actor, film_actor, film. The fields are:

actor:

actor_id(primary key)

first_name

last_name

film_actor:

actor_id,film_id(primary key)

film:

film_id(primary key)

title

description

In the Actor model I have the relations:

‘filmactors’ => array(self::HAS_MANY, ‘FilmActor’, ‘actor_id’),

‘films’ => array(self::HAS_MANY, ‘Film’, ‘film_id’, ‘through’ => ‘filmactors’),

In the View of Actor for an actor_id I have

$dataProvider=new CActiveDataProvider(‘Film’);

$dataProvider->getData();

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

'dataProvider'=>$dataProvider,


	'columns'=>array('film_id','title','description')

));

In Film model I have the following relations:

‘hasFilmActors’ => array(self::HAS_MANY, ‘FilmActor’, ‘film_id’),

‘actors’ => array(self::HAS_MANY, ‘actor’, ‘actor_id’, ‘through’ => ‘hasFilmActors’),

This shows all films. I want to show only the films pertaining to the selected actor.

Can somebody suggest a solution?

Maybe you forgot the searching for the selected models. I did not see any criteria or conditions in your provided code above. Without it, of course, it will list all the models.

use MANY_MANY instead HAST_MANY

in Actor model’s relations




'films' => array(self::MANY_MANY, 'Film', 'film_actor(actor_id, film_id)'),



in Film model’s relations




'actors' => array(self::MANY_MANY, 'Actor', 'film_actor(film_id, actor_id)'),



That’s OK, but i think there should be a middle class for film_actor, Isn’t it?

this way you don’t need a middle class for film_actor just test it and you’ll see

I have tried that as well. My view file for actor is as follows:

<?php

/* @var $this ActorController */

/* @var $model Actor */

$this->breadcrumbs=array(

'Actors'=&gt;array('index'),


&#036;model-&gt;actor_id,

);

$this->menu=array(

array('label'=&gt;'List Actor', 'url'=&gt;array('index')),


array('label'=&gt;'Create Actor', 'url'=&gt;array('create')),


array('label'=&gt;'Update Actor', 'url'=&gt;array('update', 'id'=&gt;&#036;model-&gt;actor_id)),


array('label'=&gt;'Delete Actor', 'url'=&gt;'#', 'linkOptions'=&gt;array('submit'=&gt;array('delete','id'=&gt;&#036;model-&gt;actor_id),'confirm'=&gt;'Are you sure you want to delete this item?')),


array('label'=&gt;'Manage Actor', 'url'=&gt;array('admin')),

);

?>

<h1>View Actor #<?php echo $model->actor_id; ?></h1>

<?php $this->widget(‘zii.widgets.CDetailView’, array(

'data'=&gt;&#036;model,


'attributes'=&gt;array(


	'actor_id',


	'first_name',


	'last_name',


	'created',


	'modified',


),

));

$dataProvider=new CActiveDataProvider(‘Film’); //I want code for this line

$dataProvider->getData();

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

'dataProvider'=&gt;&#036;dataProvider,


	'columns'=&gt;array('film_id','title','description')

));

?>

My problem is to show only films of the selected actor_id.

What is the code for the CActiveDataProvider?

with this in film model relations




'actors' => array(self::MANY_MANY, 'Actor', 'film_actor(film_id, actor_id)'),



you can access an actor’s films as follows




$dataProvider=new CActiveDataProvider('Film', array(

    'criteria'=>array(

        'with'=>array('actors'),

        'condition'=>'actors.actor_id='.$model->actor_id,

    ),

));



or with your relation




'filmactors' => array(self::HAS_MANY, 'FilmActor', 'actor_id'),



you can change it to




$dataProvider=new CActiveDataProvider('Film', array(

    'criteria'=>array(

        'with'=>array('filmactors'),

        'condition'=>'filmactors.actor_id='.$model->actor_id,

    ),

));



It is not working! I get SQL error msg.

Pl advise.

It is not working! I get error message:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘actors.actor_id’ in ‘where clause’. The SQL statement executed was: SELECT t.film_id AS t0_c0, t.title AS t0_c1, t.description AS t0_c2, t.release_year AS t0_c3, t.category_id AS t0_c4, t.language_id AS t0_c5, t.rental_duration AS t0_c6, t.rental_rate AS t0_c7, t.length AS t0_c8, t.replacement_cost AS t0_c9, t.rating AS t0_c10, t.special_features AS t0_c11, t.created AS t0_c12, t.modified AS t0_c13, t.last_update AS t0_c14 FROM film t WHERE (actors.actor_id=6) LIMIT 10

The other solution of having model relations through filmactors is also giving error msg:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘filmactors.actor_id’ in ‘where clause’. The SQL statement executed was: SELECT t.film_id AS t0_c0, t.title AS t0_c1, t.description AS t0_c2, t.release_year AS t0_c3, t.category_id AS t0_c4, t.language_id AS t0_c5, t.rental_duration AS t0_c6, t.rental_rate AS t0_c7, t.length AS t0_c8, t.replacement_cost AS t0_c9, t.rating AS t0_c10, t.special_features AS t0_c11, t.created AS t0_c12, t.modified AS t0_c13, t.last_update AS t0_c14 FROM film t WHERE (filmactors.actor_id=7) LIMIT 10

I tried the follwing code:

$criteria=new CDBcriteria;

$criteria->select = 't.* ;

$criteria->join = ‘INNER JOIN film_actor ON t.film_id = film_actor.film_id INNER JOIN actor ON film_actor.actor_id = actor.actor_id’;

$criteria->condition = 'film_actor.actor_id = ’ . $model->actor_id;

$dataProvider=new CActiveDataProvider(‘Film’, array(

	'criteria'=&gt;array(


			'with'=&gt;array('actors'),


			'condition'=&gt;'actors.actor_id='.&#036;model-&gt;actor_id,


	),

));

$dataProvider->getData();

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

'dataProvider'=&gt;&#036;dataProvider,


	'columns'=&gt;array('film_id','title','description','release_year','rental_duration','rental_date','rating','special_features')

));

This works. But this doesn’t look elegant. It moreover does not use the model relations.

Can you tell me what is wrong?

make sure you declared the relation and try this instead




$dataProvider=new CActiveDataProvider('Film', array(

    'criteria'=>array(

        'with'=>array('actors'),

        'together'=>true,

        'condition'=>'actors.actor_id='.$model->actor_id,

    ),

));



It worked! Thanks Mr. Reza.