Форум MySQL

Всем привет... вот застрял по уши...
есть 3 таблицы: `online`,`contacts`,`massege`

CREATE TABLE `online` (  `ID`      INT(0) NOT NULL  default '0',  `nick`    char(20) NOT NULL default '',  `exit`    tinyint(0) NOT NULL DEFAULT '0', ....  UNIQUE KEY `ID` (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

`ID` - id юзера
`nick` - ник
`exit` - состояние юзера (онлайн/вышел)

CREATE TABLE `massege` (  `ID` INT(10) unsigned NOT NULL   auto_increment,  `from` INT(10) NOT NULL default '0',  `to` INT(10) NOT NULL default '0',  `read` tinyint(1) unsigned NOT NULL default '0',   ..... PRIMARY KEY (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

`ID` - id сообщения
`from` - id юзера от кого пришло сообщение
`to` - id юзера к кому отправлено сообщение
`read` - состояние письма (прочитано/непрочитано)

CREATE TABLE `contacts` (  `ID` INT(10) unsigned NOT NULL,  `contact` INT(10) unsigned NOT NULL,  ..... INDEX `ID` (`ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

ID - id юзера, к которму принадлежит контакт
contact - id юзера, которого добавили в контакт


Теперь мне надо сделать выборку...

1ыми показать тех, кто в контакт, онлайне и от кого есть письма
2ыми кто в контакте, ОФФЛАйНЕ и есть письма
3ми те кто НЕ в контакте, но есть письма


уже 2ой день над этим запросом сижу... помогите!!!!

ой... т.е.так надо сделать выборку:
1ыми показать тех, кто в контакт, онлайне и от кого есть письма
2ыми кто в контакте, ОФФЛАйНЕ и есть письма
3ми показать тех, кто в контакте, онлайне, но нет писем
4ми те кто НЕ в контакте, онлайн, но есть письма
5ми те кто НЕ в контакте, офлайне, и тоже есть письма

Все к чему я пришел это:
т.е. работал лэфт джойнами... если приходит ID. значит они есть в той или иной таблице

SELECT    massege.from,   online.ID,   contacts.contact,   count(massege.ID) as `all_msg`  FROM    `massege`     LEFT JOIN       `online`     ON       online.ID=massege.from      AND      online.exit="0"     LEFT JOIN       `contacts`     ON       contacts.id='.ID.'      AND      contacts.contact = massege.from  WHERE   massege.to='.ID.'   AND   massege.read="0"  GROUP BY   massege.from  ORDER BY    online.ID DESC  LIMIT ....

но этот запрос не выполняет всего,что нужно....
нет, тех кто в онлайне, в контакте, но от которых нет писем

Надо понимать, что основная таблица пользователей (то есть та, по которой, например, можно определить как кого зовут) называется online?

Я бы не стал пытаться укладывать получение и списка контактов и списка пришедших сообщений в один SQL-запрос - сложность при таком подходе вырастает изрядно, а что при этом экономим - неясно.
Разбил бы на два. И уже на уровне php совместил бы результаты в один список.

PS. Выдумать название massege - редкостное издевательство надо теми, кто будет вынужден с этой таблицей работать.

>Надо понимать, что основная таблица пользователей (то есть та, по которой, например, можно определить как кого зовут) называется online?
я просто не все поля показал. и имена как раз идут вместе с сообщениями. Такая структура проекта. Нельзя привязывать к ID юзера тот или иной ник(имя).

>Я бы не стал пытаться укладывать получение и списка контактов и списка пришедших сообщений в один SQL-запрос - сложность при таком подходе вырастает изрядно, а что при этом экономим - неясно.
>Разбил бы на два. И уже на уровне php совместил бы результаты в один список.
Думаю так и поступить.... Спасибо...
Но вот как лучше разбить эти запросы?



>PS. Выдумать название massege - редкостное издевательство надо теми, кто будет вынужден с этой таблицей работать.
мдааа) учтем. просто я менял названия перед тем как оставить тут пост.
и видать на сонную голову....))

Это черновик. Исходных данных Вы не привели - проверять код не на чем

<?php function list_sorter($a, $b) {     if($a['order'] != $b['order'])        return $b['order'] - $a['order'];     return $a['uid'] - $b['uid']; } $sql = "   SELECT uid, msgcnt, (exit = 0) AS onl     FROM     ( SELECT `from` AS uid, COUNT(`from`) AS msgcnt        FROM message        WHERE `to` = $id AND `read` = 0        GROUP BY uid        HAVING msgcnt > 0     ) AS usrlist       LEFT JOIN online ON usrlist.uid = online.id "; $res = mysql_query($sql) or die("Error in $sql : ". mysql_error() ); $list = array(); while($row = mysql_fetch_assoc($res)) {     $row['order'] = 2 | $row['onl'];     $list[$row['uid']] = $row; } $sql = "SELECT `contact` AS uid, (exit = 0) AS onl        FROM contacts        WHERE id  = $id     ) AS usrlist       LEFT JOIN online ON usrlist.uid = online.id "; $res = mysql_query($sql) or die("Error in $sql : ". mysql_error() ); while($row = mysql_fetch_assoc($res)) {     $uid = $row['uid'];     if(!isset($list[$uid]))         $list[$uid] = array('uid'=>$uid, 'msgcnt'=>0, 'onl'=>0, 'order'=>0);     $list[$uid]['listed'] = 1;     $list[$uid]['onl'] |= $row['onl'];     $list[$uid]['order'] |= (4 | $row['onl']); } usort($list, "list_sorter"); ?>

Я не стал трогать ники, потому что Вы сказали, что к соответствующим id они не привязаны.

(exit = 0) это что?

Логическое выражение, равное нулю, если поле exit отлично от нуля, и равное единице в противном случае.

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

тоже самое что и IF(exit = 0, 1, 0)

уау) это что-то)

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

<? $sql = "SELECT `contact` AS uid, (exit = 0) AS onl         FROM contacts         WHERE id  = $id      ) AS usrlist        LEFT JOIN online ON usrlist.uid = online.id "; ?>

Большое спасибо за помощь!)

До сих пор сижу над этим запросом... =(


я вот что подумал. Может главной таблицой сделать "контакты"
и когда кто-то оставляет сообщение, добавлять его в контакты,но ставить метку "временно".
и после прочтения сообщение удалять его... хммм...

перепишите так:

$sql = "SELECT `contact` AS uid, (exit = 0) AS onl        FROM contacts        LEFT JOIN online ON contact = online.id       WHERE contacts.id  = $id  ";

Зачем сидеть-то?

Первый запрос вытягивает пользователей, от которых есть непрочтенные письма.
Второй - вытягивает контакт-лист.
И первый и второй запросы попутно выясняют, являются ли вытянутые пользователи online или нет.
При обработке обох запросов формируется численный показатель order .
Формируется он из трех весов.
"Пользователь присутствует в контакт-листе" имеет вес 4 .
"От пользователя есть нечитанная почта" имеет вес 2
"Пользователь является online" имеет вес 1
Такой набор весов отвечает порядку сортировки списка, который Вы обозначили в посте 31.05.2008 в 09:17
Численный показатель используется для сортировки на уровне php с помощью usort() и сравнивающей callback-функции list_sorter()
.

Это что-то невероятное. Млин… так далеко до Вас. Практически сутки убил, чтоб все понять, протестировать. Все работает на У р а =))) … Спасибо огромное.
Вот только решил немного переделать.
А именно,
Все таки решил добавлять в контакты пользователей, от которых пришло сообщение, но ставить метку «временно» и удалять их после прочтения письма на выходе из сессии…
Будет что-то вроде ICQ. =)
Т.е. будет постоянная таблица контактов и временная.
После входа все контакты буду переходить во временную.
а после выхода удаляться вместе со временными контактами...
так можно будет использовать таблицу типа MEMORY(на VDS)

Я извиняюсь за такую наглость и не прошу это все делать, но хотя бы подскажите в каком направление для этого двигаться…
И еще как тут лучше с LIMITом работать?? если мне надо только по 10 выводить....