所以我有一个准备好的SQL查询(如下),它根据用户的ID将4个表连接在一起。然而,最后的关系是一对多(一个用户可以有多个技能),所以我需要返回技能ID等于用户ID的所有行。目前,只返回freelancer_skill
表中与用户ID匹配的第一行。我如何让所有的行返回?
SELECT
u.user_id, u.firstname, u.lastname, u.email, u.bio, u.portfolio, u.location, u.time_joined, u.image_location,
f.freelancer_id, f.jobtitle, f.priceperhour,
ut.*,
ft.testimonial, ft.testimonial_source,
fs.skill, fs.skill_rating
FROM ((((users AS u
LEFT JOIN freelancers AS f
ON u.user_id = f.freelancer_id)
LEFT JOIN user_types AS ut
ON u.user_id = ut.user_type_id)
LEFT JOIN freelancer_testimonials AS ft
ON u.user_id = ft.testimonial_id)
LEFT JOIN freelancer_skills AS fs
ON u.user_id = fs.skill_id)
WHERE
u.confirmed = :confirmed
AND u.user_id = :userID
AND ut.user_type = :userType
AND u.granted_access = :grantedAccess
编辑
将用户类型WHERE
子句移动到join:
SELECT
u.firstname, u.lastname, u.email, u.bio, u.portfolio, u.location, u.time_joined, u.image_location,
f.jobtitle, f.priceperhour,
ut.user_type,
ft.testimonial, ft.testimonial_source,
fs.skill, fs.skill_rating
FROM ((((" . DB_NAME . ".users AS u
LEFT JOIN " . DB_NAME . ".freelancers AS f
ON u.user_id = f.freelancer_id)
LEFT JOIN " . DB_NAME . ".user_types AS ut
ON u.user_id = ut.user_type_id AND ut.user_type = :userType)
LEFT JOIN " . DB_NAME . ".freelancer_testimonials AS ft
ON u.user_id = ft.testimonial_id)
RIGHT JOIN " . DB_NAME . ".freelancer_skills AS fs
ON u.user_id = fs.skill_id)
WHERE
u.confirmed = :confirmed
AND u.user_id = :userID
AND u.granted_access = :grantedAccess
要获取结果,我使用fetch(下面)。我试过使用fetchALL,它确实返回用户持有的每个技能,但也返回每个技能的数据副本。
$results->execute();
$user = $results->fetch(PDO::FETCH_ASSOC);
return $user;
这里有一个SQLFiddle: http://sqlfiddle.com/#!2/d7bb3/4
过滤器放错地方了。声明一个左连接到user_types,但是在where子句中对该表进行过滤。这使它成为隐式内连接。
移动and ut.user_type = :userType
跟在后面:
LEFT JOIN user_types AS ut ON u.user_id = ut.user_type_id
这可能是问题的原因,但即使不是,使用where子句中的过滤器仍然会得到意想不到的结果。
改变连接的顺序。交换Users表和freelancer_skills表。-这显然不起作用
也许可以试试这样的东西。如果你想为一个人列出一百种技能,这是不理想的。
SELECT
u.firstname,
u.lastname,
u.email, u.bio, u.portfolio, u.location, u.time_joined, u.image_location,
f.jobtitle, f.priceperhour,
ut.user_type,
ft.testimonial, ft.testimonial_source,
count(case when fs.skill = 'HTML5' then 1 end) as HTML5,
count(case when fs.skill = 'CSS3' then 1 end) as CSS3,
count(case when fs.skill = 'PHP' then 1 end) as PHP,
fs.skill_rating
FROM
(
(
(
(
users AS u
LEFT JOIN freelancers AS f ON
u.user_id = f.freelancer_id
)
LEFT JOIN user_types AS ut ON
u.user_id = ut.user_type_id AND
ut.user_type = 'developer'
)
LEFT JOIN freelancer_testimonials AS ft ON
u.user_id = ft.testimonial_id
)
RIGHT JOIN freelancer_skills AS fs ON
u.user_id = fs.skill_id
)
WHERE
u.confirmed = '1'
AND u.user_id = '3'
AND u.granted_access = '1'
group by
u.firstname,
u.lastname,
u.email, u.bio, u.portfolio,
u.location,
u.time_joined, u.image_location,
f.jobtitle, f.priceperhour,
ut.user_type,
fs.skill_rating