Jack Jack - 1 month ago 9
SQL Question

MYSQL Query - Get latest comment related to the post

I am trying to get the latest 1 or 2 comments related to each post I download, a bit like instagram does as they show the latest 3 comments for each post, So far I am getting the posts & the likes counts.

Now all I need to do is figure out how to get the lates comments, not too sure how to approach it and that is why I hoping someone with alot more expertise can help me!

This is my current query:

SELECT Posts.id,
Posts.uuid,
Posts.caption,
Posts.path,
Posts.date,
USERS.id,
USERS.username,
USERS.fullname,
USERS.profileImage,
coalesce(A.LikeCNT,0)
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
ORDER BY date DESC
LIMIT 0, 5


Basically the comments are store in the same table as the likes.

So the Table is Activity, then I have a collumn call "comment" which store the comment text, and then the "type" is equal to "comment".

Possibly not very well explained but I am willing to try and give as much detail as possible!

If anyone can help it's very much appreciated!!

UPDATE

On this query given by http://stackoverflow.com/users/1016435/xqbert I am currently getting this error:

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

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
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, date DESC) Com
ON Com.UUIIDPOSt = Posts.UUID
AND row_number <= 2
ORDER BY date DESC
LIMIT 0, 5


Latest Edit

TABLES Examples

**Posts**

----------------------------------------------------------
| id | int(11) | | not null |
| uuid | varchar(100) | utf8_unicode_ci | not null |
| imageLink | varchar(500) | utf8_unicode_ci | not null |
| date | timestamp | | not null |
----------------------------------------------------------

**USERS**

-------------------------------------------------------------
| id | int(11) | | not null |
| username | varchar(100) | utf8_unicode_ci | not null |
| profileImage | varchar(500) | utf8_unicode_ci | not null |
| date | timestamp | | not null |
-------------------------------------------------------------


**Activity**

----------------------------------------------------------
| id | int(11) | | not null |
| uuid | varchar(100) | utf8_unicode_ci | not null |
| uuidPost | varchar(100) | utf8_unicode_ci | not null |
| type | varchar(50) | utf8_unicode_ci | not null |
| commentText | varchar(500) | utf8_unicode_ci | not null |
| date | timestamp | | not null |
----------------------------------------------------------


Those are some examples, in the "Activity" table in this case "type" will alwas be equal to "comment".

Sum up of everything and desire result:

When I query the users posts, I would like to be able to go into the "Activity" table and get the latest 2 comments for every posts he has. Maybe there will be no comments so obviously it would return 0, maybe there could be 100 comments for that post. But I only want to get the latest/most recent 2 comments.

An example could be looking at how Instagram does it. For every post the display the most recent comments 1, 2 or 3....

Hope this helps!

Answer

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

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.

  1. so that you will understand any result I present
  2. 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 preceeded 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 comemnt 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 queston had an "expeced result" then we would know which option to choose. Nnetheless 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 seperated 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 behavour) 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.