Finally, we can actually get our data back and this is where you see how cool Yii is.
So far we haven't written any SQL yet... and in this tutorial we still won't write much.
So we'll start by looking at our auto-generated Product model.
<?php class Product extends CActiveRecord { /** * The followings are the available columns in table 'Product': * @var integer $id * @var integer $brandId * @var string $name * @var string $price */ /** * Returns the static model of the specified AR class. * @return CActiveRecord the static model class */ public static function model($className=__CLASS__) { return parent::model($className); } /** * @return string the associated database table name */ public function tableName() { return 'Product'; } /** * @return array validation rules for model attributes. */ public function rules() { return array( array('name','length','max'=>255), array('price','length','max'=>8), array('name, price', 'required'), ); } /** * @return array relational rules. */ public function relations() { // NOTE: you may need to adjust the relation name and the related // class name for the relations automatically generated below. return array( 'brand' => array(self::BELONGS_TO, 'Brand', 'brandId'), 'categories' => array(self::MANY_MANY, 'Category', 'ProductCategory(productId, categoryId)'), ); } /** * @return array customized attribute labels (name=>label) */ public function attributeLabels() { return array( 'id'=>'Id', 'brandId'=>'Brand', 'name'=>'Name', 'price'=>'Price', ); } }
First let's enable our web logger so we can easily see what Yii is doing while we're making these calls.
open up your config file.
/protected/config/main.php By default your log array looks like this:
'log'=>array(
'class'=>'CLogRouter',
'routes'=>array(
array(
'class'=>'CFileLogRoute',
'levels'=>'error, warning',
),
),
),
Let's add in the CWebLogRoute so it looks like this.
'log'=>array(
'class'=>'CLogRouter',
'routes'=>array(
array(
'class'=>'CFileLogRoute',
'levels'=>'error, warning',
),
array(
'class'=>'CWebLogRoute',
'levels'=>'trace,info, error, warning',
),
),
),
Go ahead and pull up your site now. On the bottom of the scree you should see what Yii is doing behind the scenes for you. This is especially useful when you're new to the Yii DB stuff.
Without any customizing, let's see some cool things we can do with our product model.
Let's start by getting all of our products.
I'm working from a new install and I'm simply going to be modifying the SiteController and the view from /protected/views/site/index.php
So we'll first modify our SiteController::actionIndex method.
public function actionIndex()
{
$this->render('index', array(
'Products' => Product::model()->findAll(),
));
}
Now open your view and edit it to look like this.
We added an array that will be passed to our index view. Our array contains 'Products' which will be usable as '$Products' in our view.
/protected/views/site/index.php
<?php $this->pageTitle=Yii::app()->name; ?> <h1> Welcome, <?php echo Yii::app()->user->name; ?>! </h1> <table> <?php foreach($Products AS $Product):?> <tr> <td><?php echo $Product->name;?></td> <td><?php echo $Product->price;?></td> </tr> <?php endforeach;?> </table>
So as you can see, we didn't write ANY SQL, but we got back an array of product objects. Very simple and pretty darn straightforward. :)
Now let's use our 'brand' relation that is set in our Product model.
Without changing our controller we can can simply change our view and add our line to output the brand name.
<td><?php echo $Product->brand->name;?></td>
Now how did Yii do that? Look at the screen logger. You will see that it used the 'lazy-loading' technique to query the Brand table at that point. So essentially for my 2 products, it ran these queries.
Querying SQL: SELECT * FROM `Product` Querying SQL: SELECT `Product`.`id` AS `t0_c0`, t1.`id` AS `t1_c0`, t1.`name` AS `t1_c1`, t1.`website` AS `t1_c2` FROM `Product` LEFT OUTER JOIN `Brand` t1 ON (`Product`.`brandId`=t1.`id`) WHERE (`Product`.`id`=3) Querying SQL: SELECT `Product`.`id` AS `t0_c0`, t1.`id` AS `t1_c0`, t1.`name` AS `t1_c1`, t1.`website` AS `t1_c2` FROM `Product` LEFT OUTER JOIN `Brand` t1 ON (`Product`.`brandId`=t1.`id`) WHERE (`Product`.`id`=4)
So you can see that this probably isn't the best way to do this if we know that we will need the brand information from the get go. Let's now change our controller to include our brand relation:
public function actionIndex()
{
// renders the view file 'protected/views/site/index.php'
// using the default layout 'protected/views/layouts/main.php'
$this->render('index', array(
'Products' => Product::model()->with(array('brand'))->findAll(),
));
}
Now reload the page and check out the log.
We now got all of our data using one query:
Querying SQL: SELECT `Product`.`id` AS `t0_c0`, `Product`.`brandId` AS `t0_c1`, `Product`.`name` AS `t0_c2`, `Product`.`price` AS `t0_c3`, t1.`id` AS `t1_c0`, t1.`name` AS `t1_c1`, t1.`website` AS `t1_c2` FROM `Product` LEFT OUTER JOIN `Brand` t1 ON (`Product`.`brandId`=t1.`id`)
Nothing had to change in our view. This technique is called eager-loading and you can see the advantages already of eager-loading over lazy-loading.
Now the next tutorial will over more complex queries with using limits, ordering, and adding in where clauses.
You're probably realizing just how awesome the Yii Framework's Database implementation is already. More to come soon.
7 comments to "Yii Framework + MySQL Part 3 (Using Our Models To Do Basic Queries On The DB)"
Leave a Comment