CODEWITHSUNDEEP
cakephpcakephp-2.0has-and-belongs-to-manyjoin
usumoio
usumoio

Reputation: 3568

Cakephp habtm relation unable to correctly filter by tag

I have a habtm relation between users and tags. However, it works such that our list of tags is maintained separately from the users. Such that say there are currently exactly 12 tags and N users. As new users are added, tagged with different tags and deleted, the number of tags will not change, but the records in the taggings_users table will increase to accurately reflect everyone's tags. This part works.

Additionally, there is an interface that allows users to filter tagged users by their tags. This interface is designed such that if user A has tags Monday and Tuesday then they would show up in a search for either. Additionally, if user B has tags Monday and Wednesday and a search is preformed to search for users that include tag Monday and exclude tag Wednesday then user B should not show up (exclusions trump inclusions).

Cake is able to manage requiring included tags in a search, and separately, Cake is able to manage requiring tags to be excluded. The trouble is making Cakephp "Know" that when user C has tags X and Z and a search is preformed requested users including tag X and excluding tag Z that user C should NOT appear. Presently user C does appear in the results.

I realize that this is a result of the join operation that has to be preformed on the DB. i.e. since user C has two tags, that user also has two records returned from the DB. Cake correctly ignores one, but the other has the correct tag and allows user C to enter into result sets that are not intended for that user. Is there a way to make cake solve this issue. I can fix this on the server (removing user C in the controller, but I feel like I'm missing something easy). This is the query that gets the users with consideration for tags:

$conditions = array(
    'AND' => array(
        'Tagging.tag_name' => array(
            (int) 0 => 'tech'
        ),
        'NOT' => array(
            'Tagging.tag_name' => array(
                (int) 0 => 'monday'
            )
        )
    )
);

// I am also using the join options to preform this search
$joins[] = array(
    'table'         =>  'taggings_users',
    'alias'         =>  'TaggingUser',
    'type'          =>  'inner',
    'conditions'    =>  array(
        'User.id = TaggingUser.user_id'
    )
);

$joins[] =  array(
    'table'         =>  'taggings',
    'alias'         =>  'Tagging',
    'type'          =>  'inner',
    'conditions'    =>  array(
        'TaggingUser.tagging_id = Tagging.id'
    )
);

// which powers this operation
$this->paginate = array( 'conditions' => $conditions, 'joins' => $joins, 'limit' => 50 );

// there are users in here that should not be
$users = $this->paginate('User');

// thank you to anyone who helps.

Upvotes: 0

Views: 233

Answers (1)

Mark
Mark

Reputation: 3389

Without testing it I think it will work when you add another join:

$joins[] =  array(
    'table'         =>  'taggings',
    'alias'         =>  'Tagging2',
    'type'          =>  'inner',
    'conditions'    =>  array(
        'TaggingUser.tagging_id = Tagging2.id'
    )
);

Note that I changed "alias" to "Tagging2". And then

$conditions = array(
    'AND' => array(
        'Tagging.tag_name' => array(
            (int) 0 => 'tech'
        ),
        'NOT' => array(
            'Tagging2.tag_name' => array(
                (int) 0 => 'monday'
            )
        )
    )
);

Additionaly I dont think this "(int) 0" syntax is necessary. It should be enough to write

$conditions = array(
    'AND' => array(
        'Tagging.tag_name' => 'tech',
        'NOT' => array(
            'Tagging2.tag_name' => 'monday'
        )
    )
);

Upvotes: 1

Related Questions