LEFT JOIN GROUP结果与PK相同

Question

I want to GROUP LEFT JOIN rows in my query,with another, like this:

   [ID] => 1,
   [user_email] => '[e-mail]',
   [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
• user_account
• user_social_links

users :
--------------------------------------------------
| ID | user_email        | user_registered       |
--------------------------------------------------
|  1  | [e-mail]  |2017-10-13 14:39:20    |
--------------------------------------------------
|  3  | [e-mail] |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] => [e-mail]
            [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] => [e-mail]
            [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.

Answer 1

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