I would like to implement 'Like' in my app (like facebook). Here is my table design:

CREATE TABLE [dbo].[Post]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    [Content] NVARCHAR(500) NOT NULL,
    [CreatedDate] datetime DEFAULT getdate()
)

CREATE TABLE [dbo].[Like]
(
    [Id] INT NOT NULL PRIMARY KEY Identity(1,1),
    [PostId] INT NOT NULL,
    [UserId] INT NOT NULL,
    FOREIGN KEY (PostId) REFERENCES Post(Id),
    FOREIGN KEY (UserId) REFERENCES User(Id)
)

CREATE TABLE [dbo].[User] (
    [Id] INT IDENTITY (1, 1) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

And below is the sql script to get Posts by page

SELECT 
    [Post].[Id],
    [Post].[Content]
FROM [dbo].[Post]
ORDER BY [Post].[CreatedDate] DESC
OFFSET @Offset ROWS
FETCH NEXT @GetCount ROWS ONLY

And here is my question

1. How can I also get the 'like count' for each posts in the above query?
2. How can I also get if the current user had liked each post or not?

I wish to get result like below in one query.

Thank you.

CodePudding user response：

Join posts to a derived table that performs the wanted counts

SELECT [Post].[Id]
    , [Post].[Content]
    , coalesce(likes.likes,0) as likes
    , coalesce(likes.cu_like,0) as cu_like
FROM [dbo].[Post]
LEFT JOIN (
    SELECT PostId
        , count(*) AS likes
        , max(CASE WHEN UserId = current_user THEN 1 ELSE 0 END) AS cu_like
    FROM [dbo].[Like]
    GROUP BY PostId
    ) AS likes ON [Post].[Id] = [likes].[PostId]
ORDER BY [Post].[CreatedDate] DESC 
OFFSET @Offset ROWS
FETCH NEXT @GetCount ROWS ONLY