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

Ответ 1

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

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

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

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

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

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

• чтобы вы поняли любой результат, который я представляю
• чтобы в будущем, когда вы задаете другой вопрос, связанный с 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 это нормально представлять как несколько строк. Если вы не хотите, чтобы это нормальное поведение, пожалуйста, укажите, что вы действительно хотите в своем вопросе.

Postscript. Чтобы включить еще один подзапрос для "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. план объяснения запроса.)

Ответ 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 в таблице это не должно быть возможно).

Ответ 3

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

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

в этом случае @Row_num и @prev_Value. @Row_number отслеживает текущую строку для каждого сообщения (поскольку у одного сообщения может быть много комментариев), тогда, когда встречается новый идентификатор сообщения (UUIDPOST?), Переменная row_num имеет значение reset до 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, вы потеряли бы те сообщения без каких-либо комментариев, которые, я думаю, вы все еще хотите.

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

Ответ 4

Этот тип комментариев был опубликован много раз, и попытка найти "последний для каждого" всегда является камнем преткновения и кошмаром 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;

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

Ответ 5

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

SET NAMES utf8 COLLATE utf8_unicode_ci;

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

Ответ 6

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

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

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

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

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