Forum Discussion

micha80's avatar
micha80
Expert
10 years ago

Condition for more then one role

Hi,

 

I want to generate a custom leaderboard based on role conditions. The Leaderboard should only show member which have two specific roles assigned. For this I gave the API v2 a chance and created the following query: 

 

SELECT * FROM users WHERE roles.name = 'Role1' AND roles.name = 'Role2'

But the API is not working as expected. The AND condition is working like an OR condition. This means I get all user which have Role1 or Role2 assigned. But not a list of users which have both assigned.

 

Do I something wrong? Is there another way to reach the goal?

 

Regards,

Michael

 

  • OlivierS's avatar
    OlivierS
    Lithium Alumni (Retired)
    10 years ago

    micha80 Try to use the clause 'IN' per the User Resource example:

     

    SELECT id, login FROM users WHERE roles.name='Administrator'
SELECT * FROM users WHERE roles.name IN ('Administrator', 'Community Manager')
    • PaoloT's avatar
      PaoloT
      Lithium Alumni (Retired)
      10 years ago

      Hi micha80

       

      if that is indeed the behavior you are seeing, I think you should flag this with support as a possible bug. Thanks,

       

    • micha80's avatar
      micha80
      Expert
      10 years ago

      Hi Paolo,

       

      thanks for the reply. I tried the following:

       

      1.  SELECT * FROM users WHERE roles.name = 'Role1' AND roles.name = 'Role2'

      Result Count: 4

      Out of the 4 items 3 user have only the role "Role1" and 1 user has both roles assigned.

       

      2. SELECT * FROM users WHERE roles.name IN ('Role1') AND roles.name IN ('Role2')

      Same results like descripted in 1.

       

      3. SELECT * FROM users WHERE roles.name IN ('Role1', 'Role2')

      Result Count: 5

      Out of the 5 items 3 user have only the role "Role1", 1 user has only the role "Role2" and 1 user has both roles assigned.

       

      Only the user with both roles assigned is correct. The others a wrong.

       

      Regards,

      Michael

      • OlivierS's avatar
        OlivierS
        Lithium Alumni (Retired)
        10 years ago

        micha80 sorry, missread your issue ... There isn't something in the documentation saying you could use 'AND'. But nothing saying you shouldn't !

         

        A quick workaround would be to add another role to the users if they have both roles, then use the newly created role in your API call.

         

        But I would agree with PaoloT, raise a case with Lithium support to check if it's an expected behaviour.