与其他表条件的Yii关系记录 [英] Yii Relational record with condition from other table

问题描述

我有类似如下的关系记录：

I have a relational record similar to the following:

• 有网友（表用户）与IDS
• 有类（表类别）与ID，名称
• 有篇（表项目）与ID，身体，CATEGORY_ID
• 然后有一个表read_articles与article_id的，USER_ID

这样用户就可以看到一个类别中的所有文章的列表。他们可以点击一篇文章，它会添加一个条目（USER_ID，article_id的）到read_articles表。

So a user can see a list of all articles in a category. They can click on an article and it will add an entry (user_id, article_id) to the read_articles table.

我希望能够使用的Yii的ActiveRecord的获得来自那些没有被当前用户读取并显示这些特定类别的所有文章。我一直在想这个问题在SQL方面的一点点，但我不知道如何将它列入我的ActiveRecord的设置。我最初的想法是在积极条记录中的参数范围：

I want to be able to use Yii's ActiveRecord to get all articles from a given category that have not been read by the current user and display those. I've been thinking about it in terms of SQL a little bit but I'm not sure how to fit it into my ActiveRecord setup. My first idea was a parameterized scope on the Article active record:

public function unread( $userId )
{
    $this->getDbCriteria()->mergeWith( array(
        'alias' => 'articles',
        'condition' => 'not exists (SELECT * '
            . 'FROM user_read_articles '
            . 'WHERE articles.id=user_read_articles.article_id '
            . 'AND read_articles.user_id=' . (int)$userId
            . ')',
    ) );
}

这似乎是工作，但感觉非常脏了我。

It seems to be working, but it feels really dirty to me.

有一个更清洁的方式做我的ActiveRecord的谈论？或者我应该可能考虑转向更普通的SQL和处理这样的事情我自己（而失去很多的AR不错的功能）？

Is there a cleaner way to do what I'm talking about in ActiveRecord? Or should I maybe consider moving toward more plain SQL and handling things like this myself (but lose a lot of nice feature of AR)?

修改以下是上述车型的关系：（声明，这些将被截断，只相关部分）

Edit Here are the relations for the above-mentioned models: (Disclaimer, these are truncated to only relevant parts)

UserActiveRecord

UserActiveRecord

public function relations()
{
    return array(
        'categories' => array(
            self::HAS_MANY,
            'UserCategoryActiveRecord',
            'user_id' ),
        // I added this early using gii, never really used it...
        'readArticles' => array(
            self::HAS_MANY,
            'UserReadArticleActiveRecord',
            'user_id' ),
    );
}

UserCategoryActiveRecord

UserCategoryActiveRecord

public function relations()
{
    return array(
        'user' => array(
            self::BELONGS_TO,
            'UserActiveRecord',
            'user_id' ),
        'articles' => array(
            self::HAS_MANY,
            'ArticleActiveRecord',
            'category_id',
            'order' => 'articles.pub_date DESC' ),
    );
}

ArticleActiveRecord

ArticleActiveRecord

public function relations()
{
    return array(
        'category' => array(
            self::BELONGS_TO,
            'CategoryActiveRecord',
            'category_id' ),
    );
}

我也做了一个UserReadArticleActiveRecord早在我建立这个，但我从来没有使用过这种模式，这是pretty的简单，只是空的。

I have also made a "UserReadArticleActiveRecord" early as I was building this, but I never used that model and it is pretty much just empty.

在所有诚实，我一直在想搬到Symfony2中和学说反正，只是开沟活动记录完全。我知道它并没有解决这个问题，但我可能只是下降这一点，并保持我目前的解决方案暂且。

In all honesty, I've been thinking of moving to Symfony2 and Doctrine anyway and just ditching active record altogether. I know it doesn't solve this problem, but I might just drop this and keep my current solution for the time being.

推荐答案

要获得读的文章，可以如下描述定义您的用户模型MANY_MANY关系：

To get the read articles, you can define a MANY_MANY relationship on your User model as described here:

http://www.yiiframework.com/doc/guide/ 1.1 / EN / database.arr

我不认为你需要代理模型UserReadArticles，你只需要正确定义的关系。

I don't think you'll need the proxy model UserReadArticles, you just need to define the relationship properly.

要获得读的文章，我也有麻烦想着Yii的方式做到这一点，但比使用子选择查询是做留下了一个更有效的方式连接来user_read_articles ，并检查user_read_articles列IS NULL（没有匹配的行）是这样的：

To get unread articles, I'm also having trouble thinking about the Yii way to do it, but a more efficient way than using a sub-select query is to do a LEFT JOIN to user_read_articles and check if a column from user_read_articles IS NULL (there was no matching row) something like this:

$this->getDbCriteria()->mergeWith( array(
    'alias' => 'articles',
    'join' => 'LEFT JOIN user_read_articles ON articles.id=user_read_articles.article_id',
    'condition' => 'user_read_articles.article_id IS NULL',
) );

这篇关于与其他表条件的Yii关系记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！