Как сделать правильную выборку?

Question

[Владимир]

Добрый день.

Подскажите пожалуйста как сделать правильную выборку для вывода всех диалогов пользователя.

Существует три таблицы:

• conversation: таблица всех диалогов
  
• conversation_mem: информация о том, в каком диалоге(c_id) состоит пользователь(user)
  
• user_profile: таблица пользователей
  

Раньше в одном диалоге состояли ровно 2 пользователя, поэтому таблицы conversation_mem не было вовсе, а таблица conversation: имела поля: user1 и user2. Выборка делалась так:

SELECT U.id, U.name, U.lastname, U.lastact, C.c_id
FROM user_profile U, conversation C
WHERE
CASE
   WHEN   C.user1 = '$my_id'
   THEN    C.user2 = U.id
   WHEN   C.user2 = '$my_id'
   THEN    C.user1 = U.id
END
AND (   C.user1 = '$my_id' OR C.user2 = '$my_id'   										)
ORDER BY C.time DESC

Теперь необходимо все по той же переменной $my_id:

1. узнать все с_id, где user равен $my_id
   
2. узнать все user, где с_id равен найденным в шаге 1
   
3. и достать необходимые поля из user_profile, где id равен найденным id в поле user в шаге 2
   

Хочу сделать как и раньше все в одном запросе, понимаю что сам запрос нужно усложнить, но не знаю как. Буду благодарен за оказанную помощь.

Answer 1

[olamedia .]

SELECT * FROM conversation 
INNER JOIN conversation_mem ON (conversation_mem.c_id = conversation.id) 
WHERE conversation_mem.user = $my_id 
ORDER BY conversation_mem.time DESC

ну и если нужны пользователи сразу

SELECT user_profile.* FROM conversation 
INNER JOIN conversation_mem m1 ON (m1.c_id = conversation.id) 
INNER JOIN conversation_mem m2 ON (m2.c_id = conversation.id) 
INNER JOIN user_profile ON (m2.user = user_profile.id) 
WHERE m1.user = $my_id 
ORDER BY conversation_mem.time DESC

* можно убрать conversation, если данные оттуда не нужны

SELECT user_profile.* FROM conversation_mem m1 
INNER JOIN conversation_mem m2 ON (m1.c_id = m2.c_id) 
INNER JOIN user_profile ON (m2.user = user_profile.id) 
WHERE m1.user = $my_id 
ORDER BY conversation_mem.time DESC

и если убрать себя из собеседников
WHERE ... AND m2.user <> $my_id

Comments

[Владимир]

Выводит всех пользователей, id которых нет в таблице "conversation".

[olamedia .]

vovaricofun: вообще оно выводит не пользователей, а conversation, конечно таких id нет

[olamedia .]

"как сделать правильную выборку для вывода _всех диалогов пользователя_."

[Владимир]

Вы правы, но список диалогов выглядит из имен собеседников. Помогите достать еще и это из бд. Посмотрите код который я использовал ранее. Он выводил диалоги с данными о собеседнике из поля user1 или user2 таблицы conversation.

[olamedia .]

обновил, но выглядит жутковато ) можно сделать несколько последовательных SELECT..WHERE id IN (,,,)

[Владимир]

А как сделать несколько последовательных? В нескольких запросах, или можно в одном?

[olamedia .]

в нескольких - вначале собирается массив c_id комнат в которых находится пользователь.
затем в следующий запрос передается этот массив, чтобы найти список id пользователей в этих комнат, где он не равен текущему пользователю, а этот массив можно передать в третий запрос к user_profile чтобы все нужные профили достать одним запросом.

[Владимир]

Именно так сейчас все работает, но блок с диалогами интерактивный, а так же обновляется, когда вам приходит новое сообщение в любом из диалогов. Поэтому у пользователей с количеством диалогов, например 10, каждые 3-5 секунд обновляется блок, в котором 1+10*2=21 запросов в бд. Плюс выводится счетчик новых сообщений для каждого, так что 21+10=31. И это если 1 пользователь онлайн..

[olamedia .]

Владимир: делается WHERE id IN (массив через запятую) один запрос всего на таблицу.

[Владимир]

Спасибо, буду пробовать.

Answer 2

[Дмитрий С]

SELECT
	cvr.`c_id`      AS "c_id",
	usr.`id`        AS "id",
	usr.`name`      AS "name",
	usr.`lastname`  AS "lastname",
	usr.`lastact`   AS "lastact"

FROM 
	`conversation_mem` cvr_m
	LEFT JOIN `conversation` cvr ON cvr.`c_id` = cvr_m.`c_id`
	LEFT JOIN `user_profile` usr ON usr.`id` = cvr_m.`user`
	
WHERE
	cvr_m.`user` = '$my_id'
	
GROUP BY
	cvr.`c_id`

Comments

[Владимир]

Выводит только пользователя, исправил (WHERE cvr_m.`user` = '$my_id') "=" на "<>". Теперь выводит правильно, но буду тестировать. Позже отпишусь.

[Дмитрий С]

Владимир: " как сделать правильную выборку для вывода всех диалогов пользователя", "Выводит только пользователя". Я вас не понял сейчас) Нет, конечно, возможности проверить запрос, но выводить должен идентификаторы всех диалогов в которых участвует пользователь. Ну и в догонку данные пользователя.

[Владимир]

Все отлично работает, просто выводились мои данные, а нужны все кроме моих. Спасибо большое.

[Дмитрий С]

Владимир: ну как скажете, рад был помочь:)