I want to GROUP LEFT JOIN rows in my query,with another, like this:
[ID] => 1,
[user_email] => 'example@example.com',
[user_registered] => '2017-10-13 14:39:20',
[preferences_id] => 1,
[uid] => 1,
[user_social_links] => Array
(
[1 *social_id index*] => Array
(
[name] => FACEBOOK
[value] => http://facebook.com/url
[display] => 1
)
[2 *social_id index*] => Array
(
[name] => TWITTER
[value] => http://twitter.com/url
[display] => 1
)
)
I have these tables:
users : -------------------------------------------------- | ID | user_email | user_registered | -------------------------------------------------- | 1 | example@org.com |2017-10-13 14:39:20 | -------------------------------------------------- | 3 | example2@org.com |2017-10-13 15:20:05 | -------------------------------------------------- user_account: -------------------------------------------------- | preferences_id | uid | user_nicename | -------------------------------------------------- | 1 | 1 | example nice name1 | -------------------------------------------------- | 2 | 3 | example nice name2 | -------------------------------------------------- user_social_links : ------------------------------------------------------------ | social_id | uid | name | value | display | ------------------------------------------------------------ | 1 | 1 | FACEBOOK | fb.me/example | 1 | ------------------------------------------------------------ | 2 | 1 | TWITTER | twitter.com/example| 1 | ------------------------------------------------------------
My query is :
SELECT
user_account.preferences_id,user_account.uid,user_account.user_nicename,
users.ID,users.user_email,users.user_registered,user_social_links.social_id,user_social_links.value,user_social_links.name,user_social_links.display,
FROM user_account
LEFT JOIN user_social_links
ON user_social_links.uid = user_account.uid
LEFT JOIN users
ON users.ID = user_account.uid
WHERE user_account.uid = :userid
Output:
Array
(
[0] => Array
(
[preferences_id] => 1
[uid] => 1
[user_nicename] => example nice name1
[ID] => 1
[user_email] => example@org.com
[user_registered] => 2017-10-13 14:39:20
[social_id] => 1
[value] => fb.me/example
[name] => FACEBOOK
[display] => 1
)
[1] => Array
(
[preferences_id] => 1
[uid] => 1
[user_nicename] => example nice name1
[ID] => 1
[user_email] => example@org.com
[user_registered] => 2017-10-13 14:39:20
[social_id] => 2
[value] => twitter.com/example
[name] => TWITTER
[display] => 1
)
)
Note: I can't use GROUP BY user_account.uid (#1055 only_full_group_by error mysql).¿How I can get first result example? I want to do in one query,not use foreach (PHP). Please anyone can help me? best regards.
In MySQL 5.7 you could enable GROUP BY on aggregated columns by overriding the default ONLY_FULL_GROUP_BY sqlmode using SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); as explained at Error Code: 1055 incompatible with sql_mode=only_full_group_by