User Likes

Where are likes stored in the database please e.g. what tables and what columns. Ideally I'd like to be able to extract the total number of likes per user regardless of what thread they made which is liked or reply which is liked of blog post made which is liked etc. I can extract the values if someone can point me in the right direction as to table and column definition and/or frequency that likes are added via any job scheduler task. Cheers. P :)
  • A quick scan of the database and SPROCs makes me believe they are stored in te_Content_ContentUserLikes - It appears there are the following columns:



    LikeTypeId (I have no idea the different types of likes)




    As far as frequency..I am not sure on that, but am guessing it is as soon as the user presses the "Like" button

  • In reply to Luke D:

    You should not be writing any SQL against the core database schema as it can cause data issues and is not supported.  While simple select statements are less risky they can still cause performance issues and the schema can change at any time without notice(this includes hotfixes)

  • In reply to Patrick Mason:

    Thanks Luke D - I did manage to find those tables eventually. Cheers.

    For reference I merely need to do a select and count where applicable as there's no other method available at present, plus fully aware of hotfixes etc - any code written that selects data in such a manner is easily remedied with correct working practices regardless of updates..

  • In reply to Paul Deehan:

    This is already supported in the API,  PublicApi.Likes.List(...)  takes a LikeListOptions that has a user ID parameter.  Same with the REST Api.  There is no need to have to use SQL to do this as the total is returned on either response

  • In reply to Patrick Mason:

    Have already looked at PublicApi.Likes.List (an actual proposed answer compared to the former obtuse response). According to this - references are made to parameters contentId and contentTypeId and unfortunately not userId. Not to worry, I've resolved the originally query and extended to include other areas I now require to total up.

  • In reply to Paul Deehan:

    PMFJI here, but is this something you would care to share? We are looking for something along the lines of what you describe (particularly as it applies to individual metrics, which is something that is currently absent in the Analytics functionality) and would be interested to see what you are doing.

  • In reply to Phil Chouinard:

    There is a widget called "Liked Content" which displays all likes for a user.  It uses the below method which returns a "PagedSet" of "Like"- The PagedSet object does have a "TotalCount" field.  The only bad part is that if you want to get the total like count for a batch of users, you'd have to execute this multiple times which would not be very efficient.

    #set($pagedListResponse = $core_v2_like.List("%{ApplicationId = $applicationIdArg, ContainerId = $containerIdArg, ContentId = $contentIdArg, ContentTypeId = $contentTypeIdArg, ContentUrl = $contentUrlArg, PageIndex = $pageIndexArg, PageSize = $pageSizeArg, SortBy = $sortByArg, SortOrder = $sortOrderArg, TypeId = $typeIdArg, UserId = $userIdArg}"))

    If you want to run a SQL query across a set of users, you could do something like:

    Select Count(*), CreatedUserId from te_Content_ContentUserLikes
    <Where Clauses to filter by date range or content type ids or a set of user  Ids>
      Group By CreatedUserId

    And Yes Patrick, we know this is not supported and would need to be re-done for 8.0 or even a 7.6 hotfix :)