Есть таблица сообщений, таблица пользователей и таблица связки пользователей (в таблица хранится, кто у кого в друзьях). Таблица пользователей: Код (Text): +----+------------+-----------+ | id | first_name | last_name | +----+------------+-----------+ | 1 | fName1 | lName1 | +----+------------+-----------+ | 2 | fName2 | lName2 | +----+------------+-----------+ | 3 | fName3 | lName3 | +----+------------+-----------+ | 4 | fName4 | lName4 | +----+------------+-----------+ | 5 | fName5 | lName5 | +----+------------+-----------+ Таблица связки пользователей: Код (Text): +----+-------------+-------------+ | id | contact_id1 | contact_id1 | +----+-------------+-------------+ | 1 | 1 | 2 | +----+-------------+-------------+ | 2 | 1 | 4 | +----+-------------+-------------+ | 3 | 3 | 1 | +----+-------------+-------------+ | 4 | 5 | 1 | +----+-------------+-------------+ По бд получается, что у первого пользователя друзья с id=2, 3, 4, 5. Таблица сообщений: Код (Text): +-----+-------------+--------------+-------+--------+--------+ | id | id_sender | id_recipient | text | active | time | +-----+-------------+--------------+-------+--------+--------+ | 1 | 1 | 2 | mes1 | 1 | time1 | +-----+-------------+--------------+-------+--------+--------+ | 2 | 2 | 1 | mes2 | 1 | time2 | +-----+-------------+--------------+-------+--------+--------+ | 3 | 1 | 2 | mes3 | 1 | time3 | +-----+-------------+--------------+-------+--------+--------+ | 4 | 2 | 1 | mes4 | 1 | time4 | +-----+-------------+--------------+-------+--------+--------+ | 5 | 3 | 1 | mes5 | 0 | time5 | +-----+-------------+--------------+-------+--------+--------+ | 6 | 1 | 5 | mes6 | 1 | time6 | +-----+-------------+--------------+-------+--------+--------+ | 7 | 5 | 1 | mes7 | 0 | time7 | +-----+-------------+--------------+-------+--------+--------+ | 8 | 3 | 1 | mes8 | 0 | time8 | +-----+-------------+--------------+-------+--------+--------+ | 9 | 1 | 2 | mes9 | 1 | time9 | +-----+-------------+--------------+-------+--------+--------+ | 10 | 2 | 1 | mes10 | 1 | time10 | +-----+-------------+--------------+-------+--------+--------+ id_sender - id отправителя, id_recipient - id получателя, active - прочитано ли сообщение (0 - не прочитано, 1 - прочитано). Надо вывести id пользователей в порядке последних сообщений и вывести это последнее сообщение, если считать, что id пользователя равно 1. Пример: Код (Text): +------------+-----------+-------+--------+--------+ | first_name | last_name | text | active | time | +------------+-----------+-------+--------+--------+ | fName2 | lName2 | mes10 | 1 | time10 | +------------+-----------+-------+--------+--------+ | fName3 | lName3 | mes8 | 0 | time9 | +------------+-----------+-------+--------+--------+ | fName5 | lName5 | mes7 | 0 | time7 | +------------+-----------+-------+--------+--------+ | fName4 | lName4 | | | | +------------+-----------+-------+--------+--------+ Господа, помогите кто-нибудь с данной проблемой.
SELECT `user`.`first_name`, `user`.`last_name`, `message`.`text`, `message`.`active`, `message`.`time` FROM `user`, `message` WHERE `user`.`id` = `message`.`id_sender` AND `message`.`id_sender` = 1 ORDER BY `time` DESC LIMIT 1
Извини, немного не понял вопроса. SELECT `user`.`first_name`, `user`.`last_name`, `message`.`text`, `message`.`active`, MAX(`message`.`time`) FROM `user`, `message` WHERE `user`.`id` = `message`.`id_sender` GROUP BY `user`.`first_name`, `user`.`last_name`, `message`.`text`, `message`.`active`
SELECT `user`.`first_name`, `user`.`last_name`, `message`.`text`, `message`.`active`, MAX(`message`.`time`) FROM `user`, `message` WHERE `user`.`id` = `message`.`id_recipient` AND `message`.`id_sender` = 1 GROUP BY `user`.`first_name`, `user`.`last_name`, `message`.`text`, `message`.`active` Наверное так нужно? Добавлено спустя 4 минуты 20 секунд: ОК, можно и так: SELECT `message`.`id_recipient`,`user`.`first_name`, `user`.`last_name`, `message`.`text`, `message`.`active`, MAX(`message`.`time`) и GROUP BY `message`.`id_recipient`,`user`.`first_name`, `user`.`last_name`, `message`.`text`, `message`.`active`
Здесь понято: выбираем ИД получателей, ФИО получателей, сообщение, статус сообщения и максимальное по времени сообщение. Таблица "message" нужна для выборки сообщений, а "user" для выборки ФИО получателей. Из таблицы "user" берем ФИО с ИД равным ИД получателей из таблицы "message". Согласно условию задачи выбираем только сообщения с ИД отправителя равным 1. Как меня учили, в условия группировки нужно включать все поля до оператора "MAX", но должно работать и так: GROUP BY `message`.`id_recipient`, `message`.`text`, `message`.`active`
в этом месте у вас итак остается только по одной строке для каждого возможного получателя. и каждая эта строка - уникальна (по первичному же ключу). так что уже вообще не повлияют на выдачу. мускул только потратит время на лишнюю группировку. и поскольку вы решили группировать по идентификатору получателя уже тоже не повлияют на результат не повлияют - это я по логике сужу, и не заявляю о себе как о гуру скуэля. а если на самом деле влияют? тогда если два и более одинаковых сообщений с разными получателями - покажет только наипоследнейшее. и потом покажет вообще максимум два сообщения - по одному для нулевого и ненулевого статуса. но это уже проверять надо на реальных данных)))
Кстати, таблица связки пользователей создаст кучу проблем. Попробуйте сделать селект, чтобы выбрать всех друзей юзера с ИД=1? Да и поле "id" здесь, по моему, лишнее. Структура должна быть, примерно, следующая: Таблица: user_friends Поля: user - здесь ИД пользователя friends - здесь ИД друга пользователя user friend 1 2 1 3 1 4 1 5 2 1 3 1 4 1 5 1 тогда все друзья пользователя с ИД=1 будут выбираться простым селектом: SELECT friend FROM user_friends WHERE user = 1 Добавлено спустя 11 минут 8 секунд: 100% согласен. Если вставить `message`.`active`, то получим 2-е строки с максимальным "time" для "active" = 1 и вторую с максимальным "time" для "active" = 0 А если еще и вставить "text", то вообще бред получим. Действительно нужно все проверять на практике. Добавлено спустя 48 секунд: Будет правильно.
146% правы))) только, будьте так любезны, упомяните про уникальный индекс на строку, а то мне лениво)))
По моему мнению, поле "id" - уникальный индекс строки, должно присутствовать практически во всех таблицах, даже, если там уже присутствует поле, значение которого уникально для всей таблицы, напр. код ОКПО предприятия в таблице предприятий. Но в таблице, которая создается для связи двух таблиц типа "многие ко многим" уникальный идентификатор строки, как правило, лишний. Таблица связки пользователей, как раз и является такой таблицей т.к. таблица пользователей имеет связь сама к себе типа "многие ко многим": у одного пользователя может быть несколько друзей, а так же и он может быть другом нескольких пользователей.
понятно))) не в теме))) про n-n никто не спорит но контролировать записи нужно же как-то. вот и добавляем уникальный индекс по обоим полям. то есть именно оба значения. если его не добавить - пользователь 1 может n раз дружить с пользователем 2. а если уник будет - на попытку вставки второй пары 1-2 скуэль ругнется на нарушение уникального индекса и да. уникальный индекс не обязательно является первичным ключом таблицы. итак. нам нужно только 2 поля - юзер и друг. и первичный ключ нам нафиг не нужен - согласен. но уникальный ключ по значению полного кортежа - нужен.
Это уже добавление ограничений. С этим никто не спорит. Проблема в том, я только что проверял, что мой селект все-таки работает неправильно. В поле максимального значения "time" действительно подставляется максимальное значение, а вот все остальные значения попадают из первой же попавшейся строки. И не обязательно, чтобы в этой строке значение "time" было максимальным. Первое, что приходит на ум - это след. селект: SELECT `user`.`first_name`, `user`.`last_name`, `message`.`text`, `message`.`active`, MAX(`message`.`time`) FROM (SELECT * FROM `message` ORDER BY `time` DESC) AS message, `user` WHERE `user`.`id` = `message`.`id_recipient` AND `message`.`id_sender` = 1 GROUP BY `message`.`id_recipient` Добавлено спустя 24 минуты 33 секунды: Что значит алиасить? `user` AS user и `message` AS message?
рекурсивный синоним к названию таблицы. бессмысленный и беспощадный)))) `user` AS `u`, `message` AS `m`
вы присваеваете id_sender = 1 и извлекаете id_recipient. Номожет быть подругому, то есть я шлю сообщение, тогда надо присвоить id_recipient = 1 и извлечь id_sender надо упорядочить по любому сообщению, не только по тому, которое я отправил.