I am still in the early stages of learning Yii (an MVC, OOP, etc) and I hoping someone can take a little time to help me figure out when and where Active Record should be used vs DAO. I have spent the past few days reading nearly every wiki entry, forum post, and Yii document I could find regarding the topic but have not been able to find a definitive answer. While I understand there is no "right" answer, there still must be a best practice according to Yii experts or authors and that is what I am looking for.
Here is the simplified version of the tables I have and their relations:
"Film" table ~400 records (contains the name of the film, the running time, rating, production year, etc.)
"Name" table ~18,000 (contains names of actors, actresses, directors, and anyone else credited in the films)
"Role" table ~10,000(contains the roles that can be assigned to any credited individuals such as director, character name, etc.)
"Credits" table ~38,000 (contains the combination of the three above tables as well as a flag for cast/crew such as film_id=145, name_id=20786, role_id=48, and is_cast=0 which tells me that Christopher Nolan is the Director of Batman Begins and is a crew member, not a cast member)
I have models, controllers, and views for all of these with basic CRUD functionality. My understanding is that this is what Active Record does best. Right now I have the film information displaying in the CDetailView widget as delivered in the view.php file on the film view.
What I would like is to add a list of cast (is_cast=1) and a list of crew (is_cast=0) beneath the CDetailView widget. As I mentioned earlier, I have read a ton of documents/wikis/forums and cannot figure out the best way to do this. My first attempt was based on some examples in the Agile book and was to modify FilmController.php controller file and change the actionView function to the following:
public function actionView($id)
{
$creditsCrewDataProvider=new CActiveDataProvider('Credits', array(
'criteria'=>array(
'condition'=>'film_id=:filmId AND is_cast=0',
'params'=>array(':filmId'=>$id),
),
));
$creditsCastDataProvider=new CActiveDataProvider('Credits', array(
'criteria'=>array(
'condition'=>'film_id=:filmId AND is_cast=1',
'params'=>array(':filmId'=>$id),
),
));
$this->render('view',array(
'model'=>$this->loadModel($id),
'creditsCrewDataProvider'=>$creditsCrewDataProvider,
'creditsCastDataProvider'=>$creditsCastDataProvider,
));
}
and modify the Credits.php model file to include
public function getNameOptions()
{
$nameArray = CHtml::listData(Name::model()->findAll(), 'id', 'name');
return $nameArray;
}
public function getNameText()
{
$nameOptions=$this->nameOptions;
return isset($nameOptions[$this->name_id]) ? $nameOptions[$this->name_id] : "unknown name ({$this->name_id})";
}
etc...
and add this to my view
<h2>Credits</h2>
<?php $this->widget('zii.widgets.CListView', array(
'dataProvider'=>$creditsCrewDataProvider,
'viewData' => array( 'model' => 'film' ),
'itemView'=>'/credit/_view',
)); ?>
etc...
Doing the above, viewing a single film took approximately 25 seconds. Should that take that long? Doing a similar query directly against the database in phpMyAdmin returns results instantly. So, I thought that maybe this is where Active Record should be left behind, and DAO should take over.
Reading hundreds of pages about DAO, I have not found a best practice on how to implement such a requirement. So, combining what I read from multiple sources and playing around until something worked, this is what I have now:
In the Film.php model
public function queryCredits($id,$is_cast)
{
$cmd=Yii::app()->db->createCommand("SELECT mov_name_id, name, role FROM {{mov_film_credit}} mfc LEFT JOIN {{mov_name}} mn ON mfc.mov_name_id = mn.id LEFT JOIN {{mov_role}} mr ON mfc.mov_role_id = mr.id WHERE mov_film_id=:id AND is_cast=:is_cast ORDER BY mfc.order");
$cmd->bindValue(':id',$id);
$cmd->bindValue(':is_cast',$is_cast);
return $cmd->query();
}
and in the view.php view file for film
<h2>Cast</h2>
<?php
$cast=Film::model()->queryCredits($model->id,'1');
foreach($cast as $credit)
{
echo CHtml::link(CHTML::encode($credit[name]), array('/movies/name/view', 'id'=>$credit[mov_name_id]));
echo " ($credit[role])<br />";
}
?>
<h2>Crew</h2>
<?php
$crew=Film::model()->queryCredits($model->id,'0');
foreach($crew as $credit)
{
echo CHtml::link(CHTML::encode($credit[name]), array('/movies/name/view', 'id'=>$credit[mov_name_id])) . " ($credit[role])<br />";
}
?>
This solution returns the results instantly like I would expect. So, I’m happy that it works, but, it doesn’t seem right. I merely kept playing with the code until something worked (my newbie showing through). So I have a few questions about the first example using Active Record and this second example using DAO.
[list=1]
[*]Which approach is preferred for this type of scenario? If Active Record, why is it taking so long and how do I fix the speed issue?
[*]Assuming the second approach is necessary/desired for this scenario, what is the "correct" way to code this? Should that much logic be placed into the view file? Is the Film model (or any model in general) the best place for the select statement? Is the controller just not used in this situation? Should there be a separate "dao" directory where I just place all of these "queryCredits" style functions that I use throughout the site.
[/list]
I’m sure I will have more questions for the amazingly helpful individual that responds to this long post. All I ask is that people not merely reply with a post to another forum topic that I’ve probably already read. Here is a bonus question for anyone that wants to answer–which of the two options below is better?
$cmd=Yii::app()->db->createCommand("SELECT mov_name_id, name, role FROM {{mov_film_credit}} mfc LEFT JOIN {{mov_name}} mn ON mfc.mov_name_id = mn.id LEFT JOIN {{mov_role}} mr ON mfc.mov_role_id = mr.id WHERE mov_film_id=:id AND is_cast=:is_cast ORDER BY mfc.order");
$cmd->bindValue(':id',$id);
$cmd->bindValue(':is_cast',$is_cast);
return $cmd->query();
$credits=Yii::app()->db->createCommand()
->select('mov_name_id, name, role')
->from('{{mov_film_credit}} mfc')
->join('{{mov_name}} mn', 'mfc.mov_name_id = mn.id')
->join('{{mov_role}} mr', 'mfc.mov_role_id = mr.id')
->where('mov_film_id=:id AND is_cast=:is_cast', array(':id'=>$id, ':is_cast'=>$is_cast))
->order('mfc.order')
->limit('25')
->query();
return $credits;
To whomever decides to answer this post, thank you so much. If I could, I’d buy you a beer (or other beverage of choice)
now you can reduce the logic with a combination of two methods you have used to.[/size][/font]