Forum Discussion

Claudius's avatar
Claudius
Boss
5 years ago

LiQL: Exclude users with certain rank

I am building a user leaderboard component and exclude users with certain ranks. Unfortunately I cannot find a constraint to exclude users by rank.

These did not work:

 

SELECT * FROM users WHERE rank.name!='Administrator' ORDER BY kudos_received.sum(weight) DESC 
SELECT * FROM users WHERE NOT rank.name='Administrator' ORDER BY kudos_received.sum(weight) DESC

 

Any suggestions on how to filter already within the query? Otherwise I would have to filter while iterating the query result

  • PAULEM's avatar
    PAULEM
    Advisor
    5 years ago

    Hi Claudius 

    I had a play with the ranks "table" and this worked:

    SELECT * FROM users WHERE rank.id IN ('2','3','4') ORDER BY kudos_received.sum(weight) DESC

    While not elegant, maybe this would serve?  Assuming "Administrator" has an ID of "1", leaving it out of the list would exclude Administrators from the results.

    • Claudius's avatar
      Claudius
      Boss
      5 years ago

      This would be another workaround, but hard to manage since we have 26 ranks right now and I don't want to create a future dependency to update the code any time we change the rank structure. Ideally we should be able to just exclude ranks in the query via negation already. 

      • luk's avatar
        luk
        Boss
        5 years ago

        Claudius unfortunately there's no elegant way of doing this, you either have to make two queries to get all ranks or roles, then compile the IN condition for the second query from that, excluding whatever ranks/roles you do not want OR as you wrote, handle it while you loop over the users, the problem there is, that you don't know how many users in the amount of users you fetched will be excluded, so if you want to make a Top 20 and you fetch 25, it's possible that 8 of these users are Admins and therefore your Top 20 would only be a Top 17... that means you need to  increase the LIMIT on your query and hope for the best...

        The main issue here is that the v2 API primarily uses a "whitlisting" approach for WHERE conditions, e.g. you have to specify whatever you WANT, but you can't really tell it to return everything EXCEPT some things you do NOT WANT =)...

        If you think that would be useful, consider upvoting my related idea (which in the end would basically allow for NOT IN() where conditions...

        https://community.khoros.com/t5/Khoros-Community-Ideas/API-v2-boards-collection-Allow-conversation-style-constraint/idi-p/491889

         

        I would have suggested to aggregate all top level roles (I assume that would be enough for your filter purposes) with:

        SELECT * FROM roles WHERE node.id = 'community:<community-id>'

        as described in the docs but unfortunately I was unable to successfully use this query, as it simply returns an empty result instead of the top-level roles... can you reproduce that on your side as well? Claudius ping SuzieH