我正在写一个旅行的应用程序.
>用户注册自己
>用户告诉应用程序,如果他们是男性或女性
>用户告诉应用程序他们想访问哪些国家
>用户告诉应用程序,如果他们想与男性(pref_m = 1)或女性(pref_f = 1)旅行,
我的桌子
表1：用户
id(key)|性别| pref_m | pref_f
————————————
1男1 0
2男1 1
表2：国家选择
id(key)| userid | countryid
————————————
1 1 123
2 1 111
3 1 100
4 1 110
5 2 123
6 2 111
7 2 202
8 2 210
那么select语句必须做什么
输入：当前用户的userid
输出(逻辑)：选择所有想要与同一个国家旅行的人的用户名和匹配的国家,并且想和有性别的人一起旅行
(加入)在这个选择中,我显然只需要我正在寻找的性别的人.
由具有最匹配国家的人订购DESC.
我到目前为止(警告：不多)
$sql =“SELECT userid,count(*)AS matches from countryselection”;
$sql.=“WHERE countryid IN(SELECT countryid FROM countryselection WHERE userid =：userid)GROUP BY userid ORDER BY matches DESC;”;
这给了我一个想要和我一样旅行到同一个国家的人的名单(以及我们有多少个国家)
最后的说明
我显然是与性别选择部分挣扎.
不知道我是否以正确的方式存储用户选择.
我也可能需要一些指导.
显然 – 谢谢所有.
SELECT
us2.id,-- etc.
COUNT(cs2.countryid) as countries_in_common
FROM
countryselection cs1 -- let's gather user countries he want to visit
LEFT JOIN -- now let's find other users!
countryselection cs2 ON
(
cs2.userid <> :userid AND -- which are not him
cs2.countryid = cs1.countryid -- and want to visit same countries
)
INNER JOIN -- let's grab our user_data
users us1 ON
(
us1.id = cs1.userid
)
INNER JOIN -- and let's grab other user data!
users us2 ON
(
us2.id = cs2.userid
)
WHERE
cs1.userid = :userid AND -- finding our user countries he want to visit
-- final checks
(
(us1.pref_m = 1 AND us2.gender = 'male')
-- he is looking for male and second user is male
OR
(us1.pref_f = 1 AND us2.gender = 'female')
-- he is looking for female and second user is female
) AND
(
(us2.pref_m = 1 AND us1.gender = 'male')
OR
(us2.pref_f = 1 AND us1.gender = 'female')
)
GROUP BY
cs2.userid -- finally group by user_id
