php - CakePHP Complex 查找/查询 - 改进当前解决方案

Question

我正在用 CakePHP(最新版本)编写标签搜索，但与 CakePHP 的其余部分的简单程度相比，我所做的解决方案似乎过于复杂。希望有人能为我指明正确的方向或帮助我改进当前的解决方案。

我的应用程序中的每个用户都可以使用标签来标记自己，例如:php、objective-c、javascript、jquery。不同类型的用户可以搜索具有特定标签的用户。他们可能会搜索:php、objective-c、ios。我需要按照匹配的标签数量的顺序返回一个用户数组，具有所有 3 个标签的用户将出现在数组的顶部。

下面是数据库示例和我的解决方案。如果能帮助我改进这一点，我将不胜感激。

[数据库]

[解决方案]

                //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

请试一试:

$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) 应该给出:

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

希望对你有用。如果您还想知道每个用户在同一查询中有哪些标签，只需调整包含或递归值即可。