CakePHP conditional query with controller that 'hasAndBelongsToMany'

I apologize for the horrible title, I couldn't think of how to explain my problem.

In my database I have the following tables, articles, tags, and articles_tags. An article can have many tags.

Currently I am able to grab all the articles, with all the tags, but I want to be able to find articles based upon it's tags.

My select is simple:

$articles = $this->Article->find('all', array(
    // extra condition to check for tag, maybe?
    'conditions'    => array('Article.status' => 'active'), 
    'limit'         => $this->articles_per_page,
    'offset'        => ($page_num-1)*$this->articles_per_page
));

My return from the database is as follows:

Array
(
[0] => Array
    (
        [Article] => Array
            (
                [id] => 1
            )

        [Tag] => Array
            (
                [0] => Array
                    (
                        [id] => 1
     开发者_JS百科                   [name] => Ruby
                        [slug] => ruby
                        [uses] => 1
                        [ArticlesTag] => Array
                            (
                                [id] => 1
                                [article_id] => 1
                                [tag_id] => 1
                            )

                    )

            )

    )

What do I do if I only want to return the articles with a Ruby tag?

Try this

// In your Article model
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.name"=>Sanitize::clean($tag_words)),
                    'order' => null,
                    'group' => "ArticlesTag.article_id"
                    ),
                    $this
                    );
                    $subQuery = ' Article.id  IN (' . $subQuery . ')';
                    return $dbo->expression($subQuery);

}

// In your Articles Controller

$this->paginate['conditions'][] = $this->Article->getArticleByTagSql($tag_name);
$this->paginate['conditions'][] = array('Article.status' => 'active');
$this->paginate['limit'] = $this->articles_per_page;



// or as per your example
$articles = $this->Article->find('all', array(
                // extra condition to check for tag, maybe?
                'conditions'    => array('Article.status' => 'active',$this->Article->getArticleByTagSql($tag_name)), 
                'limit'         => $this->articles_per_page,
                'offset'        => ($page_num-1)*$this->articles_per_page
            ));

For conditions like this you can use the LinkableBehavior. It's designed for exact your wanted result. In case you don't want to use it, you have to do a query on the tag controller:

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

Not possible directly.

The easiest way is to make the query through the tag controller

I'm updating this question with an answer for CakePHP 3.x.

You can find all articles for a given tag using an INNER JOIN. The benefit of this join is that it keeps articles as the primary table in the query. Making this an easy solution when you're paginating results by a HABTM join.

$tag_id = 3; // the ID of the tag

$query = $this->Articles->find('all')
                 ->innerJoin('articles_tags', [
                      'Articles.id = articles_tags.article_id',
                      'articles_tags.tag_id' => $tag_id
                 ]);

The first join condition has to be the array key. If you use a => array assignment then CakePHP will pass the articles_tags.article_id as a string argument to the join condition. Which won't work.

You can also stack innerJoin for multiple HABTM conditions. For example; find all articles for a tag and also for a category. You can also use the in query expression to match multiple tags.

You can also use the above in pagination like this $articles = $this->paginate($query);

I'm sure this information is out on the web, but this question was the top of google's results. So maybe this will help others.