CakePHP Complex find/query - Improve current solution

Question

I'm coding a tag search in CakePHP (latest release) but the solution I have made seems over complicated compared to how easy the rest of CakePHP is. Hopefully someone can point me in the right direction or help me improve my current solution.

Each user in my app can tag themselves with tags, for example: php, objective-c, javascript, jquery. A different type of user can search for users with specific tags. They may search for: php, objective-c, ios. I need to return a array of users in the order of how many tags they match, a user with all 3 tags would appear at the top of the array.

Here's a sample of the database and my solution below. I'd really appreciate any help on improving this.

[Database]

[Solution]

                //Search Array
            //Format: array('objective-c', 'javascript', 'jquery', 'php')
            $tag_array = explode(",", $this->request->data["User"]["search"]);

            //Get Tag IDs - array([id] => [id])
            //Format: array([1] => '1', [2] => '2', [4] => '4', [15] => '15')
            $result_tag_ids = $this->User->TagsUser->Tag->find('list', array(
                'conditions' => array(
                    "Tag.name" => $tag_array
                ),
                'fields' => array('Tag.id')    
            ));

            //Get User IDs - array([id] => [id])
            //Format: array([24] => '24', [24] => '24', [26] => 26, [27] => '27')
            $result_user_ids = $this->User->TagsUser->find('list', array(
                'conditions' => array(
                    "TagsUser.tag_id" => $result_tag_ids
                ),
                'fields' => array('TagsUser.user_id')     
            ));


            //Remove Duplicate user ids and add a count of how many tags matched & sort the array in that order
            //Format:  array([26] => 1, [24] => 2, [27] => 3)
            $counted_user_ids = array_count_values($result_user_ids);
            asort($counted_user_ids);


            //Get the keys (user_ids)
            $list_user_ids = array_keys($counted_user_ids); 

            //Get these users in the order of the sorted array
            $search_result = $this->User->find('all', array(
                'conditions' => array(
                    "User.id" => $list_user_ids
                ),
                'order' => 'FIELD(User.id,' . implode(" ,", $list_user_ids) . ')'

            ));

Answer 1

please give this a try:

$tag_array = explode(",", $this->request->data["User"]["search"]);
//arrays expanded for better readability, 
//you should be able to compress in fewer lines if desired

$options = array();
$options['contain'] = ''; 
//or recursive=-1, depends of what you are using to avoid extra models/fields

$options['joins'][0]['table'] = 'tags_users';
$options['joins'][0]['conditions'] = 'User.id = user_id';
$options['joins'][1]['alias'] = 'Tag';
$options['joins'][1]['table'] = 'tags'; 
$options['joins'][1]['conditions']= 'Tag.id = tag_id';
$options['fields'] =  array('User.id', 'COUNT(*) as tag_counter');
$options['group']  =  'User.id';
$options['order']  =  'tag_counter DESC';
$options['conditions']['Tag.name'] = $tag_array;
$search_result = $this->User->find('all', $options);

print_r($search_result) should give:

    Array
    (
        [0] => Array
            (
                [User] => Array
                    (
                        [id] => (user id)
                    )
                [0] => Array
                    (
                        [tag_counter] => (how many tags)
                    )
            )
        [...]
    )

I hope it works for you. If you want to know also what tags each user have on the same query just adjust the contain or recursive value.