Yii 关系记录与其他表的条件

2022-01-04 00:00:00 php yii activerecord

我有一个类似于以下的关系记录:

I have a relational record similar to the following:

  • 有带有 id 的用户(表用户)
  • 有带有 id、name 的类别(表类别)
  • 有 id、body、category_id 的文章(表格文章)
  • 然后有一个表 read_articles 和 article_id, user_id

因此用户可以看到一个类别中所有文章的列表.他们可以点击一篇文章，它会在 read_articles 表中添加一个条目(user_id、article_id).

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)

用户活动记录

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' ),
    );
}

文章活动记录

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

我在构建它的早期也制作了一个UserReadArticleActiveRecord"，但我从未使用过该模型，而且它几乎是空的.

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 和 Doctrine 并且完全放弃活动记录.我知道它不能解决这个问题，但我可能会放弃它并暂时保留我当前的解决方案.

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 执行 LEFT JOIN并检查 user_read_articles 中的列是否为 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',
) );

相关文章