Yii 中的relation

最新推荐文章于 2018-05-31 14:01:19 发布

doushen1

最新推荐文章于 2018-05-31 14:01:19 发布

阅读量1k

点赞数

分类专栏： Yii

Yii 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

原文：http://www.yiiframework.com/wiki/428/drills-search-by-a-has_many-relation/

此文主要讲述YII中的relation(HAS_MANY , BELONGS_TO, MANY_MANY)

Relation

BELONGS_TO: 多对一

HAS_MANY：一对多

MANY_MANY：多对多

BELONGS_TO 是相当简单的，但有时候HAS_MANY和MANY_MANY用起来就有些费劲了，下面让我们来通过几个例子看一下：

Example of HAS_MANY

例如：一个作者( author )对应多篇博文(post),它就是一对多的关系，表示如下：

/**
 * Author model
 * @property integer $id
 * @property integer $name Author's name
...
 */
class Author extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'posts' => array(self::HAS_MANY, 'Post', 'author_id');
        );
    }
    ...

/**
 * Post model
 * @property integer $id
 * @property integer $author_id FK to Author's id
 * @property integer $title Title of Post
...
 */
class Post extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'author' => array(self::BELONGS_TO, 'Author', 'author_id');
        );
    }
    ...

Task #1

展示在title中包含某个关键字的所有博文（ Show all posts that has a word in post title）

public static function GetPostsByTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // compare title
    $criteria->compare('title', $searchWord, true);
    // find
    $posts = Post::model()->findAll($criteria);
    // show
    foreach($posts as $post)
    {
        echo "Title = " . $post->title . "\n";
    }
}

工作正常！！

Task #2

展示在title中包含某个关键字的所有博文以及该博文的作者（Show all posts with their authors that has a certain word in post title）

public static function GetPostsWithAuthorByTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // with Author model
    $criteria->with = array('author');
    // compare title
    $criteria->compare('t.title', $searchWord, true);
    // find all posts
    $posts = Post::model()->findAll($criteria);
    // show all posts
    foreach($posts as $post)
    {
        echo "Title = " . $post->title . "\n";
        echo "Author = " . $post->author->name . "\n";
    }
}

然而，你可能会说：“$criteria->with = array('author');“这句code是不需要的，是的，你是对的！在不加这句代码的情况下也是可以正常工作的！因为post和author已经通过relation建立了关系。

在不使用$criteria->with = array('author');,而直接调用$post->author使用的是 lazy loading，这样会造成每次调用$post->author的时候都会发一次sql语句去查找一遍。如果使用了$criteria->with = array('author');，则会只发一次sql语句去join，称为：eager loading

eager loading比 lazy loading更加的有效率，但要注意的是在使用with来join表的时候，要消除歧义，尤其是两张表有相同名称的字段时;

...
    // compare Author's name
    $criteria->compare('author.name', $searchName, true);
    ...

Task #3

展示所有的作者，条件是该作者只少有一篇博文的title包含某个关键词（ Show all authors who has at least one post that has a certain word in post title）

下面，我们将要检索author表

public static function GetAuthorsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // with Post model
    $criteria->with = array('posts');
    // compare title
    $criteria->compare('posts.title', $searchWord, true);
    // find all authors
    $authors = Author::model()->findAll($criteria);
    // show all authors
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
    }
}

OK，工作正常，简直太简单了！

Task #4

展示所有的作者及他的所有博文，条件是该作者只少有一篇博文的title包含某个关键词（Show all authors with his/her all posts who has at least one post that has a certain word in post title ）

Hmmm. Just a small addition to the 3rd task. Something like this?

Wrong Answer

public static function GetAuthorsWithPostsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // with Post model
    $criteria->with = array('posts');
    // compare title
    $criteria->compare('posts.title', $searchWord, true);
    // find all authors
    $authors = Author::model()->findAll($criteria);
    // show all authors and his/her posts
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
        foreach($author->posts as $post)
        {
            echo "Post = " . $post->title . "\n";
        }
    }
}

啊哈，你错了！

为什么呢？

因为该任务是某作者所有的博文，而你只是展示了包含有某关键字的博文,怎么解决呢？此时就可以用lazy loading来解决：

Answer

public static function GetAuthorsWithPostsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // join Post model (without selecting)
    $criteria->with = array(
        'posts' => array(
            'select' => false,
        ),
    );
    // compare title
    $criteria->compare('posts.title', $searchWord, true);
    // find all authors
    $authors = Author::model()->findAll($criteria);
    // show all authors and his/her posts（查出来的用户都是只少有一篇博文中包含关键字）
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
        foreach($author->posts as $post)（连接生成的表中包含全部博文）
        {
            echo "Post = " . $post->title . "\n";
        }
    }
}

注意：这里的 lazy loading和上面所描述的lazy loading还是有区别的，上面的lazy loading是在没有使用with的情况下产生的（根本没有去连接author表），而这里的lazy loading是指使用了with的情况下产生的（已经连接了post表，但是并没有根据条件去删除哪些没有包含关键词的post）

通过设置'select' => false来说实现lazy loading。

Task #5

通过名字排序作者，并展示前5个作者及他的所有博文，条件是该作者只少有一篇博文的title包含某个关键词（Show top 5 authors in the order of name with his/her all posts who has at least one post that has a certain word in post title ）

貌似我们加上'LIMIT'和'ORDER'就可以了：

public static function GetTop5AuthorsWithPostsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // with Post model
    $criteria->with = array('posts');
    // compare title
    $criteria->compare('posts.title', $searchWord, true);
    // order by author name
    $criteria->order = 't.name ASC';
    // limit to 5 authors
    $criteria->limit = 5;
    // find all authors
    $authors = Author::model()->findAll($criteria);
    // show all authors and his/her posts
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
        foreach($author->posts as $post)
        {
            echo "Post = " . $post->title . "\n";
        }
    }
}

然后，却报错了： There's no column likeposts.title

The guide says:

Guide : By default, Yii uses eager loading, i.e., generating a single SQL statement, except when LIMIT is applied to the primary model.

It means that if LIMIT is applied to the primary model, then lazy loading will be used. This rule has been applied to our code above, and the query was executed without joining the author table. So, what to do then?

The guide proceeds to say:

Guide : We can set the together option in the relation declarations to be true to force a single SQL statement even when LIMIT is used.

OK. So we will modify the code to:

什么意思呢？意思是：LIMIT是作用在主表上的，默认情况下， YII使用的是eager loading（除了使用LIMIT的情况下），所以才会报上面的错误，如果想使用eager loading，则可以通过设置together=true来实现。（但这里的默认情况貌似不包含在使用HAS_MANY和MANY_MANY时，因为他们在不使用with的情况下是lazy loading）

...
    // force to join Post
    $criteria->with = array(
        'posts' => array(
            'together' => true,
        ),
    );
    ...

貌似可以工作了，但是，查找的数据确不正确：

[search word = foo]
Author = Andy
    Post = Don't use foo
    Post = Use yoo for foo
Author = Ben
    Post = foo is great
    Post = I love foo
Author = Charlie
    Post = What's foo?
[end]

你需要的是5个author，但是却查找出来了5个post，3个author（因为连接后的表删除了一部分post）

通过'select' => false 来解决：

...
    // force to join Post (without selecting)
    $criteria->with = array(
        'posts' => array(
            'together' => true,
            'select' => false,
        ),
    );
    ...

But it still doesn't work. It will show the results like this:

[search word = foo]
Author = Andy
    Post = Don't use foo
    Post = Use yoo for foo
    Post = Don't use bar
    Post = Use yar for bar
Author = Ben
    Post = foo is great
    Post = I love foo
    Post = I also love bar
Author = Charlie
    Post = What's foo?
    Post = What's bar?
[end]

这是因为LIMIT是作用在主表上的，对于生成的虚拟表并没有对此行为的解释，因此它不知道怎么去LIMIT数据！

不要放弃，我们可以使用$criteria->group = 't.id';来解决;

...
    // force to join Post (without selecting)
    $criteria->with = array(
        'posts' => array(
            'together' => true,
            'select' => false,
        ),
    );
    ...
    // group by Author's id
    $criteria->group = 't.id';
    ...

OK, 完美工作！

结果：

public static function GetTop5AuthorsWithPostsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // force to join Post (without selecting)
    $criteria->with = array(
        'posts' => array(
            'together' => true,
            'select' => false,
        ),
    );
    // compare title
    $criteria->compare('posts.title', $searchWord, true);
    // group by Author's id
    $criteria->group = 't.id';
    // order by author name
    $criteria->order = 't.name ASC';
    // limit to 5 authors
    $criteria->limit = 5;
    // find all authors
    $authors = Author::model()->findAll($criteria);
    // show all authors and his/her posts
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
        foreach($author->posts as $post)
        {
            echo "Post = " . $post->title . "\n";
        }
    }
}

Task #6

通过名字排序作者，并展示前5个作者及包含关键词的有关博文，条件是该作者只少有一篇博文的title包含某个关键词（Show top 5 authors in the order of name with his/her relevant posts who has at least one post that has a certain word in post title ）

也许通过使用eager loading就可以完成，但是，下面是另一种方法：

public static function GetTop5AuthorsWithPostsByPostTitle($searchWord)
{
    // query criteria
    $criteria = new CDbCriteria();
    // force to join Post (without selecting)
    $criteria->with = array(
        'posts' => array(
            'together' => true,
            'select' => false,
        ),
    );
    // compare title
    $criteria->compare('posts.title', $searchWord, true);
    // group by Author's id
    $criteria->group = 't.id';
    // order by author name
    $criteria->order = 't.name ASC';
    // limit to 5 authors
    $criteria->limit = 5;
    // find all authors
    $authors = Author::model()->findAll($criteria);
    // show all authors and his/her posts
    foreach($authors as $author)
    {
        echo "Author = " . $author->name . "\n";
        // lazy loading posts with filtering
        $filteredPosts = $author->posts(
            array(
                'condition' => 'title LIKE :search_word',
                'params' => array(
                    ':search_word' => '%' . $searchWord . '%',
                ),
            )
        );
        foreach($filteredPosts as $post)
        {
            echo "Post = " . $post->title . "\n";
        }
    }
}

在这里你可以动态的在lazy loading中定义查询条件。

The guide says:

Guide : 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.