复杂的搜索问题

Question

我有表款（表）在我的数据库字段名称，如

tags     (id, name) 
taggings    (id, tag_id, taggable_id, taggable_type, context) 
employment_histories (id, user_id, grades, subjects, my_interests) 
users    (id) 

taggable_id实际上是employment_histories_id和背景可以是等级或拍摄对象或my_interests

现在我有一些标签例如g = {“9th”，“10th”} 我想获得用户，只有其标签都与上面的g数组匹配。

我已经写了下面的查询：

SELECT DISTINCT users.* FROM `users` 
LEFT OUTER JOIN `employment_histories` 
    ON `employment_histories`.`user_id` = `users`.`id` 
LEFT OUTER JOIN `taggings` 
    ON `employment_histories`.`id` = `taggings`.`taggable_id` 
    AND `taggings`.`taggable_type` = 'EmploymentHistory' 
LEFT OUTER JOIN `tags` ON taggings.context = 'subjects' 
WHERE tags.name='9th' OR tags.name='10th' 

，但它给我的用户太多，匹配任何标记，然而我想，这将只返回谁所有的两个标签相匹配的用户

假设标签9和10的标签ID为9和10，那么我想要它只会返回taggable_id（这是employmenthistories.id）谁拥有这两个tag_id（即9和10）的共同taggable_id标记表

例如我有两个用户tariq和kamal，这两个用户都有第9个标签，但kamal没有标签第10个，所以需要查询哪个如果传递这两个标签应该只返回tariq，其标签都是marahing这两个标签，但用户喜欢kamal匹配任何标签也应过滤

Answer 1

从php chat room：

SELECT 
users.* , 
count(*) AS count 
FROM users 
    LEFT JOIN employment_histories ON users.id = employment_histories.user_id 
    LEFT JOIN tagging ON tagging.taggable_id = employment_histories.id 
    LEFT JOIN tags ON tags.id = tagging.tag_id 
WHERE tags.name = "9th" 
     OR tags.name = "10th" 
GROUP BY users.id 
HAVING count = 2 

Answer 2

SELECT users.* FROM users 
INNER JOIN employment_histories 
    ON employment_histories.user_id = users.id 
INNER JOIN taggings 
    ON employment_histories.id = taggings.taggable_id 
    AND taggings.taggable_type = 'EmploymentHistory' 
    AND taggings.context = 'subjects' 
INNER JOIN tags ON tags.id = taggings.tag_id 
WHERE tags.name IN ('9th','10th') 
GROUP BY users.id 
HAVING COUNT(DISTINCT(tags.name)) = 2; 

Answer 3

我已重新编写查询。

一些变化：

• 上tags.id = taggings.tag_id
• 删除或从whe​​re子句中加入标签，并使用，提高了性能。

 
    SELECT DISTINCT users.*, count(*) as totRow FROM `users` 
    LEFT OUTER JOIN `employment_histories` 
    ON `employment_histories`.`user_id` = 
    `users`.`id` LEFT OUTER JOIN 
    `taggings` ON 
    `employment_histories`.`id` = 
    `taggings`.`taggable_id` AND 
    `taggings`.`taggable_type` = 
    'EmploymentHistory' AND 
    `taggings`.`context` = 'subjects' 
    LEFT OUTER JOIN `tags` ON `tags`.`id` = `taggings`.`tag_id` 
    WHERE tags.name = '9th' or tags.name = '10th' 
    GROUP BY `users`.`id`