LiQL: Exclude users with certain rank

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/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 

luk I can confirm the problem with querying from the roles collection with a node.id constraint. I tried with board, category and community node ids that I took from the valid results from a "SELECT * FROM roles" query, but for all simply get an empty success result set.

luk Sorry for the delayed response. I'm seeing the same thing in my test community. I'm raising this with the Engineer who added the constraint.

Claudius luk 

I spoke with the Engineer who added the constraint.

I think the best way to address this is to file a Support ticket and call this out as a bug. We added the constraint and it is does not seem to be implemented in a way that a customer using the Community API could make use of it. The engineer wasn't able to provide a satisfactory workaround either.

EDIT: The Engineering team is planning to remove the 'node.id' constraint from the public documentation. If you feel that this is in error, definitely file a feature enhancement or a bug to pursue the issue. Having a customer on a ticket carries more weight.

Thank you for bringing it to my attention.