Yii Framework + MySQL Part 5 ( Adding To Your Models )

posted by kevin on September 19, 2009

Related Articles

Adding Methods to Yii Models

We've looked at how to Create the model, use in built methods like findById and findAll() to query to get our data back, now we're going to take it a step further and look at how to actually go about adding methods and named scopes to our models.

There are a few ways to add to your models that increase your code re-usability.

One way would be to just add methods to your models, that would do a complex query, or very common query that you use a lot.

Say we want to add a query that will get all products within a price range, we need to add a limit and an offset for pagination.

One way we can accomplish that would be to add a method to our Product model.

 
public function findAllByPrice($min=0, $max=1000000, $offset = 0, $limit = 30) {
    $Criteria = new CDbCriteria();
    $Criteria->condition = "price >= :min AND price <=:max";
    $Criteria->limit = $limit;
    $Criteria->offset = $offset;
    $Criteria->params = array (
        ':min' => $min,
        ':max' => $max
    );
    return $this->with(array('brand'))->findAll($Criteria);
}

Now in any of our controlller files we can easily access this method and return all of our 'Product' objects with a single line command:

 
$Products = Product::model()->findAllByPrice(2501, 8000, 0, 30);
CA_Debug::output_yii_models($Products);

Which outputs:

 
Array
(
    [0] => Array
        (
            [id] => 4
            [brandId] => 1
            [name] => 1965 Telecaster
            [price] => 4950.00
        )
 
)

So very simply you can just add on to your model classes to return whatever you're looking for.

Named Scopes

Named scopes are another route you can take to get your data back. It's pretty slick. :)

Let's implement the same query using two named scope methods that we'll create:

I'm just going to prefix my named scopes with 'scope' then when I want to use them my code completion will bring them all up once I start to type 'scope'.

The named scope addition is simply just creating and merging a CDbCriteria object.

For example, to add a named scope for a price search we'd do this:

 
/**
 * Price Between - Named Scope
 *
 * @param double $min
 * @param double $max
 * @return Product 
 */
public function scopePriceBetween($min, $max) {
    $tableName = $this->tableName();
    $Criteria = new CDbCriteria();
    $Criteria->condition = "$tableName.price BETWEEN :min AND :max";
    $Criteria->params = array(
        ':min' => $min,
        ':max' => $max
    );
    $this->getDbCriteria()->mergeWith($Criteria);
    return $this;
}

Then we'll add an additional named scope for our limit.

 
/**
 * Limit - Named Scope
 *
 * @param int $limit
 * @param int $offset
 * @return Product
 */
public function scopeLimit($limit = 30, $offset = 0) {
    $Criteria = new CDbCriteria();
    $Criteria->limit = $limit;
    $Criteria->offset = $offset;
    $this->getDbCriteria()->mergeWith($Criteria);
    return $this;
}

Now we can simply use our named scopes to get our data back, and then we could continue to pile on named scopes to return our data.

 
$Products = Product::model()->scopePriceBetween(0, 5000)
                ->scopeLimit(30, 1)
                ->findAll();
CA_Debug::output_yii_models($Products);

Which produces:

 
Array
(
    [0] => Array
        (
            [id] => 4
            [brandId] => 1
            [name] => 1965 Telecaster
            [price] => 4950.00
        )
 
)

The query that actually gets executed is simple and looks like this:

 
Querying SQL: SELECT * FROM `Product` WHERE Product.price BETWEEN :min AND
:max LIMIT 30 OFFSET 1

So just another little hint if you didn't see this already, it makes it really easy to drill down and search for products.

I.E. in your controller you could do this:

 
public function actionIndex()
{
    $ProductModel = Product::model();
    if(!empty($_GET['min_price']) && !empty($_GET['max_price'])) {
        $ProductModel->scopePriceBetween($_GET['min_price'], $_GET['max_price']);
    }
    $offset = (!empty($_GET['start'])) ? $_GET['start'] : 0;
    $Products = $ProductModel->scopeLimit(30, $offset)->findAll();
    CA_Debug::output_yii_models($Products);
}

So as you see you can define several named scopes and then reuse them all over the place and significantly decrease development time as well as increasing code re-usability and flexibility.

Tags: Yii Framework and MySQL, PHP, Yii Framework, CDbCriteria, Yii Named Scope
Read more | Comments (3) | Last updated on September 19, 2009

3 comments to "Yii Framework + MySQL Part 5 ( Adding To Your Models )"

#91

Touya says:

December 6, 2009 at 07:10 am

Great work! I've read through all your 5 articles about Yii+MySQL in one breath.It's much more clear than the document provided by Yii itself. But I still have a question to discuss with you: How can I use multi-connection in Yii framework? I mean , as you know, we have more than 1 database , for example: we may have a DB master(localhost:3306,user,pass) with a DB slave(localhost:3309,user2,pass2), how can we define the two db connections in config.php? and can both of them use the same Model classes, because the structure of Master and Slave is almost the same. Thank you for your time,I'm looking forward your reply.

#98

Applouse! says:

December 23, 2009 at 12:42 pm

Wonderful work, its the best for yii newbie ever!

#104

Ryzior says:

January 22, 2010 at 03:10 am

Well, at last I understood whole model concept with your tutorial to mysql. There's no other as for now - so easy to understand for a newbie - intro/tutor to yii in the net. That would be perfect if you could write several posts about input data and the flow view->controller->model->db. Thanks and looking forward to seeing more :)

Adding Methods to Yii Models

Named Scopes

3 comments to "Yii Framework + MySQL Part 5 ( Adding To Your Models )"

Leave a Comment