How to get all tags that do NOT have posts associated in CakePHP

Question

Hi,

I'm doing a blog in CakePHP, so I have two tables in my database that are HABTM related: posts and tags. Because they are HABTM related I also have a poststags table keeping track of relations.

I'd like to have a method in my tags_controller that will delete all unused tags.

How do I find all tags that are NOT associated with any posts?

Answer 1

You could use the following statement to delete all unused tags:

$this->query('delete from tags where not exists (select * from posts_tags where posts_tags.tag_id = tags.id)');

(And to find all tags not associated with any posts simply replace the "delete" with "select *")

Answer 2

$this->Tag->find('all', array(
'conditions' => array(
    'PostsTag.id IS NULL'
),
'joins' => array(
    array(
        'table' => 'poststags',
        'alias' => 'PostsTag',
        'type' => 'LEFT',
        'conditions' => array(
            'PostsTag.tag_id' => 'Tag.id',
        ),
    ),
),

));