Get the user who like the video?

PeerTube
1

Right now, I am just playing with the database only. I wanted to get the username of the person who have like a video. After digging some time, I found that in order to do this, I have 3 tables in database, i.e., user, userVideoHistory, video. When a user likes a video, the total like is increased in the video. I have the following database structure for User, userVideoHistory and Video respectively.

image
image938×546 5.41 KB

In order to get the user and like from PeerTube database, I have used the following sql query:

SELECT "video"."id", "video"."name", "video"."likes", "user"."username" FROM "video" JOIN "userVideoHistory" ON "userVideoHistory"."videoId"="video"."id" JOIN "user" ON "user"."id"="userVideoHistory"."userId";

The output is as follow:

image
image1436×332 20.3 KB

The problem is that, when i like a video from abc, the video likes is 1 and it is ok, But the root and other user is also displayed. I just want abc information to display when abc likes a video.

2

I don’t know where is this information, but it is not there. As likes are part of the federation/activityPub mechanism, I think you have to search around the «actor» table, not «user».
Be aware that actors are not necessarely local users. It can be from other instances, or even from other fediverse applications (mastodon, …).