Mysql Join:获取每个条目以及每行的总收藏夹 [英] Mysql Join:get total favourites for each item along with each row
问题描述
我的DB
1)cushbu_users
1)cushbu_users
id first_name last_name email
1 sh s sh@sh.com
2 sb s sh1@sh.com
2)cushbu_art
2)cushbu_art
id user_id title image_name
1 1 cool cool.jpeg
2 2 funny funny.jpeg
3 1 blaaa blaa.jpeg
4 2 foo foo.jpeg
3)cushbu_mark_user_favorites-存储收藏项的详细信息
3)cushbu_mark_user_favorites-Store the details of favourited item
id user_id art_id
1 1 1
2 1 2
3 2 1
4 2 2
As you see two users Favorited two arts so the total count
for favourite of each art is `two`
我希望获得每位用户最喜欢的艺术品,并且总喜欢它
I want to get the favourited arts of each user with total favourite
我为id = 1的用户提供的例外输出
My excepted output for user of id=1
art_id artist_name total_fav
1 sh s 2
2 sb s 2
这是对此的查询
SELECT
cushbu_art.id AS art_id,
cushbu_art.title,
cushbu_art.image_name,
CONCAT(
cushbu_users.first_name,
' ',
cushbu_users.last_name
) AS artist_name , count(cushbu_mark_user_favorites.id) as total_fav
FROM
cushbu_mark_user_favorites
LEFT JOIN cushbu_art ON cushbu_art.id=cushbu_mark_user_favorites.art_id
LEFT JOIN cushbu_users ON cushbu_users.id = cushbu_art.artist_id
WHERE cushbu_mark_user_favorites.user_id=1
GROUP BY cushbu_art.id
但是它返回
art_id artist_name total_fav
1 sh s 1
2 sb s 1
每行仅返回total_fav
1
,但排除输出2
returns total_fav
only 1
for each row but the excepted output 2
推荐答案
问题是您正在过滤WHERE cushbu_mark_user_favorites.user_id=1
,因此无法从其他用户那里获得收藏夹数.这个想法是第二次加入该表,但是没有这个矛盾.
推测,未经测试...
The problem is that you are filtering WHERE cushbu_mark_user_favorites.user_id=1
so it's not possible to get the number of favorites from other users. The idea is to join the table a second time, but without this contraints.
Speculating, untested...
SELECT
cushbu_art.id AS art_id,
cushbu_art.title,
cushbu_art.image_name,
CONCAT(
cushbu_users.first_name,
' ',
cushbu_users.last_name
) AS artist_name , b.favorites_count as total_fav
FROM
cushbu_mark_user_favorites
LEFT JOIN cushbu_art ON cushbu_art.id=cushbu_mark_user_favorites.art_id
LEFT JOIN cushbu_users ON cushbu_users.id = cushbu_art.artist_id
LEFT JOIN (SELECT art_id,count(*) as favorites_count FROM cushbu_mark_user_favorites GROUP BY art_id) as b ON b.art_id=cushbu_art.id
WHERE cushbu_mark_user_favorites.user_id=1
GROUP BY cushbu_art.id
这篇关于Mysql Join:获取每个条目以及每行的总收藏夹的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!