Yii Relational record with condition from other table

Question

I have a relational record similar to the following:

• There are users (table users) with ids
• There are categories (table categories) with id, name
• There are articles (table articles) with id, body, category_id
• Then there is a table read_articles with article_id, user_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.

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.

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

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

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

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

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.

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.

Answer 1

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

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

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