CakePHP Getting Associated Posts from Tag

I've got three tables

• posts
• tags
• posts_tags

I need to write some controller logic that selects and sets all posts that have the tag work

This means querying the tags to find the id for the queried tag, checking in the associated posts_tags table to find matches, using the post_id to return the correct posts from the posts table.

I'm not sure how to even begin this query, I'm new to CakePHP and could use a hand... Please?

If it helps, here's my posts model relationship:

var $hasAndBelongsToMany = array(
        'Tag' => array(
            'className' => 'Tag',
            'joinTable' => 'posts_tags',
            'foreignKey' => 'posts_id',
            'associationForeignKey' => 'tag_id',
            'unique' => true,
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'finderQuery' => '',
            'deleteQuery' => '',
            'insertQuery' => ''
        )
    );

My Tag table is made up of two fields: id and tag

My PostsTag table is made up of three fields: id, tag_id and post_id

My Posts table is made up of four fields: id, title, body and created

I found some code on the CakePHP Book that shows the below code:

$this->Recipe->Tag->find('all', array('conditions'=>array('Tag.name'=>'Dessert')));

They suggested that it was a similar idea, so I attempted to adapt:

$this->Post->Tag->find('all', array('conditions'=>array('Tag.tag'=>'work')));

However, this has not worked. This returns all Posts without filtering.

I took the advice of @Leo and tried to adapt his code to mine:

function getArticleByTagSql($tag) {
        $dbo = $this->getDataSource();
        $subQuery = $dbo->buildStatement(
                        array(
                    'fields' => array('DISTINCT(ArticlesTag.article_id)'),
                    'table' => "articles_tags",
                    'joins' => array(
                        array('table' => 'tags',
                            'alias' => 'Tag',
                            'type' => 'INNER',
                            'conditions' => array('ArticlesTag.tag_id = Tag.id')
                        )
                    ),
                    'alias' => "ArticlesTag",
                    'conditions' => array("Tag.tag" => $tag),
                    'order' => null,
                    'group' => "ArticlesTag.article_id",
                    'limit' => null
                        ), 
                $this
        );
        $subQuery = ' Article.id  IN (' . $subQuery . ')';
        return $dbo->expression($subQuery);
    }

Controller:

$this->set('articles', $this->paginate(array(
            'conditions' => $this->Article->getArticleByTagSql('work')
        )));

However, whatever I type in the paginate() method as a key - in this case 'conditions' appears in the query and I can't figure out why - I keep getting unknown column 'conditions' errors. The error is with the paginate function, the data returns correctly without it, but I cannot use the paginator without it. It's like a c开发者_开发技巧atch 22

Cheers,

Dan

if you use:

$this->Post->Tag->find('all'...

you're telling him to find all Tags work... and since its recursive, each tag will contain the related posts..

try doing this instead:

$this->Post->find('all'...

hopefully cake will be smart enough (and if you have correctly set your models relations) to only select posts that have the "work" tag.. and if that doesnt work, you could always set the joins "manually" using a complex find condition

Good Luck

Daniel this question is actually answered in the Cake documentation when describing the HABTM relationship betweens Receipe/Tag

I answered this question for someone who had a similar issue with an Article/Tag models, that answer used a subquery

I had same problem, but I tried this query and it worked for me...May be it will work for you too :)

$claims = $this->Claim->query("SELECT DISTINCT Claim.id,Claim.title, Claim.description FROM
             claims as Claim
             LEFT JOIN claim_tags as ClaimTag ON Claim.id = ClaimTag.claim_id
            LEFT JOIN tags as Tag ON Tag.id =ClaimTag.tag_id
            WHERE Tag.id = '$id'");