我想在技能最匹配的地方按降序向用户显示推荐用户。
问题是我在这个表单中的单个字段中存储技能
user_skill
musician,pop,singer
注:这将是输入音乐家,流行歌手,歌手。。
所以我想实现的是,我想向用户展示这三种技能都在顶端,然后是那些最终拥有两种技能的用户,以及那些只有一种技能的人。
所以输出将像
**user_name skills**
sam musician,pop,singer
smith musician,pop,singer
ali musician,singer
nasira musicain,pop
siri musician
taylor pop
andrew singer
这可以通过单个mysql查询实现吗?
如果这不可能,这可以通过php代码来完成。我不想改变表的结构,因为这需要大量的重做。
谢谢你的帮助。
您可能可以使用SQL,但查询会非常复杂,我可能不会。
在php方面,您可以迭代结果集,并在您要查找的给定技能集中统计每个user_name的技能,然后对它们进行排序:
$desired = explode(',', $input);
$users = array();
// i presume you have the query worked out to find users with any one of input
// skills attributed to them so lets say that $stmt is the PDO statement where you
// have executed that query
while (false === ($row = $stmt->fetch(PDO::FETCH_ASSOC))) {
$row['skills'] = explode(',', $row['skills']);
// assign an array containing only skills that were in your $input
// to $row['desired_skills']
$row['desired_skills'] = array_intersect($desired, $row['skills']);
$row['nb_desired_skills'] = count($row['desired_skills']);
$user[] = $row;
}
usort($users, function ($a, $b) {
return $b['nb_desired_skills'] - $a['nb_desired_skills'];
});
// now you can loop over $users and display the fields you need
然而,这些技能的定义和归属是您应用程序的关键部分,您现在应该规范化表格。。。推迟它只会成为一个更大的重构。
我不想改变表的结构,因为这需要大量的重做。
我强烈建议您制作一个单独的skills
表,并使用外键将其链接到users
表。一旦你的项目变得稍微复杂一点,你就会无限感激自己。
如果你不知道怎么做,我相信如果你发布另一个问题,这个网站上的人会帮助你编写一个转换脚本。
MySQL
CREATE TABLE skills
(
name VARCHAR(64) NOT NULL,
user_id INT NOT NULL,
PRIMARY KEY (name, user_id),
FOREIGN KEY (user_id) REFERENCES users(id)
);
通过这种方式,您可以使用简单的子查询(假设您有一个带有id
列的users
表)通过大多数技能轻松地选择用户:
MySQL
SELECT *,
(SELECT COUNT(*)
FROM skills
WHERE user_id = u.id) AS num_skills
FROM users u
ORDER BY num_skills DESC;
那么,为什么要采用这种方法而不依赖PHP呢
- 在数据库中创建最小的额外开销
- 显著通过不需要查询整个用户表(例如,如果您只想查询前10名用户)来减少数据库和Web服务器之间的聊天
- 可以在未来轻松地实现更复杂和变化更大的查询
下面的MySQL片段只查询那些拥有特定技能(例如"歌手")的用户,然后根据技能数量按降序对他们进行排序。查询不会返回零技能用户:
MySQL
SELECT *,
(SELECT COUNT(*)
FROM skills
WHERE user_id = u.id
AND name = 'singer') AS num_skills
FROM users u
WHERE num_skills > 0
ORDER BY num_skills DESC;
当然,您也可以通过id
搜索技能,只需将子查询中WHERE
子句的第二部分替换为AND id = 3
,即可查询ID为3的用户。
迈向更优化数据库的下一步是创建一个真正的skills
表,它只存储您在数据库中注册的技能,以及一个user_skills
表,它将它和users
表链接在一起。
这将使您能够从长远来看显著减小数据库大小,并能够运行不依赖于Web服务器的复杂但"干净"的查询。