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...
Forum Discussion
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.
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.
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/491889I 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
