MYSQL Query – получить последние комментарии, связанные с сообщением

Это сообщение об ошибке

Недопустимое сочетание сортировок (utf8_general_ci, IMPLICIT) и (utf8_unicode_ci, IMPLICIT) для операции '='

обычно определяется определением ваших столбцов и таблиц. Обычно это означает, что по обе стороны от знака равенства есть разные сопоставления. Что вам нужно сделать, это выбрать одно и включить это решение в свой запрос.

Проблема сопоставления здесь была в CROSS JOIN @prev_value, которая нуждалась в явной сортировке, которая будет использоваться.

Я также немного изменил логику «row_number» на одно перекрестное соединение и переместил логику if в крайности списка выбора.

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

1. так что вы поймете любой результат, который я представляю
2. так что в будущем, когда вы зададите другой вопрос, связанный с SQL, вы понимаете важность предоставления данных. Нам не только удобно, что вы это делаете. Если искатель предоставит образцы данных, то апеллятор уже поймет это – это не будет изобретение какого-то незнакомца, который посвятил часть своего времени, чтобы помочь.

Пример данных

Обратите внимание, что в таблицах отсутствуют некоторые столбцы, только столбцы, указанные в таблице, были включены.

В этих образцовых данных есть 5 комментариев к одному сообщению (нет записи не нравится)

 CREATE TABLE Posts ( `id` int, `uuid` varchar(7) collate utf8_unicode_ci, `imageLink` varchar(9) collate utf8_unicode_ci, `date` datetime ); INSERT INTO Posts(`id`, `uuid`, `imageLink`, `date`) VALUES (145, 'abcdefg', 'blah blah', '2016-10-10 00:00:00') ; CREATE TABLE USERS ( `id` int, `username` varchar(15) collate utf8_unicode_ci, `profileImage` varchar(12) collate utf8_unicode_ci, `date` datetime ) ; INSERT INTO USERS(`id`, `username`, `profileImage`, `date`) VALUES (145, 'used_by_already', 'blah de blah', '2014-01-03 00:00:00') ; CREATE TABLE Activity ( `id` int, `uuid` varchar(4) collate utf8_unicode_ci, `uuidPost` varchar(7) collate utf8_unicode_ci, `type` varchar(40) collate utf8_unicode_ci, `commentText` varchar(11) collate utf8_unicode_ci, `date` datetime ) ; INSERT INTO Activity (`id`, `uuid`, `uuidPost`, `type`, `commentText`, `date`) VALUES (345, 'a100', 'abcdefg', 'comment', 'lah lha ha', '2016-07-05 00:00:00'), (456, 'a101', 'abcdefg', 'comment', 'lah lah lah', '2016-07-06 00:00:00'), (567, 'a102', 'abcdefg', 'comment', 'lha lha ha', '2016-07-07 00:00:00'), (678, 'a103', 'abcdefg', 'comment', 'ha lah lah', '2016-07-08 00:00:00'), (789, 'a104', 'abcdefg', 'comment', 'hla lah lah', '2016-07-09 00:00:00') ; 

[Стандартное поведение SQL: 2 строки в каждом запросе]

Это был мой первоначальный запрос с некоторыми исправлениями. Я изменил порядок столбцов в списке выбора, чтобы вы могли легко увидеть некоторые данные, связанные с комментариями, когда я представляю результаты. Изучите те результаты, которые они предоставили, чтобы вы могли понять, что будет делать запрос. Столбцы, которым предшествуют #, не существуют в образцах данных, с которыми я работаю, по причинам, которые я уже отмечал.

 SELECT Posts.id , Posts.uuid , rcom.uuidPost , rcom.commentText , rcom.`date` commentDate #, Posts.caption #, Posts.path , Posts.`date` , USERS.id , USERS.username #, USERS.fullname , USERS.profileImage , COALESCE(A.LikeCNT, 0) num_likes FROM Posts INNER JOIN USERS ON Posts.id = 145 AND USERS.id = 145 LEFT JOIN ( SELECT COUNT(A.uuidPost) LikeCNT , A.UUIDPost FROM Activity A WHERE type = 'like' GROUP BY A.UUIDPOST ) A ON A.UUIDPost = Posts.uuid LEFT JOIN ( SELECT @row_num := IF(@prev_value=UUIDPOST,@row_num+1,1) as row_number , commentText , uuidPost , `date` , @prev_value := UUIDPOST FROM Activity CROSS JOIN ( SELECT @row_num := 1, @prev_value := '' collate utf8_unicode_ci ) xy WHERE type = 'comment' ORDER BY uuidPost , `date` DESC ) rcom ON rcom.uuidPost = Posts.UUID AND rcom.row_number <= 2 ORDER BY posts.`date` DESC ; 

См. Рабочую демонстрацию этого запроса в SQLFiddle

Результаты :

 | id | uuid | uuidPost | commentText | date | date | id | username | profileImage | num_likes | |-----|---------|----------|-------------|------------------------|---------------------------|-----|-----------------|--------------|-----------| | 145 | abcdefg | abcdefg | hla lah lah | July, 09 2016 00:00:00 | October, 10 2016 00:00:00 | 145 | used_by_already | blah de blah | 0 | | 145 | abcdefg | abcdefg | ha lah lah | July, 08 2016 00:00:00 | October, 10 2016 00:00:00 | 145 | used_by_already | blah de blah | 0 | 

Есть 2 ROWS – как и ожидалось. Одна строка для последнего комментария и другие строки для следующего последнего комментария. Это нормальное поведение для SQL и до тех пор, пока в этом ответе не будет добавлен комментарий. Читатели вопроса предполагают, что это нормальное поведение было бы приемлемым.

В вопросе отсутствует четко выраженный «ожидаемый результат».

---

[Вариант 1: одна строка для каждого запроса, с комментариями UP TO 2, добавленные столбцы]

В приведенном ниже комментарии было показано, что вам не нужно было 2 строки за сообщение, и это было бы легко исправить. Ну, это легко, но есть варианты, и параметры продиктованы пользователем в виде требований. Если бы у вопроса был «ожидаемый результат», тогда мы знали бы, какой вариант выбрать. Тем не менее здесь есть один вариант

 SELECT Posts.id , Posts.uuid , max(case when rcom.row_number = 1 then rcom.commentText end) Comment_one , max(case when rcom.row_number = 2 then rcom.commentText end) Comment_two #, Posts.caption #, Posts.path , Posts.`date` , USERS.id , USERS.username #, USERS.fullname , USERS.profileImage , COALESCE(A.LikeCNT, 0) num_likes FROM Posts INNER JOIN USERS ON Posts.id = 145 AND USERS.id = 145 LEFT JOIN ( SELECT COUNT(A.uuidPost) LikeCNT , A.UUIDPost FROM Activity A WHERE type = 'like' GROUP BY A.UUIDPOST ) A ON A.UUIDPost = Posts.uuid LEFT JOIN ( SELECT @row_num := IF(@prev_value=UUIDPOST,@row_num+1,1) as row_number , commentText , uuidPost , `date` , @prev_value := UUIDPOST FROM Activity CROSS JOIN ( SELECT @row_num := 1, @prev_value := '' collate utf8_unicode_ci ) xy WHERE type = 'comment' ORDER BY uuidPost , `date` DESC ) rcom ON rcom.uuidPost = Posts.UUID AND rcom.row_number <= 2 GROUP BY Posts.id , Posts.uuid #, Posts.caption #, Posts.path , Posts.`date` , USERS.id , USERS.username #, USERS.fullname , USERS.profileImage , COALESCE(A.LikeCNT, 0) ORDER BY posts.`date` DESC ; 

См. Второй запрос, работающий в SQLFiddle

Результаты запроса 2 :

 | id | uuid | Comment_one | Comment_two | date | id | username | profileImage | num_likes | |-----|---------|-------------|-------------|---------------------------|-----|-----------------|--------------|-----------| | 145 | abcdefg | hla lah lah | ha lah lah | October, 10 2016 00:00:00 | 145 | used_by_already | blah de blah | 0 | 

---

** Вариант 2, объединяет самые последние комментарии в один список, разделенный запятыми **

 SELECT Posts.id , Posts.uuid , group_concat(rcom.commentText) Comments_two_concatenated #, Posts.caption #, Posts.path , Posts.`date` , USERS.id , USERS.username #, USERS.fullname , USERS.profileImage , COALESCE(A.LikeCNT, 0) num_likes FROM Posts INNER JOIN USERS ON Posts.id = 145 AND USERS.id = 145 LEFT JOIN ( SELECT COUNT(A.uuidPost) LikeCNT , A.UUIDPost FROM Activity A WHERE type = 'like' GROUP BY A.UUIDPOST ) A ON A.UUIDPost = Posts.uuid LEFT JOIN ( SELECT @row_num := IF(@prev_value=UUIDPOST,@row_num+1,1) as row_number , commentText , uuidPost , `date` , @prev_value := UUIDPOST FROM Activity CROSS JOIN ( SELECT @row_num := 1, @prev_value := '' collate utf8_unicode_ci ) xy WHERE type = 'comment' ORDER BY uuidPost , `date` DESC ) rcom ON rcom.uuidPost = Posts.UUID AND rcom.row_number <= 2 GROUP BY Posts.id , Posts.uuid #, Posts.caption #, Posts.path , Posts.`date` , USERS.id , USERS.username #, USERS.fullname , USERS.profileImage , COALESCE(A.LikeCNT, 0) ORDER BY posts.`date` DESC 

См. Этот третий запрос, работающий на SQLFiddle

Результаты запроса 3 :

 | id | uuid | Comments_two_concatenated | date | id | username | profileImage | num_likes | |-----|---------|---------------------------|---------------------------|-----|-----------------|--------------|-----------| | 145 | abcdefg | hla lah lah,ha lah lah | October, 10 2016 00:00:00 | 145 | used_by_already | blah de blah | 0 | 

---

** Резюме **

Я представил 3 запроса, каждый из которых показывает только два последних комментария, но каждый запрос делает это по-другому. Первый запрос (поведение по умолчанию) отображает 2 строки для каждого сообщения. Вариант 2 добавляет столбец, но удаляет вторую строку. Вариант 3 объединяет два последних комментария.

Обратите внимание, что:

• В вопросе отсутствуют определения таблиц, охватывающие все столбцы
• В вопросе отсутствуют какие-либо выборочные данные, что затрудняет вам понимание любых результатов, представленных здесь, но и труднее для нас готовить решения
• В вопросе также отсутствует окончательный «ожидаемый результат» (желаемый результат), и это привело к дальнейшей сложности ответа

Я надеюсь, что дополнительная предоставленная информация будет полезна, и что теперь вы также знаете, что для SQL является нормальным представлением данных в виде нескольких строк. Если вы не хотите, чтобы это нормальное поведение, пожалуйста, укажите, что вы действительно хотите в своем вопросе.

---

Постскриптум. Чтобы включить еще один подзапрос для «follow», вы можете использовать аналогичный подзапрос для того, который у вас уже есть. Он может быть добавлен до или после этого подзапроса. Вы также можете увидеть, что он используется в sqlfiddle здесь

 LEFT JOIN ( SELECT COUNT(*) FollowCNT , IdOtherUser FROM Activity WHERE type = 'Follow' GROUP BY IdOtherUser ) F ON USERS.id = F.IdOtherUser 

В то время как добавление другого подзапроса может решить ваше желание получить дополнительную информацию, общий запрос может замедляться пропорционально росту ваших данных. После того как вы определились с функциональностью, в которой вы действительно нуждаетесь, может быть полезно рассмотреть, какие индексы вам нужны для этих таблиц. (Я считаю, что вам будет предложено отдельно спросить этот совет, и если вы убедитесь, что вы включили 1. полный DDL ваших таблиц и 2. план объяснения запроса.)

Я немного потерян в вашем запросе, но если вы хотите сразу загрузить данные для нескольких сообщений, не стоит включать данные комментариев в первый запрос, так как вы будете включать все данные о почте и публикации пользователя несколько раз , Вы должны запустить другой запрос, который будет связывать сообщения с комментариями. Что-то вроде:

 SELECT A.UUIDPost, C.username, C.profileImage, B.Comment, B.[DateField] FROM Posts A JOIN Activities B ON A.uuid = B.UUIDPost JOIN Users C ON B.[UserId] = C.id 

и использовать эти данные для отображения ваших комментариев с комментариями идентификатора пользователя, имени, изображения и т. д.

Чтобы получить только 3 комментария за сообщение, вы можете посмотреть этот пост:

Выберите верхние 3 значения из каждой группы в таблице с SQL

если вы уверены, что в таблице комментариев или в этом сообщении не будет дубликатов строк:

Как выбрать верхние 3 значения из каждой группы в таблице с SQL, которые имеют дубликаты

если вы не уверены в этом (хотя из-за DateField в таблице это не должно быть возможным).

UNTESTED: я бы рекомендовал собрать скрипт SQL с некоторыми примерами данных и существующей структурой таблицы, показывающей проблему; таким образом мы могли бы играть с ответами и обеспечивать функциональность с вашей схемой.

Поэтому мы используем переменные для имитации функции окна (например, row_number)

в этом случае @Row_num и @prev_Value. @Row_number отслеживает текущую строку для каждого сообщения (поскольку в одном столбце может быть много комментариев), тогда, когда встречается новый идентификатор сообщения (UUIDPOST?), Переменная row_num сбрасывается до 1. Когда текущие записи UUIDPOST соответствуют переменной @prev_Value, мы просто увеличиваем строку на 1.

Этот метод позволяет присвоить номер строки, основанный на дате или порядке идентификатора активности, по убыванию. Поскольку каждое перекрестное соединение приводит только к 1 записи, мы не приводим к появлению повторяющихся записей. Однако, поскольку мы ограничиваемся row_number <= 2, мы получаем только два последних комментария в нашем недавно добавленном левом соединении.

Это предполагает, что сообщения, связанные с пользователями, являются «Многими к одному», а это означает, что почта может иметь только 1 пользователя.

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

 SELECT Posts.id, Posts.uuid, Posts.caption, Posts.path, Posts.date, USERS.id, USERS.username, USERS.fullname, USERS.profileImage, coalesce(A.LikeCNT,0) com.comment FROM Posts INNER JOIN USERS ON Posts.id = 145 AND USERS.id = 145 LEFT JOIN (SELECT COUNT(A.uuidPost) LikeCNT, A.UUIDPost FROM Activity A WHERE type = 'like' GROUP BY A.UUIDPOST) A on A.UUIDPost=Posts.uuid --This join simulates row_Number() over (partition by PostID, order by activityID desc) (Nice article [here](http://preilly.me/2011/11/11/mysql-row_number/) several other examples exist on SO already. --Meaning.... Generate a row number for each activity from 1-X restarting at 1 for each new post but start numbering at the newest activityID) LEFT JOIN (SELECT comment, UUIDPOST, @row_num := IF(@prev_value=UUIDPOST,@row_num+1,1) as row_number,@prev_value := UUIDPOST FROM ACTIVITY CROSS JOIN (SELECT @row_num := 1) x CROSS JOIN (SELECT @prev_value := '') y WHERE type = 'comment' ORDER BY UUIDPOST, --Some date or ID desc) Com on Com.UUIIDPOSt = Posts.UUID and row_number < = 2 -- Now since we have a row_number restarting at 1 for each new post, simply return only the 1st two rows. ORDER BY date DESC LIMIT 0, 5 

мы должны были поставить и row_number <= 2 в самом соединении. Если бы он был помещен в предложение where, вы потеряли бы эти сообщения без комментариев, которые, я думаю, вы все еще хотите.

Кроме того, мы должны, вероятно, посмотреть поле «комментарий», чтобы убедиться, что оно не пустое или пустое, но позволяет убедиться, что это работает в первую очередь.

Этот тип комментариев был опубликован много раз, и попытка получить «последний для каждого» всегда кажется камнем преткновения и кошмаром join / subquery для большинства.

Специально для веб-интерфейса вам может быть лучше приклеить столбец (или 2 или 3) к одной таблице, которая является вашей активной таблицей сообщений, такой как Latest1, Latest2, Latest3.

Затем, используя вставку в таблицу комментариев, введите триггер insert в таблицу, чтобы обновить основной пост с помощью нового идентификатора. Тогда у вас всегда есть этот идентификатор на столе без каких-либо подключений. Теперь, как вы уже упоминали, вы можете захотеть иметь последние 2 или 3 идентификатора, затем добавить 3 столбца образца и включить триггер вставки для комментариев в комментариях к сообщению, чтобы обновить основную таблицу сообщений, например,

 update PrimaryPostTable set Latest3 = Latest2, Latest2 = Latest1, Latest1 = NewDetailCommentID where PostID = PostIDFromTheInsertedDetail 

Это должно быть формализовано в надлежащий триггер по MySQL, но должно быть достаточно простым для реализации. Вы можете перенести список с последним 1, а затем, когда появятся новые сообщения, он автоматически перевернет самые последние в свои 1, 2, 3 позиции. Наконец, ваш запрос можно было бы упростить до

 Select P.PostID, P.TopicDescription, PD1.WhateverDetail as LatestDetail1, PD2.WhateverDetail as LatestDetail2, PD3.WhateverDetail as LatestDetail3 from Posts P LEFT JOIN PostDetail PD1 on P.Latest1 = PD1.PostDetailID LEFT JOIN PostDetail PD2 on P.Latest2 = PD2.PostDetailID LEFT JOIN PostDetail PD3 on P.Latest3 = PD3.PostDetailID where whateverCondition 

Денормализация данных обычно НЕ требуется. Однако в таких случаях он является большим упрощением для получения этих «последних» записей в запросе для каждого типа. Удачи.

Вот полный рабочий пример в MySQL, чтобы вы могли видеть таблицы и результаты sql-вставок и автоматическое тиснение с помощью триггера, чтобы обновить основную почтовую таблицу. Затем, запрашивая столбец, вы можете увидеть, как последние автоматически переходят в первую, вторую и третью позиции. Наконец, соединение, показывающее, как вытащить все данные из каждой «активности сообщения»,

 CREATE TABLE Posts ( id int, uuid varchar(7), imageLink varchar(9), `date` datetime, ActivityID1 int null, ActivityID2 int null, ActivityID3 int null, PRIMARY KEY (id) ); CREATE TABLE Activity ( id int, postid int, `type` varchar(40) collate utf8_unicode_ci, commentText varchar(20) collate utf8_unicode_ci, `date` datetime, PRIMARY KEY (id) ); DELIMITER // CREATE TRIGGER ActivityRecAdded AFTER INSERT ON Activity FOR EACH ROW BEGIN Update Posts set ActivityID3 = ActivityID2, ActivityID2 = ActivityID1, ActivityID1 = NEW.ID where ID = NEW.POSTID; END; // DELIMITER ; INSERT INTO Posts (id, uuid, imageLink, `date`) VALUES (123, 'test1', 'blah', '2016-10-26 00:00:00'); INSERT INTO Posts (id, uuid, imageLink, `date`) VALUES (125, 'test2', 'blah 2', '2016-10-26 00:00:00'); INSERT INTO Activity (id, postid, `type`, `commentText`, `date`) VALUES (789, 123, 'type1', 'any comment', '2016-10-26 00:00:00'), (821, 125, 'type2', 'another comment', '2016-10-26 00:00:00'), (824, 125, 'type3', 'third comment', '2016-10-27 00:00:00'), (912, 123, 'typeAB', 'comment', '2016-10-27 00:00:00'); -- See the results after the insert and the triggers. -- you will see that the post table has been updated with the -- most recent -- activity post ID=912 in position Posts.Activity1 -- activity post ID=789 in position Posts.Activity2 -- no value in position Posts.Activity3 select * from Posts; -- NOW, insert two more records for post ID = 123. -- you will see the shift of ActivityIDs adjusted INSERT INTO Activity (id, postid, `type`, `commentText`, `date`) VALUES (931, 123, 'type1', 'any comment', '2016-10-28 00:00:00'), (948, 123, 'newest', 'blah', '2016-10-29 00:00:00'); -- See the results after the insert and the triggers. -- you will see that the post table has been updated with the -- most recent -- activity post ID=948 in position Posts.Activity1 -- activity post ID=931 in position Posts.Activity2 -- activity post ID=912 in position Posts.Activity3 -- notice the FIRST activity post 789 is not there as -- anything AFTER the 4th entry, it got pushed away. select * from Posts; -- Finally, query the data to get the most recent 3 items for each post. select p.id, p.uuid, p.imageLink, p.`date`, A1.id NewestActivityPostID, A1.`type` NewestType, A1.`date` NewestDate, A2.id SecondActivityPostID, A2.`type` SecondType, A2.`date` SecondDate, A3.id ThirdActivityPostID, A3.`type` ThirdType, A3.`date` ThirdDate from Posts p left join Activity A1 on p.ActivityID1 = A1.ID left join Activity A2 on p.ActivityID2 = A2.ID left join Activity A3 on p.ActivityID3 = A3.ID; 

Вы можете создать тестовую базу данных, чтобы не повредить ваш, чтобы увидеть этот пример.

Вероятно, это избавит вас от незаконного сочетания сортировок … После установления соединения выполните этот запрос:

 SET NAMES utf8 COLLATE utf8_unicode_ci; 

Для вопроса о «последних 2», пожалуйста, используйте инструмент командной строки mysql и запустите SHOW CREATE TABLE Posts и предоставите вывод. (То же самое для других соответствующих таблиц.) Phpmyadmin (и другие пользовательские интерфейсы) имеют способ выполнить запрос, не входя в командную строку.

Вы можете получить там довольно простой запрос, используя подзапросы. Сначала я указываю пользователя в where-clause и присоединяюсь к сообщениям, потому что мне кажется более логичным. Затем я получаю все понравившиеся сообщения для сообщения с подзапросом.

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

INNER JOIN Деятельность, если вы хотите показывать только сообщения с хотя бы одним комментарием.

 SELECT u.id, u.username, u.fullname, u.profileImage, p.uuid, p.caption, p.path, p.date, (SELECT COUNT(*) FROM Activity v WHERE v.uuidPost = p.uuidPost AND v.type = 'like') likes, a.commentText, a.date FROM Users u INNER JOIN Posts p ON p.id = u.id LEFT JOIN Activity a ON a.uuid = p.uuid AND a.type = 'comment' AND 2 > ( SELECT COUNT(*) FROM Activity v WHERE v.uuid = p.uuid AND v.type = 'comment' AND v.date > a.date) WHERE u.id = 145 

---

Тем не менее, редизайн, вероятно, был бы лучшим, а также с точки зрения производительности (активность скоро будет содержать много записей, и их всегда нужно фильтровать для желаемого типа). Таблица пользователей в порядке с автоматическим добавлением идентификатора и в качестве первичного ключа. Для сообщений я также добавлял бы auto-incremented id в качестве первичного ключа и user_id в качестве внешнего ключа (вы также можете решить, что делать при удалении, например, с помощью каскада все его сообщения также будут автоматически удалены).

Для комментариев и комментариев вы можете создавать разделенные таблицы с двумя внешними ключами user_id и post_id (простой пример, например, это могут быть только сообщения и ничего больше, но если их не так много, это может быть полезно создать post_likes и некоторые другие …, которые любят таблицы, вам нужно подумать о том, как эти данные обычно запрашиваются, если эти симпатии в основном независимы друг от друга, это, вероятно, хороший выбор).