Меню
Что нового?

вроде несложная выборка из 1 таблицы, но не получается

Leonid

PHP? нет, не слышал...
Есть таблица messages сообщений между пользователями сайта. Нужно для какого-либо пользователя, например с Id= 100
сделать список его собеседников, отсортированный по дате последнего сообщения.

таблица messages:
PHP: 
id          from_user          to_user                    date_time                    content
1          100                    123                    2011-01-24 12:12:30          bla-bla-bla
2          123                    100                    2011-01-24 12:30:30
3          100                    500                    2011-01-24 12:31:20
4          555                    777                    2011-01-24 12:50:33
5          800                    555                    2011-01-24 12:54:12
6          100                    777                    2011-01-24 13:11:09
7          777                    100                    2011-01-24 13:22:19
8          333                    555                    2011-01-24 13:23:20
9          777                    555                    2011-01-24 13:30:45
10         100                    123                    2011-01-24 13:34:56
11         333                    100                    2011-01-24 13:36:01
Вначале нужно сделать выборку всех пользователей,
а) для которых есть сообщение от него
б) от которых есть сообщение для него

в) отсортированных по дате

вроде просто:

select * from messages where from_user = 100 or to_user = 100 order by date_time desc

имеем:
PHP: 
id          from_user          to_user                    date_time                    content
11          333                    100                    2011-01-24 13:36:01
10          100                    123                    2011-01-24 13:34:56
7           777                    100                    2011-01-24 13:22:19
6           100                    777                    2011-01-24 13:11:09
3           100                    500                    2011-01-24 12:31:20
2           123                    100                    2011-01-24 12:30:30
1           100                    123                    2011-01-24 12:12:30          bla-bla-bla

усложняем задачу. Поскольку нам нужен не список сообщений, а список пользователей, с которыми есть переписка, нам нужно получить
только по одной записи, в которой есть сообщение от или к другому пользователю.
Если сделать select distinct from_user или select distinct to_user, то не всегда получим полную выборку собеседников

Пришла мысль сделать так: с помощью логических функций при выборке формируется дополнительное поле sobesednik, равное
либо from_user (если from_user <> 100) либо to_user (Если to_user <> 100) и делать distinct по нему:

Без distinct выборка будет такой

PHP: 
id          from_user          to_user         sobesednik          date_time                    content
11         333                    100          333                    2011-01-24 13:36:01
10         100                    123          123                    2011-01-24 13:34:56
7          777                    100          777                    2011-01-24 13:22:19
6          100                    777          777                    2011-01-24 13:11:09
3          100                    500          500                    2011-01-24 12:31:20
2          123                    100          123                    2011-01-24 12:30:30
1          100                    123          123                    2011-01-24 12:12:30          bla-bla-bla
а с distinct по логике должна быть такой:

PHP: 
id          from_user          to_user          sobesednik          date_time                    content
11          333                    100          333                    2011-01-24 13:36:01
10          100                    123          123                    2011-01-24 13:34:56
7           777                    100          777                    2011-01-24 13:22:19
3           100                    500          500                    2011-01-24 12:31:20
точнее, при использовании select distinct sobesednik выборка содержит только sobesednik:
333
123
777
500

Это все работает. Только последовательность sobesednik-ов почему-то совершенно другая, логику определения этого порядка
я не понял. Может быть например

500
777
123
333

Вот. собственно что я не так делаю? Как по-другому получить список собеседников?

Сейчас на сайте работает довольно извратный способ.
Получаю отдельно списки from_user и to_user, перевожу их в массивы, объединяю в один, удаляю уникальные записи.
Способ работает, но результат тоже никак не привязан к дате последнего сообщения.
 

Leonid

PHP? нет, не слышал...
Действительно, запрос

SELECT distinct from_user FROM `messages` WHERE to_user = 100
union
select distinct to_user FROM `messages` WHERE from_user = 100

дает оъединенный список, но попытка добавить к нему order by date_time вызывает ошибку - неизвестный столбец date_time :(
 

Leonid

PHP? нет, не слышал...
надо что-то вроде

SELECT unique from_user, date_time FROM `messages` WHERE to_user = 100
union
select unique to_user, date_time FROM `messages` WHERE from_user = 100

order by date_time desc

но не работает...
 
Splurov

Splurov

Новичок
Вообще, я имел ввиду:
Код: 
(SELECT from_user AS user_id, MAX(date_time) AS dt FROM messages WHERE to_user = 100 GROUP BY to_user)
UNION
(SELECT to_user AS user_id, MAX(date_time) AS dt FROM messages WHERE from_user = 100 GROUP BY from_user)
ORDER BY dt DESC
Но это не правильно, т.к. если пользователь и писал письмо пользователю и получал письмо от этого же пользователя, будут дубли.
Так что мой совет скорее вредный, извиняюсь.
 

Leonid

PHP? нет, не слышал...
попробовал вредный совет, что-то он вообще мне выдал только 2 строчки , хотя в моем реальном примере должно быть более 100 собеседников.
а мой запрос с Union выдал результаты без дублей, хотя действительно, они должны быть.. мистика.
 

Leonid

PHP? нет, не слышал...
craz
может дело в версии MySQL... Проверял у себя на локальном компе и на хостинге, везде ошибки выдает. Даже просто
select unique to_user from messages where from_user = 1000

выдает
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'unique to_user from messages where from_user = 100' at line 1
 
Splurov

Splurov

Новичок
Leonid написал(а):
попробовал вредный совет, что-то он вообще мне выдал только 2 строчки , хотя в моем реальном примере должно быть более 100 собеседников.
Нажмите для раскрытия...
Описка, исправил:
Код: 
(SELECT from_user AS user_id, MAX(date_time) AS dt FROM messages WHERE to_user = 100 GROUP BY from_user)
UNION
(SELECT to_user AS user_id, MAX(date_time) AS dt FROM messages WHERE from_user = 100 GROUP BY to_user)
ORDER BY dt DESC
Leonid написал(а):
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'unique to_user from messages where from_user = 100' at line 1
Нажмите для раскрытия...
unique -> distinct
 
craz

craz

Нестандартное звание
у меня и правда дублирующие записи... я не подумал про ограничение..
надо думать про темповую таблицу мне кажется скинуть в нее данные
Splurov
по вот этому вредному совету, а потом дистинкт, не?
 
Splurov

Splurov

Новичок
craz
Не поможет, там же ещё дата. А дату нужно максимальную будет выбрать.
 
craz

craz

Нестандартное звание
ну и нафик короче тогда не надо это делать в майсиквеле
 
Splurov

Splurov

Новичок
Можно попробовать так извратиться )
Код: 
SELECT user_id, MAX(date_time) AS dt FROM (
	(SELECT from_user AS user_id, date_time FROM messages WHERE to_user = 100)
	UNION
	(SELECT to_user AS user_id, date_time FROM messages WHERE from_user = 100)
) AS u
GROUP BY user_id
ORDER BY dt DESC
 

Leonid

PHP? нет, не слышал...
Splurov
да, твой запрос сработал, но все с дублями. Ну по крайней мере дублей точно не больше 2. :) ладно, буду думать
 

Leonid

PHP? нет, не слышал...
Splurov
а второй работает!!! и без дублей. Правда не понимаю как :) Спасибо!
 
Splurov

Splurov

Новичок
Leonid написал(а):
Правда не понимаю как
Нажмите для раскрытия...
(Нужно понимать, что это не оптимальный запрос, и на большом количестве данных будет медленным.)
А работает так:
Код: 
SELECT from_user AS user_id, date_time FROM messages WHERE to_user = 100
выбираем пользователей и дату сообщения, которые писали сотому,
Код: 
SELECT to_user AS user_id, date_time FROM messages WHERE from_user = 100
выбираем пользователей и дату сообщения, которым писал сотый,
объединяем,
вставляем этот запрос во FROM; mysql с ним будет обращаться как с таблицей (или, другими словами, с набором строк),
группируем по дате и выбираем максимальную дату (нам же нужно время самого последнего контакта пользователя),
и под конец сортируем, чтобы вверху показывались самые свежие контакты.
 

Leonid

PHP? нет, не слышал...
ясно спасибо.
Сейчас проверял на скорость.
в таблице messages порядка 50- тыс. записей

у пользователя, у которого 139 контактов, около 1000 сообщений
или 2 контакта, 5 сообщений

в обоих случаях время около 0,15 сек. вроде быстро
 
prolis

prolis

Новичок
последние собеседники:
Код: 
select if(to_user=100, from_user, to_user) as user, max(date_time) as dt
from messages
where 100 in (from_user, to_user)
group by if(to_user=100, from_user, to_user)
order by dt desc
limit 10
 
Войдите или зарегистрируйтесь для ответа.
Сверху