How to show only rows which have no HABTM association in table

Question

I have a news feed app I'm writing and I would like to figure out a way to be able to mark each news article as having been "viewed" by each user individually. My current method I am trying to implement is to consider any rows in the articles_users table as meaning "this user has viewed this article" if they have an entry there associating their current user_id with the current article_id.

Here's what I have so far...

In my Article model:

App::uses('AuthComponent', 'Controller/Component');

class Article extends AppModel {

    public $hasAndBelongsToMany = array(
        'User' => array(
            'className' => 'User',
            'joinTable' => 'articles_users',
            'foreignKey' => 'article_id',
            'associationForeignKey' => 'user_id',
            'unique' => true
        )
    );

    public $findMethods = array('available' =>  true);

    protected function _findAvailable($state, $query, $results = array()) {
        if ($state === 'before') {
            $limit = (Configure::read('AppSettings.itemsperpage') ? Configure::read('AppSettings.itemsperpage'): '8');
            $query['limit'] = $limit;
            $query['order'] = 'Article.listdatetime DESC';
            if (!empty($query['operation']) && $query['operation'] === 'count') {
                return $query;
            }
            $query['joins'] = array(
                array(
                    'table' => 'articles_users',
                    'alias' => 'ArticlesUser',
                    'type' => 'INNER',
                    'conditions' => array(
                        'ArticlesUser.user_id' => CakeSession::read("Auth.User.id"),
                        'ArticlesUser.article_id <> Article.id'
                    )
                )
            );
            $query['group'] = 'Article.id';
            return $query;
        }
        return $results;
    }

    public $validate = array(
        'title' => array(
            'required' => array(
                'rule' => array('notEmpty'),
                'message' => 'A title is required'
            )
        ),
        'permalink' => array(
            'required' => array(
                'rule' => array('notEmpty'),
                'message' => 'A permalink is required'
            )
        ),
        'feed' => array(
            'required' => array(
                'rule' => array('notEmpty'),
                'message' => 'A feed is required'
            )
        )
    );

}

In my ArticlesController:

public function index() {
    $this->paginate = array('available');
    $articles = $this->paginate();
    $this->set(compact('articles'));
}

An export of the structure and contents of my articles_users table:

--
-- Table structure for table `articles_users`
--

CREATE TABLE IF NOT EXISTS `articles_users` (
`id` int(11) NOT NULL,
  `article_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `articles_users`
--

INSERT INTO `articles_users` (`id`, `article_id`, `user_id`) VALUES
(2, 3266, 4);

However, despite article_id 3266 being associated to user_id 4, I still see the article show up because under conditions I have ArticlesUser.article_id <> Article.id (not equal). In my index() method/action, how could I only display the articles which do not have an article_id associated to the current user_id?

articlesuser.article_id not in (select id from article where user_id = 4)? — franglais, Jul 15 '14 at 14:02
Thank you franglais, you got me really close with your comment. I used `'Article.id NOT IN (SELECT article_id FROM articles_users)'` in place of `'ArticlesUser.article_id <> Article.id'` — Ezra Free, Jul 15 '14 at 14:12
Yeah, I had a feeling it would be with that chunk! You may want to write this up as an answer to the question! — franglais, Jul 15 '14 at 14:14

score 0 · Answer 1 · answered Jul 15 '14 at 14:14

Thanks to franglais for their comment. In case anyone else has this issue, I have it working now with:

protected function _findAvailable($state, $query, $results = array()) {
    if ($state === 'before') {
        $limit = (Configure::read('AppSettings.itemsperpage') ? Configure::read('AppSettings.itemsperpage'): '8');
        $query['limit'] = $limit;
        $query['order'] = 'Article.listdatetime DESC';
        if (!empty($query['operation']) && $query['operation'] === 'count') {
            return $query;
        }
        $query['joins'] = array(
            array(
                'table' => 'articles_users',
                'alias' => 'ArticlesUser',
                'type' => 'INNER',
                'conditions' => array(
                    'ArticlesUser.user_id' => CakeSession::read("Auth.User.id"),
                    'ArticlesUser.article_id <> Article.id'
                )
            )
        );
        $query['group'] = 'Article.id';
        return $query;
    }
    return $results;
}

Perhaps I spoke too soon. I'm noticing now that this only works properly if the articles_users table has at least one row associating a user to an article. If the `articles_users` table is empty, then the index() method returns no rows. — Ezra Free, Jul 15 '14 at 14:47

How to show only rows which have no HABTM association in table

1 Answers1