CODEWITHSUNDEEP
phpmysqldatabase
Chibuzo
Chibuzo

Reputation: 6117

How do I structure this kind of sql query

I have the following database table below enter image description here

I want to display notification to the user with owner_id 1. The notification show tell the owner that two users (with ids 17 and 2) commented on his/her post with post_id 1. I've tried the following query but it returns 2 rows instead. How can I can structure the query to return one row, because I want the notifications for one post to be returned together? Thank you.

SELECT DISTINCT commenters_id, id, owner_id, 
                post_id, type, UNIX_TIMESTAMP(date_done) AS date 
FROM notification 
GROUP BY commenters_id 
HAVING owner_id = '$user_id' AND commenters_id != '$user_id' 
ORDER BY date_done DESC

Upvotes: 2

Views: 107

Answers (3)

cypherabe
cypherabe

Reputation: 2603

your problem is that you select the date_done column, too, which makes the rows unique. Neither DISTINCT nor GROUP BY are allowed to group the result set under this circumstances.

Leave the column out of this query or use something like MAX(date_done)

EDIT: wait...you already get only two rows, not four ? Isn't that the result that you wanted? maybe I missunderstood the question

Upvotes: 0

ANisus
ANisus

Reputation: 78025

This code will not give you the commenters_id, but instead a count of how many people have replied to each post. The date will be the last time someone replied to that specific post:

SELECT
    COUNT(DISTINCT commenters_id) AS commenter_count,
    owner_id,
    post_id,
    type,
    UNIX_TIMESTAMP(MAX(date_done)) AS date 
FROM notification
WHERE owner_id = '$user_id' AND commenters_id != '$user_id' 
GROUP BY owner_id, post_id, type
ORDER BY UNIX_TIMESTAMP(MAX(date_done)) DESC

Upvotes: 1

Joni
Joni

Reputation: 111339

You can use GROUP_CONCAT(commenters_id) to get a comma-separated list of commenter IDs for the post. But then you have to group by post rather than commentor, so the query would look something like:

SELECT DISTINCT GROUP_CONCAT(commenters_id), id, owner_id, 
                post_id, type, UNIX_TIMESTAMP(date_done) AS date 
FROM notification 
GROUP BY post_id 
HAVING owner_id = '$user_id' AND commenters_id != '$user_id' 
ORDER BY date_done DESC

GROUP_CONCAT allows some processing of the data if you need it, like returning only distinct values or sorting the values. See the full documentation here: http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat

Upvotes: 1

Related Questions