Hm, not sure if it's even valid, but I'm not sure how to link users node-level roles (Which is basically what Category Experts does I think) with boards and therefore messages other than making a detour over the roles collection, e.g. start with something like this:
SELECT id, name, node.id FROM roles WHERE users.id = '${user.id}' LIMIT 1000
This is a bit ugly as it will literally list all roles of the current user (which you then have to loop over) and you can't filter them other than by boards, so if you only have specific boards where you have category experts enabled, you could add that to the query:
SELECT id, name, node.id FROM roles WHERE node.id IN('board:<board 1>', 'board:<board 2>') AND users.id = '${user.id}' LIMIT 1000
Then you get back a bunch of role objects, you need the role id or name to figure out if it's a CategoryExpert role of sorts or not, depending on how it is set up in your community. Basically you'll get back something like this from the API:
{
"type" : "role",
"id" : "b:<board.id>:<role.name>",
"name" : "CategoryExpert <optional.part.of.role.name>",
"node" : {
"type" : "node",
"id" : "board:<board.id>"
}
So you are looping and checking each object/hash for your criteria if it's a category expert role, and if so, store the node.id (you have to strip the part before the colon!) in an array/sequence and then make another query to the messages collection with a board.id IN() constraint, something like this:
"SELECT id FROM messages WHERE board.id = IN('" + ${boardids?join("','")} + "') AND depth = 0 LIMIT 100"
completely untested though, so no refunds 😜!
