如何将表1中的所有记录和tabla 2中的元数据一起获取？

Question

I want to know if theres any other way to get all record from table 1 (users) and all the meta data on table 2 (user_meta), I'm using right now this way:

SELECT 
u.id, 
u.username, 
u.email, 
r.role,
(SELECT val FROM user_meta WHERE id_parent = u.id AND attr = 'sidebar_color' LIMIT 1) AS sidebar_color,
(SELECT val FROM user_meta WHERE id_parent = u.id AND attr = 'profile_pic' LIMIT 1) AS profile_pic,
(SELECT val FROM user_meta WHERE id_parent = u.id AND attr = 'thumbnail' LIMIT 1) AS thumbnail,
(SELECT val FROM user_meta WHERE id_parent = u.id AND attr = 'bg_img' LIMIT 1) AS bg_img,
(SELECT val FROM user_meta WHERE id_parent = u.id AND attr = 'bio' LIMIT 1) AS bio,
(SELECT val FROM user_meta WHERE id_parent = u.id AND attr = 'google' LIMIT 1) AS google,
(SELECT val FROM user_meta WHERE id_parent = u.id AND attr = 'facebook' LIMIT 1) AS facebook,
(SELECT val FROM user_meta WHERE id_parent = u.id AND attr = 'twitter' LIMIT 1) AS twitter,
(SELECT val FROM user_meta WHERE id_parent = u.id AND attr = 'whatsapp' LIMIT 1) AS whatsapp
FROM users u 
LEFT JOIN roles r ON u.id_role = r.id
ORDER BY u.id
ASC

And well it's working, i get all data from all users but I would like another way around, because I will be using the same approach for a CRM but this one is gonna have so many meta data and it's gonna be a huge query to maintain.

Thank you.

Answer 1

Your method is fine and under many circumstances is the fastest. But aggregation is also viable:

SELECT um.id_parent,
       MAX(CASE WHEN um.attr = 'sidebar_color' THEN val END) as sidebar_color,
       MAX(CASE WHEN um.attr = 'profile_pic' THEN val END) as profile_pic,
       . . .
FROM user_meta um
GROUP BY um.id_parent;

This would typically be a subquery in the FROM clause.

For getting information on all users, this can be faster than the JOIN method as the number of keys gets large. However, if you are filtering down to one or a handful of users, you want to do the filtering before the aggregation for good performance.