Searching CakePHP HABTM relationship for results where 'all' terms match, not 'any'

Question

In CakePHP 2.4, I'm trying to generate conditions for a search query that searches for data that has been tagged across a HABTM relationship.

My problem is twofold: I can't get my query to return only data tagged with ALL of my search terms, and I can't get my query to return results for partial tags.

This loop generates a working query returning data with ANY of the tags in the search query.

foreach($tags as $tag) {
        $conditions['Tag.name'][] = $tag;
}

$query = $this->Tagged->getQuery('all', array(
    'conditions' => $conditions,
    'fields' => array('foreign_key'),
    'contain' => array('Tag')
));

I'd like to get the loop to generate conditions returning only data tagged with ALL the search terms, not data tagged with any of them, and to return matches for partial terms.

EDIT 2:

My database schema looks like this (I'm using the CakeDC Tags plugin to add tags)

Posts

id | other_data

    public $hasAndBelongsToMany = array(
    'Tag' => array(
        'with' => 'Tagged'));

Tagged

id | model | foreign_key | tag_id

Tags

id | name

This is the join I'm trying to use: It complains when I use Tag as an alias: Not unique table/alias: 'Tag'.

$joins = array(
    array(
        'table' => 'tags',
        'alias' => 'queryTag', 
        'type' => 'LEFT',
        'conditions' => array(
            'queryTag.name' => 'keyword'
        )
    ),
);

$query = $this->Tagged->getQuery('all', array(
    //'conditions' => array('Tag.name LIKE'  => '%' . $data['tags'] . '%'),
        'joins' => $joins,
        'fields' => array('foreign_key'),
        'contain' => array('Tag')
    ));
return $query;

your query structure isn't appropriate for your needs - you need a join per tag, not a condition per tag. — AD7six, Apr 12 '14 at 20:53
So if I understand you right, should I be running a search for each tag, and then doing an `array_intersect()` to get my final results? It sounds like it would be slow. — caitlin, Apr 12 '14 at 20:55
Nope. That would be slow and/or unviable on even a slightly not-small DB. Achieve `$taggedFooAndBar = SELECT * from x join tags on (... tag.name = "Foo") join tags on (... tag.name = "Bar")` - i.e. a join per tag. — AD7six, Apr 12 '14 at 20:57
Gotcha! What do you mean by the `...`? I'm trying to figure out the right way to do this, the Cake docs I've found don't show this being done with a SQL string, but rather with arrays, like my update above. — caitlin, Apr 12 '14 at 22:30
I'm sure I've answered question of this type before. You need to know the sql that will work for you before trying to put it into your app. Until you know the right sql, the question isn't "how do I do this in CakePHP" but "What sql will allow me to do x" - ask the right questions to get the right answers. The answer below hints at the right query. if you are searching by tag_id you need to join tagged_tags once per "condition" if you are searching by tag name you need to join tags once per "condition". — AD7six, Apr 14 '14 at 15:09

score 2 · Answer 1 · answered Apr 13 '14 at 08:09

Its hard to tell without your full database schema but i think you need to join two tables like-

$options['joins'] = array(
                        array(
                            'table' => 'tagged_tags',
                            'alias' => 'TaggedTag', 
                            'type' => 'LEFT',
                            'conditions' => array(
                                'TaggedTag.tagged_id' => 'Tagged.id'
                            )
                        ),
                        array(
                            'table' => 'tags',
                            'alias' => 'Tag', 
                            'type' => 'LEFT',
                            'conditions' => array(
                                'TaggedTag.tag_id' => 'Tag.id'
                            )
                        )
);

Searching CakePHP HABTM relationship for results where 'all' terms match, not 'any'

1 Answers1