Forum Discussion

peterlu's avatar
peterlu
Champion
10 years ago

API v2 - fetch messages from certain groups of users by roles name

SELECT * FROM messages WHERE author.roles.name IN ('Administrator', 'BlogAuthor') It shows Syntax error. "'author.roles.name' is not a valid constraint for the object 'messages'.   Any ideas? And...
SuzieH's avatar
SuzieH
Khoros Alumni (Retired)

Hi peterlu 

 

I think you're going to need separate calls. One to get the messages, such as:

SELECT messages FROM users WHERE roles.name IN ('Administrator', 'BlogAuthor')

And then using the returned query from there to get the message data... something like:

SELECT * FROM messages WHERE author.id IN ('3','17','43')

 

 

 

 

peterlu's avatar
peterlu
Champion
10 years ago

@SusanH, thanks for the info. The only problem of this solution is that it is not scalable. When there are a lot of authors of certain roles, the performance is not great. So we probably have to add in a date range into WHERE to limit the query.

  • cblown's avatar
    cblown
    Boss
    10 years ago

    Where is JOIN  :smileytongue: