Forum Discussion

bob-mccormick's avatar
bob-mccormick
Mentor
11 months ago

How can I list and count ALL bookmarks in the system?

I have been asked to analyze bookmarks, to determine such things as 

  • How many users make use of the bookmark feature in Khoros?
  • What is the maximum number of bookmarks for a user?
  • What is the average number of bookmarks for users with bookmarks?

To do this, I would normally use LiQL v2 API to do

{{prod}}/api/2.0/search?q=select count(*) from bookmarks

however this always returns 0! If I add a where clause for a user such as 

{{prod}}/api/2.0/search?q=select * from bookmarks where subscriber.id = '5375'

it works fine.  But I have 500k users so I am don't want to do it that way.

The filter by target.type does not work either.

I am logged in as Admin user.   Any help would be appreciated.

  • saikumarn's avatar
    saikumarn
    11 months ago

    bob-mccormick  

    If we use 'select count(*) from bookmarks,' it will only give the bookmarks at the current logged-in user level.

    If we want all users bookmarks, we can use 'select * from bookmarks where subscriber.id = '${user.id}'.

    1. Create an endpoint.
    2. Get all users using the users API with cursor (recommended) or offset.
    3. Then pass each user ID in the bookmarks query.
    4. This endpoint will provide all the data in one place.

3 Replies

Sort By
  • CarolineS's avatar
    CarolineS
    Boss
    11 months ago

    I don’t know the answer, but please share the results when you figure out how to do this! I’m curious if folks use bookmarks at all within Khoros - my suspicion is that the feature is barely used since it’s not particularly obvious (and there are other ways to bookmark things eg browser bookmarks)

  • saikumarn's avatar
    saikumarn
    Advisor
    11 months ago

    bob-mccormick  

    If we use 'select count(*) from bookmarks,' it will only give the bookmarks at the current logged-in user level.

    If we want all users bookmarks, we can use 'select * from bookmarks where subscriber.id = '${user.id}'.

    1. Create an endpoint.
    2. Get all users using the users API with cursor (recommended) or offset.
    3. Then pass each user ID in the bookmarks query.
    4. This endpoint will provide all the data in one place.

  • bob-mccormick's avatar
    bob-mccormick
    Mentor
    11 months ago

    Thank you saikumarn.  This will work, but it is a very inefficient solution.  I have over 300,000 users, and many of them do not have bookmarks (I assume).  There should be a way to just list all bookmarks (using cursors).  

    Or, list bookmarks in blocks of users (where subscriber.id >= 1 and subscriber.id <= 10000).

    If that is not the case, then the documentation should be updated to make it clear that the default is bookmarks for current user.  More actual examples would help. At my company, we do our API work with 2 shared credential, one for read-only work and one with full admin rights. These accounts would not have bookmarks, so it comes up empty.