MYSQL Query - Get latest comment related to the post

greatest-n-per-group, mysql, php, sql

Solution

This error message

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

is typically due to the definition of your columns and tables. It usually means that on either side of an equal sign there are different collations. What you need to do is choose one and include that decision in your query.

The collation issue here was in the CROSS JOIN of @prev_value which needed an explicit collation to be used.

I have also slightly changed the "row_number" logic to a single cross join and moved the if logic to the extremes of the select list.

Some sample data is displayed below. Sample data is needed to test queries with. Anyone attempting to answer your question with working examples will need data. The reason I am including it here is twofold.

- so that you will understand any result I present

- so that in future when you ask another SQL related question you understand the importance of supplying data. It is not only more convenient for us that you do this. If the asker provides the sample data then the asker will already understand it - it won't be an invention of some stranger who has devoted some of their time to help out.

Sample Data

Please note some columns are missing from the tables, only the columns specified in the table details have been included.

This sample data has 5 comments against a single post (no likes are recorded)

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 Standard behaviour: 2 rows per Post query]

This was my initial query, with some corrections. I changed the column order of the select list so that you will see some comment related data easily when I present the results. Please study those results they are provided so you may understand what the query will do. Columns preceded by # do not exist in the sample data I am working with for reasons I have already noted.

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
      ;
      
      

See a working demonstration of this query at SQLFiddle

Results:

|  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 |

There are 2 ROWS - as expected. One row for the most recent comment, and another rows for the next most recent comment. This is normal behaviour for SQL and until a comment was added under this answer readers of the question would assume this normal behaviour would be acceptable.

The question lacks a clearly articulated "expected result".

[Option 1: One row per Post query, with UP TO 2 comments, added columns]

In a comment below it was revealed that you did not want 2 rows per post and this would be an easy fix. Well it kind of is easy BUT there are options and the options are dictated by the user in the form of requirements. IF the question had an "expected result" then we would know which option to choose. Nonetheless here is one option

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
      ;

See the second query working at SQLFiddle

Results of query 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 |

** Option 2, concatenate the most recent comments into a single comma separated list **

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
      

See this third query working at SQLFiddle

Results of query 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 |

** Summary **

I have presented 3 queries, each one shows only the 2 most recent comments, but each query does that in a different way. The first query (default behaviour) will display 2 rows for each post. Option 2 adds a column but removes the second row. Option 3 concatenates the 2 most recent comments.

Please note that:

- The question lacks table definitions covering all columns

- The question lacks any sample data, which makes it harder for you to understand any results presented here, but also harder for us to prepare solutions

- The question also lacks a definitive "expected result" (the wanted output) and this has led to further complexity in answering

I do hope the additional provided information will be of some use, and that by now you also know that it is normal for SQL to present data as multiple rows. If you do not want that normal behaviour please be specific about what you do really want in your question.

Postscript. To include yet another subquery for "follows" you may use a similar subquery to the one you already have. It may be added before or after that subquery. You may also see it in use at sqlfiddle here

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

Whilst adding another subquery may resolve your desire for more information, the overall query may get slower in proportion to the growth of your data. Once you have settled on the functionality you really need it may be worthwhile considering what indexes you need on those tables. (I believe you would be advised to ask for that advice separately, and if you do make sure you include 1. the full DDL of your tables and 2. an explain plan of the query.)

Problem

Original source

Related problems

• How to select top 3 values from each group in a table with SQL which have duplicates