CODEWITHSUNDEEP
mysql
mvasco
mvasco

Reputation: 5107

Not getting the right results using GROUP_CONCAT in query

I have 7 tables to work with inside a query:

tb_post, tb_spots, users, td_sports, tb_spot_types, tb_users_sports, tb_post_media

This is the query I am using:

SELECT po.id_post AS id_post, 
po.description_post as description_post,
 sp.id_spot as id_spot, 
po.date_post as date_post,
 u.id AS userid, 
u.user_type As tipousuario, 
u.username AS username, 
spo.id_sport AS sportid, 
spo.sport_icon as sporticon, 
st.logo_spot_type as spottypelogo, 
sp.city_spot AS city_spot, 
sp.country_spot AS country_spot, 
sp.latitud_spot as latitudspot,
 sp.longitud_spot as longitudspot,
 sp.short_name AS spotshortname, 
sp.verified_spot AS spotverificado, 
 u.profile_image AS profile_image,
sp.verified_spot_by as spotverificadopor,
 uv.id AS spotverificador,
 uv.user_type AS spotverificadornivel,
 pm.media_type AS mediatype,
 pm.media_file AS mediafile,

GROUP_CONCAT(tus.user_sport_sport) sportsdelusuario,
GROUP_CONCAT(logosp.sport_icon) sportsdelusuariologos,
GROUP_CONCAT(pm.media_file) mediapost,
GROUP_CONCAT(pm.media_type) mediaposttype

FROM tb_posts po 

LEFT JOIN tb_spots sp ON po.spot_post = sp.id_spot 
LEFT JOIN users u ON po.uploaded_by_post = u.id 
LEFT JOIN tb_sports spo ON sp.sport_spot = spo.id_sport
LEFT JOIN tb_spot_types st ON sp.type_spot = st.id_spot_type
LEFT JOIN users uv ON sp.verified_spot_by = uv.id
LEFT JOIN tb_users_sports tus ON tus.user_sport_user = u.id
LEFT JOIN tb_sports logosp ON logosp.id_sport = tus.user_sport_sport
LEFT JOIN tb_post_media pm  ON pm.media_post = po.id_post 

WHERE po.status = 1
GROUP BY po.id_post,uv.id

I am having problems with some of the GROUP_CONCAT groups:

GROUP_CONCAT(tus.user_sport_sport) sportsdelusuario is giving me the right items but repeated, all items twice

GROUP_CONCAT(logosp.sport_icon) sportsdelusuariologos is giving me the right items but repeated, all items twice

GROUP_CONCAT(pm.media_file) mediapost is giving me the right items but repeated four times

GROUP_CONCAT(pm.media_type) mediaposttype s giving me the right items but repeated four times

I can put here all tables structures if you need them.

Upvotes: 0

Views: 32

Answers (2)

PeterHe
PeterHe

Reputation: 2766

or just add DISTINCT to all the group_concat, e.g., GROUP_CONCAT(DISTINCT pm.media_type)

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15941

Multiple one-to-many relations JOINed in a query have a multiplicative affect on aggregation results; the standard solution is subqueries:

You can change 

GROUP_CONCAT(pm.media_type) mediaposttype
...
LEFT JOIN tb_post_media pm  ON pm.media_post = po.id_post

to

pm.mediaposttype
...
LEFT JOIN (
   SELECT media_post, GROUP_CONCAT(media_type) AS mediaposttype 
   FROM tb_post_media 
   GROUP BY media_post
) AS pm ON pm.media_post = po.id_post

If tb_post_media is very big, and the po.status = 1 condition in the outer query would significantly reduce the results of the subquery, it can be worth replicating the original join within the subquery to filter down it's results.

Similarly, the correlated version I mentioned in the comments can also be more performant if the outer query has relatively few results. (Calculating the GROUP_CONCAT() for each individually can cost less than calculating it for all once if you would only actually using very few of the results of the latter).

Upvotes: 1

Related Questions