Yii Framework Forum: Show Values From Many_Many Related Table - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Show Values From Many_Many Related Table Rate Topic: -----

#1 User is offline   hampole123 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 03-May 13

Posted 05 May 2013 - 07:03 AM

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?
0

#2 User is offline   clonevn 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 111
  • Joined: 25-March 13

Posted 05 May 2013 - 09:00 AM

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.
0

#3 User is offline   Reza m 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 147
  • Joined: 07-March 12

Posted 05 May 2013 - 09:07 AM

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)'),

0

#4 User is offline   Shahcheraghean 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 478
  • Joined: 12-December 12
  • Location:Mashhad, IRAN

Posted 05 May 2013 - 11:22 AM

View PostReza m, on 05 May 2013 - 09:07 AM, said:

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?
www.shgn.ir
www.shgn.ir/yii-framework/
yii2.swsco.ir
In a world without any fences or walls,Who needs Gates or Windows!
0

#5 User is offline   Reza m 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 147
  • Joined: 07-March 12

Posted 05 May 2013 - 03:43 PM

View PostShahcheraghean, on 05 May 2013 - 11:22 AM, said:

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
0

#6 User is offline   hampole123 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 03-May 13

Posted 05 May 2013 - 08:14 PM

View PostReza m, on 05 May 2013 - 03:43 PM, said:

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'=>array('index'),
$model->actor_id,
);

$this->menu=array(
array('label'=>'List Actor', 'url'=>array('index')),
array('label'=>'Create Actor', 'url'=>array('create')),
array('label'=>'Update Actor', 'url'=>array('update', 'id'=>$model->actor_id)),
array('label'=>'Delete Actor', 'url'=>'#', 'linkOptions'=>array('submit'=>array('delete','id'=>$model->actor_id),'confirm'=>'Are you sure you want to delete this item?')),
array('label'=>'Manage Actor', 'url'=>array('admin')),
);
?>

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

<?php $this->widget('zii.widgets.CDetailView', array(
'data'=>$model,
'attributes'=>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'=>$dataProvider,
'columns'=>array('film_id','title','description')
));

?>
My problem is to show only films of the selected actor_id.
What is the code for the CActiveDataProvider?
0

#7 User is offline   Reza m 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 147
  • Joined: 07-March 12

Posted 07 May 2013 - 12:45 AM

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,
    ),
));

0

#8 User is offline   hampole123 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 03-May 13

Posted 07 May 2013 - 06:12 PM

It is not working! I get SQL error msg.
Pl advise.
0

#9 User is offline   hampole123 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 03-May 13

Posted 07 May 2013 - 06:13 PM

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
0

#10 User is offline   hampole123 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 03-May 13

Posted 07 May 2013 - 06:14 PM

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
0

#11 User is offline   hampole123 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 03-May 13

Posted 07 May 2013 - 06:15 PM

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'=>array(
'with'=>array('actors'),
'condition'=>'actors.actor_id='.$model->actor_id,
),
));
$dataProvider->getData();

$this->widget('zii.widgets.grid.CGridView', array(
'dataProvider'=>$dataProvider,
'columns'=>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?
0

#12 User is offline   Reza m 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 147
  • Joined: 07-March 12

Posted 08 May 2013 - 02:07 AM

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,
    ),
));

0

#13 User is offline   hampole123 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 03-May 13

Posted 09 May 2013 - 08:32 PM

View PostReza m, on 08 May 2013 - 02:07 AM, said:

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users