MYSQL Query - Get latest comment related to the post
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_likesFROM PostsINNER JOIN USERS ON Posts.id = 145 AND USERS.id = 145LEFT 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 <= 2ORDER BY posts.`date` DESC ; See a working demonstration of this query at 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 |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_likesFROM PostsINNER JOIN USERS ON Posts.id = 145 AND USERS.id = 145LEFT 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 <= 2GROUP 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
| 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_likesFROM PostsINNER JOIN USERS ON Posts.id = 145 AND USERS.id = 145LEFT 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 <= 2GROUP 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
| 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.IdOtherUserWhilst 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.)
I am a little bit lost in your query, but if you want to download data for multiple posts at once, it's not a good idea to include comment data in the first query since you would include all the data about post and posting user multiple times. You should run another query that would connect posts with comments. Something like:
SELECT A.UUIDPost, C.username,C.profileImage, B.Comment,B.[DateField]FROM Posts A JOIN Activities B ON A.uuid = B.UUIDPost JOINUsers C ON B.[UserId] = C.id and use that data to display your comments with commenting user id, name, image etc.
To get only 3 comments per post, you can look into this post:
Select top 3 values from each group in a table with SQL
if you are sure that there are going to be no duplicate rows in the comment table or this post:
How to select top 3 values from each group in a table with SQL which have duplicates
if you're not sure about that (although due to DateField in the table, it should not be possible).
UNTESTED: I would recommend putting together an SQL fiddle with some sample data and your existing table structure showing the problem; that way we could play around with the responses and ensure functionality with your schema.
So we use a variables to simulate a window function (Such as row_number)
in this case @Row_num and @prev_Value. @Row_number keeps track of the current row for each post (since a single post could have lots of comments) then when the a new post ID (UUIDPOST?) is encountered the row_num variable is reset to 1. When the current records UUIDPOST matches the variable @prev_Value, we simply increment the row by 1.
This technique allows us to assign a row number based on the date or activity ID order descending. As each cross join only results in 1 record we don't cause duplicate records to appear. However, since we then limit by row_number < = 2 we only get the two most recent comments in our newly added left join.
This assumes posts relation to users is a Many to one, meaning a post can only have 1 user.
Something like This: though I'm not sure about the final left join I need to better understand the structure of the activity table thus a comment against the original question.
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, 5we had to put the and row_number < = 2 on the join itself. If it was put in the where clause you would lose those posts without any comments which I think you still want.
Additionally we should probably look at the "comment" field to make sure it's not blank or null, but lets make sure this works first.