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 is it possible to do it in 1 query in API v2?

 

 

 

 

  • SuzieH's avatar
    SuzieH
    Khoros Alumni (Retired)
    10 years ago

    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.