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.