Relational Active Record

We have already seen how to use Active Record (AR) to select data from a single database table. In this section, we describe how to use AR to join several related database tables and bring back the joint data set.

In order to use relational AR, it is recommended that primary-foreign key constraints are declared for tables that need to be joined. The constraints will help to keep the consistency and integrity of the relational data.

For simplicity, we will use the database schema shown in the following entity-relationship (ER) diagram to illustrate examples in this section.

ER Diagram

ER Diagram

Info: Support for foreign key constraints varies in different DBMS. SQLite 3.6.19 or prior does not support foreign key constraints, but you can still declare the constraints when creating tables. MySQL’s MyISAM engine does not support foreign keys at all.

1. Declaring Relationship

Before we use AR to perform relational query, we need to let AR know how one AR class is related with another.

Relationship between two AR classes is directly associated with the relationship between the database tables represented by the AR classes. From database point of view, a relationship between two tables A and B has three types: one-to-many (e.g. tbl_user and tbl_post), one-to-one (e.g. tbl_user and tbl_profile) and many-to-many (e.g. tbl_category and tbl_post). In AR, there are four types of relationships:

  • BELONGS_TO: if the relationship between table A and B is one-to-many, then B belongs to A (e.g. Post belongs to User);

  • HAS_MANY: if the relationship between table A and B is one-to-many, then A has many B (e.g. User has many Post);

  • HAS_ONE: this is special case of HAS_MANY where A has at most one B (e.g. User has at most one Profile);

  • MANY_MANY: this corresponds to the many-to-many relationship in database. An associative table is needed to break a many-to-many relationship into one-to-many relationships, as most DBMS do not support many-to-many relationship directly. In our example database schema, the tbl_post_category serves for this purpose. In AR terminology, we can explain MANY_MANY as the combination of BELONGS_TO and HAS_MANY. For example, Post belongs to many Category and Category has many Post.

There is fifth special type which performs aggregational queries on the related records - it's called STAT. Please refer to the Statistical Query section for more details.

Declaring relationship in AR involves overriding the relations() method of CActiveRecord. The method returns an array of relationship configurations. Each array element represents a single relationship with the following format:

'VarName'=>array('RelationType', 'ClassName', 'ForeignKey', ...additional options)

where VarName is the name of the relationship; RelationType specifies the type of the relationship, which can be one of the four constants: self::BELONGS_TO, self::HAS_ONE, self::HAS_MANY and self::MANY_MANY; ClassName is the name of the AR class related to this AR class; and ForeignKey specifies the foreign key(s) involved in the relationship. Additional options can be specified at the end for each relationship (to be described later).

The following code shows how we declare the relationships for the User and Post classes.

class Post extends CActiveRecord
{
    ......
 
    public function relations()
    {
        return array(
            'author'=>array(self::BELONGS_TO, 'User', 'author_id'),
            'categories'=>array(self::MANY_MANY, 'Category',
                'tbl_post_category(post_id, category_id)'),
        );
    }
}
 
class User extends CActiveRecord
{
    ......
 
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id'),
            'profile'=>array(self::HAS_ONE, 'Profile', 'owner_id'),
        );
    }
}

Info: A foreign key may be composite, consisting of two or more columns. In this case, we should concatenate the names of the foreign key columns and separate them with commas or an array such as array('key1','key2'). In case you need to specify custom PK->FK association you can define it as array('fk'=>'pk'). For composite keys it will be array('fk_c1'=>'pk_c1','fk_c2'=>'pk_c2'). For MANY_MANY relationship type, the associative table name must also be specified in the foreign key. For example, the categories relationship in Post is specified with the foreign key tbl_post_category(post_id, category_id). The declaration of relationships in an AR class implicitly adds a property to the class for each relationship. After a relational query is performed, the corresponding property will be populated with the related AR instance(s). For example, if $author represents a User AR instance, we can use $author->posts to access its related Post instances.

2. Performing Relational Query

The simplest way of performing relational query is by reading a relational property of an AR instance. If the property is not accessed previously, a relational query will be initiated, which joins the two related tables and filters with the primary key of the current AR instance. The query result will be saved to the property as instance(s) of the related AR class. This is known as the lazy loading approach, i.e., the relational query is performed only when the related objects are initially accessed. The example below shows how to use this approach:

// retrieve the post whose ID is 10
$post=Post::model()->findByPk(10);
// retrieve the post's author: a relational query will be performed here
$author=$post->author;

Info: If there is no related instance for a relationship, the corresponding property could be either null or an empty array. For BELONGS_TO and HAS_ONE relationships, the result is null; for HAS_MANY and MANY_MANY, it is an empty array. Note that the HAS_MANY and MANY_MANY relationships return arrays of objects, you will need to loop through the results before trying to access any properties. Otherwise, you may receive "Trying to get property of non-object" errors.

The lazy loading approach is very convenient to use, but it is not efficient in some scenarios. For example, if we want to access the author information for N posts, using the lazy approach would involve executing N join queries. We should resort to the so-called eager loading approach under this circumstance.

The eager loading approach retrieves the related AR instances together with the main AR instance(s). This is accomplished by using the with() method together with one of the find or findAll methods in AR. For example,

$posts=Post::model()->with('author')->findAll();

The above code will return an array of Post instances. Unlike the lazy approach, the author property in each Post instance is already populated with the related User instance before we access the property. Instead of executing a join query for each post, the eager loading approach brings back all posts together with their authors in a single join query!

We can specify multiple relationship names in the with() method and the eager loading approach will bring them back all in one shot. For example, the following code will bring back posts together with their authors and categories:

$posts=Post::model()->with('author','categories')->findAll();

We can also do nested eager loading. Instead of a list of relationship names, we pass in a hierarchical representation of relationship names to the with() method, like the following,

$posts=Post::model()->with(
    'author.profile',
    'author.posts',
    'categories')->findAll();

The above example will bring back all posts together with their author and categories. It will also bring back each author's profile and posts.

Eager loading may also be executed by specifying the CDbCriteria::with property, like the following:

$criteria=new CDbCriteria;
$criteria->with=array(
    'author.profile',
    'author.posts',
    'categories',
);
$posts=Post::model()->findAll($criteria);

or

$posts=Post::model()->findAll(array(
    'with'=>array(
        'author.profile',
        'author.posts',
        'categories',
    )
));

Sometimes we need to perform query using relation but don't want to get related models. Let's assume we have Users who posted many Posts. Post can be published but also can be in a draft state. This is determined by published field in the post model. Now we need to get all users who have published posts and we are not interested in posts themselves. This can be achieved the following way:

$users=User::model()->with(array(
    'posts'=>array(
        // we don't want to select posts
        'select'=>false,
        // but want to get only users with published posts
        'joinType'=>'INNER JOIN',
        'condition'=>'posts.published=1',
    ),
))->findAll();

4. Relational Query Options

We mentioned that additional options can be specified in relationship declaration. These options, specified as name-value pairs, are used to customize the relational query. They are summarized as below.

  • select: a list of columns to be selected for the related AR class. It defaults to '*', meaning all columns. Column names referenced in this option should be disambiguated.

  • condition: the WHERE clause. It defaults to empty. Column names referenced in this option should be disambiguated.

  • params: the parameters to be bound to the generated SQL statement. This should be given as an array of name-value pairs.

  • on: the ON clause. The condition specified here will be appended to the joining condition using the AND operator. Column names referenced in this option should be disambiguated. This option does not apply to MANY_MANY relations.

  • order: the ORDER BY clause. It defaults to empty. Column names referenced in this option should be disambiguated.

  • with: a list of child related objects that should be loaded together with this object. Be aware that using this option inappropriately may form an infinite relation loop.

  • joinType: type of join for this relationship. It defaults to LEFT OUTER JOIN.

  • alias: the alias for the table associated with this relationship. It defaults to null, meaning the table alias is the same as the relation name.

  • together: whether the table associated with this relationship should be forced to join together with the primary table and other tables. This option is only meaningful for HAS_MANY and MANY_MANY relations. If this option is set false, the table associated with the HAS_MANY or MANY_MANY relation will be joined with the primary table in a separate SQL query, which may improve the overall query performance since less duplicated data is returned. If this option is set true, the associated table will always be joined with the primary table in a single SQL query, even if the primary table is paginated. If this option is not set, the associated table will be joined with the primary table in a single SQL query only when the primary table is not paginated. For more details, see the section "Relational Query Performance".

  • join: the extra JOIN clause. It defaults to empty. This option has been available since version 1.1.3.

  • group: the GROUP BY clause. It defaults to empty. Column names referenced in this option should be disambiguated.

  • having: the HAVING clause. It defaults to empty. Column names referenced in this option should be disambiguated.

  • index: the name of the column whose values should be used as keys of the array that stores related objects. Without setting this option, an related object array would use zero-based integer index. This option can only be set for HAS_MANY and MANY_MANY relations.

  • scopes: scopes to apply. In case of a single scope can be used like 'scopes'=>'scopeName', in case of multiple scopes can be used like 'scopes'=>array('scopeName1','scopeName2'). This option has been available since version 1.1.9.

In addition, the following options are available for certain relationships during lazy loading:

  • limit: limit of the rows to be selected. This option does NOT apply to BELONGS_TO relation.

  • offset: offset of the rows to be selected. This option does NOT apply to BELONGS_TO relation.

  • through: name of the model's relation that will be used as a bridge when getting related data. This option has been available since version 1.1.7 where it can be used for HAS_ONE and HAS_MANY. Since 1.1.14 it can be used for BELONGS_TO as well.

Below we modify the posts relationship declaration in the User by including some of the above options:

class User extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id',
                            'order'=>'posts.create_time DESC',
                            'with'=>'categories'),
            'profile'=>array(self::HAS_ONE, 'Profile', 'owner_id'),
        );
    }
}

Now if we access $author->posts, we would obtain the author's posts sorted according to their creation time in descending order. Each post instance also has its categories loaded.

5. Disambiguating Column Names

When a column name appears in two or more tables being joined together, it needs to be disambiguated. This is done by prefixing the column name with its table's alias name.

In relational AR query, the alias name for the primary table is fixed as t, while the alias name for a relational table is the same as the corresponding relation name by default. For example, in the following statement, the alias name for Post and Comment is t and comments, respectively:

$posts=Post::model()->with('comments')->findAll();

Now assume both Post and Comment have a column called create_time indicating the creation time of a post or comment, and we would like to fetch posts together with their comments by ordering first the posts' creation time and then the comments' creation time. We need to disambiguate the create_time column like the following:

$posts=Post::model()->with('comments')->findAll(array(
    'order'=>'t.create_time, comments.create_time'
));

Tip: The default alias of a related table is the name of the relation. Please note that if you're using relation from within another relation the alias will be the former relation name only and will not be prefixed with the parent relation. For example, the alias for 'author.group' relation is 'group', not 'author.group'.

$posts=Post::model()->with('author', 'author.group')->findAll(array(
  'order'=>'group.name, author.name, t.title'
));

You can avoid the collision of tables' aliases by specifying the alias property of the relation.

$comments=Comment::model()->with(
  'author',
  'post',
  'post.author'=>array('alias'=>'p_author'))->findAll(array(
  'order'=>'author.name, p_author.name, post.title'
));

6. Dynamic Relational Query Options

We can use dynamic relational query options in both with() and the with option. The dynamic options will overwrite existing options as specified in the relations() method. For example, with the above User model, if we want to use eager loading approach to bring back posts belonging to an author in ascending order (the order option in the relation specification is descending order), we can do the following:

User::model()->with(array(
    'posts'=>array('order'=>'posts.create_time ASC'),
    'profile',
))->findAll();

Dynamic query options can also be used when using the lazy loading approach to perform relational query. To do so, we should call a method whose name is the same as the relation name and pass the dynamic query options as the method parameter. For example, the following code returns a user's posts whose status is 1:

$user=User::model()->findByPk(1);
$posts=$user->posts(array('condition'=>'status=1'));

7. Relational Query Performance

As we described above, the eager loading approach is mainly used in the scenario when we need to access many related objects. It generates a big complex SQL statement by joining all needed tables. A big SQL statement is preferrable in many cases since it simplifies filtering based on a column in a related table. It may not be efficient in some cases, however.

Consider an example where we need to find the latest posts together with their comments. Assuming each post has 10 comments, using a single big SQL statement, we will bring back a lot of redundant post data since each post will be repeated for every comment it has. Now let's try another approach: we first query for the latest posts, and then query for their comments. In this new approach, we need to execute two SQL statements. The benefit is that there is no redundancy in the query results.

So which approach is more efficient? There is no absolute answer. Executing a single big SQL statement may be more efficient because it causes less overhead in DBMS for parsing and executing the SQL statements. On the other hand, using the single SQL statement, we end up with more redundant data and thus need more time to read and process them.

For this reason, Yii provides the together query option so that we choose between the two approaches as needed. By default, Yii uses eager loading, i.e., generating a single SQL statement, except when LIMIT is applied to the primary model. We can set the together option in the relation declarations to be true to force a single SQL statement even when LIMIT is used. Setting it to false will result in some of tables will be joined in separate SQL statements. For example, in order to use separate SQL statements to query for the latest posts with their comments, we can declare the comments relation in Post class as follows,

public function relations()
{
    return array(
        'comments' => array(self::HAS_MANY, 'Comment', 'post_id',
                        'together'=>false),
    );
}

We can also dynamically set this option when we perform the eager loading:

$posts = Post::model()->with(
            array('comments'=>array(
                'together'=>false
            ))
        )->findAll();

8. Statistical Query

Besides the relational query described above, Yii also supports the so-called statistical query (or aggregational query). It refers to retrieving the aggregational information about the related objects, such as the number of comments for each post, the average rating for each product, etc. Statistical query can only be performed for objects related in HAS_MANY (e.g. a post has many comments) or MANY_MANY (e.g. a post belongs to many categories and a category has many posts).

Performing statistical query is very similar to performing relation query as we described before. We first need to declare the statistical query in the relations() method of CActiveRecord like we do with relational query.

class Post extends CActiveRecord
{
    public function relations()
    {
        return array(
            'commentCount'=>array(self::STAT, 'Comment', 'post_id'),
            'categoryCount'=>array(
                self::STAT, 'Category', 'post_category(post_id, category_id)'
            ),
        );
    }
}

In the above, we declare two statistical queries: commentCount calculates the number of comments belonging to a post, and categoryCount calculates the number of categories that a post belongs to. Note that the relationship between Post and Comment is HAS_MANY, while the relationship between Post and Category is MANY_MANY (with the joining table post_category). As we can see, the declaration is very similar to those relations we described in earlier subsections. The only difference is that the relation type is STAT here.

With the above declaration, we can retrieve the number of comments for a post using the expression $post->commentCount. When we access this property for the first time, a SQL statement will be executed implicitly to retrieve the corresponding result. As we already know, this is the so-called lazy loading approach. We can also use the eager loading approach if we need to determine the comment count for multiple posts:

$posts=Post::model()->with('commentCount', 'categoryCount')->findAll();

The above statement will execute three SQLs to bring back all posts together with their comment counts and category counts. Using the lazy loading approach, we would end up with 2*N+1 SQL queries if there are N posts.

By default, a statistical query will calculate the COUNT expression (and thus the comment count and category count in the above example). We can customize it by specifying additional options when we declare it in relations(). The available options are summarized as below.

  • select: the statistical expression. Defaults to COUNT(*), meaning the count of child objects.

  • defaultValue: the value to be assigned to those records that do not receive a statistical query result. For example, if a post does not have any comments, its commentCount would receive this value. The default value for this option is 0.

  • condition: the WHERE clause. It defaults to empty.

  • params: the parameters to be bound to the generated SQL statement. This should be given as an array of name-value pairs.

  • order: the ORDER BY clause. It defaults to empty.

  • group: the GROUP BY clause. It defaults to empty.

  • having: the HAVING clause. It defaults to empty.

9. Relational Query with Named Scopes

Relational query can also be performed in combination with named scopes. It comes in two forms. In the first form, named scopes are applied to the main model. In the second form, named scopes are applied to the related models.

The following code shows how to apply named scopes to the main model.

$posts=Post::model()->published()->recently()->with('comments')->findAll();

This is very similar to non-relational queries. The only difference is that we have the with() call after the named-scope chain. This query would bring back recently published posts together with their comments.

And the following code shows how to apply named scopes to the related models.

$posts=Post::model()->with('comments:recently:approved')->findAll();
// or since 1.1.7
$posts=Post::model()->with(array(
    'comments'=>array(
        'scopes'=>array('recently','approved')
    ),
))->findAll();
// or since 1.1.7
$posts=Post::model()->findAll(array(
    'with'=>array(
        'comments'=>array(
            'scopes'=>array('recently','approved')
        ),
    ),
));

The above query will bring back all posts together with their approved comments. Note that comments refers to the relation name, while recently and approved refer to two named scopes declared in the Comment model class. The relation name and the named scopes should be separated by colons.

Occasionally you may need to retrieve a scoped relationship using a lazy-loading approach, instead of the normal eager loading method shown above. In that case, the following syntax will do what you need:

~~ [php] // note the repetition of the relationship name, which is necessary $approvedComments = $post->comments('comments:approved'); ~~

Named scopes can also be specified in the with option of the relational rules declared in CActiveRecord::relations(). In the following example, if we access $user->posts, it would bring back all approved comments of the posts.

class User extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id',
                'with'=>'comments:approved'),
        );
    }
}
 
// or since 1.1.7
class User extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id',
                'with'=>array(
                    'comments'=>array(
                        'scopes'=>'approved'
                    ),
                ),
            ),
        );
    }
}

Note: Before 1.1.7 named scopes applied to related models must be specified in CActiveRecord::scopes. As a result, they cannot be parameterized.

Since 1.1.7 it's possible to pass parameters for relational named scopes. For example, if you have scope named rated in the Post that accepts minimum rating of post, you can use it from User the following way:

$users=User::model()->findAll(array(
    'with'=>array(
        'posts'=>array(
            'scopes'=>array(
                'rated'=>5,
            ),
        ),
    ),
));
 
class Post extends CActiveRecord
{
    ......
 
    public function rated($rating)
    {
        $this->getDbCriteria()->mergeWith(array(
            'condition'=>'rating=:rating',
            'params'=>array(':rating'=>$rating),
        ));
        return $this;
    }
 
    ......
}

10. Relational Query with through

When using through, relation definition should look like the following:

'comments'=>array(self::HAS_MANY,'Comment',array('key1'=>'key2'),'through'=>'posts'),

In the above array('key1'=>'key2'):

  • key1 is a key defined in relation specified in through (posts is this case).
  • key2 is a key defined in a model relation points to (Comment in this case).

through can be used with HAS_ONE, BELONGS_TO and HAS_MANY relations.

HAS_MANY through

HAS_MANY through ER

HAS_MANY through ER

An example of HAS_MANY with through is getting users from a particular group when users are assigned to groups via roles.

A bit more complex example is getting all comments for all users of a particular group. In this case we have to use several relations with through in a single model:

class Group extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'roles'=>array(self::HAS_MANY,'Role','group_id'),
            'users'=>array(
                self::HAS_MANY,'User',array('user_id'=>'id'),'through'=>'roles'
            ),
            'comments'=>array(
                self::HAS_MANY,'Comment',array('id'=>'user_id'),'through'=>'users'
            ),
        );
    }
}

Usage examples

// get all groups with all corresponding users
$groups=Group::model()->with('users')->findAll();
 
// get all groups with all corresponding users and roles
$groups=Group::model()->with('roles','users')->findAll();
 
// get all users and roles where group ID is 1
$group=Group::model()->findByPk(1);
$users=$group->users;
$roles=$group->roles;
 
// get all comments where group ID is 1
$group=Group::model()->findByPk(1);
$comments=$group->comments;

HAS_ONE through

HAS_ONE through ER

HAS_ONE through ER

An example of using HAS_ONE with through is getting user address where user is bound to address using profile. All these entities (user, profile, and address) do have corresponding models:

class User extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'profile'=>array(self::HAS_ONE,'Profile','user_id'),
            'address'=>array(
                self::HAS_ONE,'Address',array('id'=>'profile_id'),
                    'through'=>'profile'
            ),
        );
    }
}

Usage examples

// get address of a user whose ID is 1
$user=User::model()->findByPk(1);
$address=$user->address;

through on self

through can be used for a model bound to itself using a bridge model. In our case it's a user mentoring other users:

through self ER

through self ER

That's how we can define relations for this case:

class User extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'mentorships'=>array(
                self::HAS_MANY,'Mentorship','teacher_id','joinType'=>'INNER JOIN'
            ),
            'students'=>array(
                self::HAS_MANY,'User',array('student_id'=>'id'),
                    'through'=>'mentorships','joinType'=>'INNER JOIN'
            ),
        );
    }
}

Usage examples

// get all students taught by teacher whose ID is 1
$teacher=User::model()->findByPk(1);
$students=$teacher->students;

Total 11 comments

#17235 report it
programator at 2014/05/14 04:38pm
Class namespaces and specifying relations

If your models are namespaced and you want to relate two models using the relations() routine, remember that the classes specified in the relations() method have to be namespaced like below:

return array(
    'iDPAYMENTMETHOD' => array(self::BELONGS_TO, '\models\PaymentMethods', 'ID_PAYMENT_METHOD'),
    'iDPROJECT' => array(self::BELONGS_TO, '\models\Projects', 'ID_PROJECT'),
    'users' => array(self::MANY_MANY, '\models\Users', '\models\User_Donations(ID_DONATION, ID_USER)'),
);

See how instead of PaymentMethods we have the fully qualified class name \models\PaymentMethods

#14537 report it
Johnny Gan at 2013/08/21 01:09pm
Using non-primary key fields as relation

When we doing integration project, we need use client's user data, but their user table contains their user_id defined, which is different with our user_id, only user_email is same, and can be used to connect two tables.

So we create below relations in client's user model:

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(
            'tools'=>array(self::BELONGS_TO, 'Tool', 'tool_id'),
            //if the foreign key name is different, use array('id'=>'user_id')
            'users'=>array(self::BELONGS_TO, 'User', array('user_email'=>'user_email')),
        );
    }

Then we can use our data in client's grid view, like "users.position_title".

#6723 report it
marcovtwout at 2012/02/01 06:56am
Pass multiple parameters to relational named scopes.

Addition to the guide for passing multiple parameters to relational named scopes:

$users=User::model()->findAll(array(
    'with'=>array(
        'posts'=>array(
            'scopes'=>array(
                // passing only one parameter
                'rated'=>5,
                // passing multiple parameters
                // must be in the same order as function declaration
                'anotherScope'=>array(5, true),
            ),
        ),
    ),
));
#4364 report it
Mike at 2011/06/29 03:25am
Disambiguate columns in named scope

If you use scopes with relational queries you sometimes need to make sure, that the column names don't conflict with the main table. You should therefore first obtain the right table prefix like this:

public function scopes()
{
    $t=$this->getTableAlias(false);
    return array(
        'published'=>array('condition'=>"$t.published=1"),
    );
}
#2843 report it
Steve Friedl at 2011/02/15 11:34pm
Watch out for spaces!

As of 1.1.6, spaces in a MANY_MANY relationship key will break if it has spaces in the quoted string. Example:

public function relations() {
    return(
       'pages' => array (self::MANY_MANY , 'Page' , 'userpagelist(idPage,idUser  )'),
        ...

Here, the spaces after idUser produce an SQL error, and the fix is to remove the spaces before the parens.

Spaces matter!

#565 report it
jamesmoey at 2010/04/22 11:08pm
Using alias to avoid ambiguous

If you have default scopes or named scopes, it is better to have alias parameter define as well. To avoid ambiguous problem. Because you never be able to tell if the query is a relation one or non-relation one.

Example,

public function scopes()
{
  return array(
    'discount'=>array(
      'condition'=>'price.discount=1',
      'alias'=>'price',
    ),
  );
}

Notice that the price is specify in alias and condition.

Hopefully this will resolve some of the your ambiguous issue.

#566 report it
jamesmoey at 2010/04/22 10:59pm
Using scope on an instance
$this->price;

This will load all the price relation belong to the product instance. To apply a discount named scope on that relation. You need to do the following,

$this->price('price:discount');

Notice the additional text price in the parameter.

#970 report it
DD.Jarod at 2010/01/17 09:44pm
additional info about MANY_TO_MANY configuration

What the guide doesn't tell: If you declare a many to many relationship, the order of keys inside the jointable declaration must be 'my_id, other_id':

class Post extends CActiveRecord
{
  public function relations()
  {
    return array(
        'categories'=>array(self::MANY_MANY, 'Category',
            'tbl_post_category(post_id, category_id)'),
    );
  }
}
class Category extends CActiveRecord
{
  public function relations()
  {
    return array(
        'Posts'=>array(self::MANY_MANY, 'Post',
            'tbl_post_category(category_id, post_id)'),
    );
  }
}
#1039 report it
Ivo Roper at 2009/12/30 12:39am
extra clarity on the 'select' and 'condition' options

If you include a select clause, you will only get back the fields that you specify. Be sure to disambiguate each column, and if you are using an expression in the clause you must define the select elements as items in a single array.

If you include a where option, it's AND'd with the generated where clause for the current model's primary key.

As far as I know, the where option only allows you to additionally restrict the set of directly related records. For example, you can't conditionally include other PK relations, you can only get some set of the current model's direct relations.

#1153 report it
Aissam at 2009/11/20 10:24pm
order clause with some database/field

pay attention with value in order clause: depending on field name and database it could crash. you need to escape fields sometimes.

eg:

'order'=>'lists.order ASC'

will crash on sqlite. CDbCommand failed to prepare the SQL statement: SQLSTATE[HY000]: General error: 1 near "order": syntax error

so to work you need to escape it

'order'=>'lists."order"'

#1217 report it
dreamynsx at 2009/10/21 11:29am
accessing relational data

say you have class Foo and class Bar which maps to database tables 'foo' and 'bar' respectively.

table bar has a field called publish_date.

You do a join on 'foo' and 'bar' via active record find() method.

$criteria->join = "JOIN bar on bar.foo_id = foo.id"; $foo = Foo::model()->find($criteria);

Now you want to access the publish_date data but if you do

$foo->publish_date, it will say property not found.

To access it, you can setup relational rule like above and then access it like $foo->bar->publish_date.

But if you don't want to setup relational data, you can just add property publish_date to Foo class, and then access it like $foo->publish_date. It will reference the publish_date as result of the relational data.

Came in handy.

Leave a comment

Please to leave your comment.