Запрос с объединением в MySQL

Question

[recky]

Имеется БД с двумя таблицам users (user_id, email, password, user_type) и user_profiles (user_id, profile_key, profile_value). Хранятся в них, соответсвенно, основные пользовательские данные и вторичные (пол, телефон, адрес и т. д.) Нужно сделать их объединение средствами исключительно SQL, так чтобы на выходе иметь один массив вида:
array {
[«user_id»]=> «1»
[«username»]=> «recky»
[«password»]=> «qwerty»
[«user_type»]=> «admin»
[«sex»] => «iAdmireIt»
[«hobbie»] => «playing tambourine»
}
Если делать «SELECT `u`.*, `up`.* FROM `users` AS `u`
INNER JOIN `users_profile` AS `up`», то на выходе я получаю 2 массива с полями profile_key и profile_value с разными значениями в каждом.

Возможно ли средствами SQL реализовать это? И как?

Answer 1

[Loki3000]

Вы не указали признак объединения:
SELECT * FROM table1 t1 JOIN table2 t2 ON t1.user_id=t2.user_id

Comments

[Loki3000]

А вообще перечень полей в выборке лучше указывать явным образом, чтобы не было никаких сюрпризов.

[recky]

ну да, я просто через Zend_Db ООП запрос делал, не люблю синтаксис SQL :) Однако, суть та же. При выполнении этого запроса («SELECT * FROM users u JOIN users_profile up ON u.user_id=up.user_id») классическими процедурными средствами PHP я получаю вообще один массив с последними считанными значениями profile_key и profile_value. Суть в том, что их может быть для одного пользователя много разных. Zend_Db даёт на выходе столько массивов, сколько во второй таблице полей с одним user_id. А нужно как-то всё это дело в виде одного массива вывести.
Как указать, что нужно вывести данные в виде profile_key => profile_value и вообще, возможно ли это?

Answer 2

[apangin]

В MySQL нет понятия «массив», там есть записи (строки) и поля (столбцы). Вы хотите, чтоб MySQL вернул все данные одного пользователя в одной записи? Это можно, с помощью, скажем, GROUP_CONCAT, но неправильно — зачем тогда вообще вторая таблица нужна, храните все в одной.

Правильным способом будет указанный выше запрос с INNER JOIN. Для того, чтобы собрать полученные данные в один массив, понадобится обойти полученный RecordSet в цикле по записям.

Comments

[recky]

>Вы хотите, чтоб MySQL вернул все данные одного пользователя в одной записи?
Не совсем. Я понимаю, что в MySQL нет понятия «массив», просто в итоге я всё равно работаю с массивом. Вот я и хочу, чтобы была такая структура данных, как я в вопросе указал. Т.е. чтобы profile_key => sex, profile_value => iAdmireIt превращался на выходе в sex => iAdmireIt. GROUP_CONCAT, насколько я понял, не подходит, ибо действительно сливает всё в одну запись. А мне нужно, чтоб MySQL на выходе выдал поля с именами из profile_key и значениями из profile_value.
Вторая таблица нужна, чтобы каши не было. Потому что, повторюсь, нужно хранить разные кастомные значения полей профиля помимо основных.
Понятное дело, что когда я уже пофетчу результат в массивы, я могу пробежаться по ним и превратить всё в нужный вид, но если кастомных полей будет много, то не лучше ли будет отдельный запрос для них сделать?
В общем, я просто хотел как-то элегантно это сделать, но, видимо, никак :(

[CKOPOBAPKuH]

> Правильным способом будет указанный выше запрос с INNER JOIN. Для того, чтобы собрать полученные данные в один массив, понадобится обойти полученный RecordSet в цикле по записям.

это — неправильный способ. если у юзера 10 атрибутов, зачем вам гонять туда-сюда 10 раз все поля из users?
нужно делать два запроса, один в user, второй в profiles, и вот уже потом сливать в один массив

Answer 3

[Анатолий]

Если данных (количество записей в профиле для одного юзера) мало, то это вот Вас спасет, если много, то никак не сделаете.

Answer 4

[CKOPOBAPKuH]

select u.user_id,u.username,u.password,u.user_type
,up1.profile_value as sex
,up2.profile_value as hobbie
,up3.profile_value as ещёчегото
FROM users as u
LEFT JOIN user_profiles as up1 ON up1.user_id=u.user_id AND up1.profile_key=«sex»
LEFT JOIN user_profiles as up2 ON up2.user_id=u.user_id AND up2.profile_key=«sex»
LEFT JOIN user_profiles as up3 ON up3.user_id=u.user_id AND up3.profile_key=«sex»

Comments

[CKOPOBAPKuH]

тьфу ты. конечно же,
LEFT JOIN user_profiles as up1 ON up1.user_id=u.user_id AND up1.profile_key=«sex»
LEFT JOIN user_profiles as up2 ON up2.user_id=u.user_id AND up2.profile_key=«hobbie»
LEFT JOIN user_profiles as up3 ON up3.user_id=u.user_id AND up3.profile_key=«ещёчегото»

[Анатолий]

Я бы так не делал бы, на большой таблице user_profiles mysql будет сложно, вложенные запросы в блок select должны отрабатывать на порядок быстрей:
SELECT users.*,
(SELECT profile_value WHERE where user_profiles.user_id = users.user_id AND user_profile_key = 'sex')

по крайней мере в моем случае такая замена почень сильно помогла

Answer 5

[recky]

ладно, спасибо всем, буду всё-таки отдельные запросы делать. Профиль тогда вообще в отдельный класс запакую.