Нужно извлечь из бд все чаты на которые подписан пользователь, имеются след. таблицы:
Подписки: chats_subscriptions
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| uid | int(11) | YES | MUL | NULL | |
| cid | int(11) | YES | MUL | NULL | |
+-------+---------+------+-----+---------+-------+
Чаты: chats
+--------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| owner | int(11) | NO | | NULL | |
| label | varchar(100) | YES | | NULL | |
| type | varchar(10) | NO | | private | |
| status | int(1) | NO | | 1 | |
| date | timestamp | YES | | CURRENT_TIMESTAMP | |
+--------+--------------+------+-----+-------------------+----------------+
Сообщения: messages
+-----------+-----------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| cid | int(11) | NO | | NULL | |
| sender | int(11) | NO | | NULL | |
| recipient | int(11) | NO | | NULL | |
| date | timestamp | NO | | CURRENT_TIMESTAMP | |
| content | text | NO | | NULL | |
| del | int(11) | YES | | NULL | |
| status | int(11) | YES | | NULL | |
+-----------+-----------+------+-----+-------------------+----------------+
Следующим запросом я пытаюсь достать все чаты на которые подписан пользователь, с кол-вом не прочитанных сообщений:
SELECT `cs`.`cid`, `ch`.`owner` AS `cOwner`, `ch`.`label` AS `cLabel`,
`ch`.`type` AS `cType`, `ch`.`status` AS `cStatus`, `ch`.`date` AS `cDate`,
COUNT(`msg`.`id`) AS `countMsg` FROM `chats_subscriptions` `cs`
LEFT JOIN `chats` `ch` ON `ch`.`id`=`cs`.`cid`
LEFT JOIN `messages` `msg` ON `msg`.`cid`=`ch`.`id` AND `msg`.`del` IS NULL AND `msg`.`status` IS NULL AND `msg`.`recipient`=`cs`.`uid`
WHERE `cs`.`uid` = 2
Такой запрос работает не совсем правильно, он извлекает информацию верно, но если юзер подписан на три чата, то все-равно вернется только одна строка. Вот если убрать LEFT JOIN messages - тогда все работает верно, но нужно же извлекать кол-во сообщений (ещё и хочется добавить текст последнего непрочитанного сообщения). Подскажите, в чем косяк?
И кстати, если по вашему мнению это кривая структура бд, с удовольствием выслушаю ваши более оптимальные предложения! (задача была такой: сделать переписку в лс на сайте с возможность добавлять/удалять собеседников, поэтому я и решил представить все в виде чатов с подписками на них)
Простой