Forum Discussion

Lindsey's avatar
Lindsey
Leader
6 years ago

Multiple "in" queries in REST-API

I am running into an issue where i want to run:

select * from messages where labels.text in ('Nursing', 'Project Manager') and labels.text in ('Walmart', 'Sprint')

That is, I want to say the message must have a label in the first group AND a label in the second group. However, when I run this it seems to stop after the first "in" statement, i.e. only run the part that says:

select * from messages where labels.text in ('Nursing', 'Project Manager')

Does anyone know why this is happening and if there is a way around it?

  • VikasB's avatar
    VikasB
    6 years ago

    Lindsey 
    Seems like the same constraint cannot apply multiple times in a single query. 
    Here is a workaround but you have to go a bit long way 
    1. Get message ids using below query

    select id from messages where labels.text in ('Nursing', 'Project Manager')

    2. Run below query 

    select * from messages where labels.text in ('Walmart', 'Sprint')

     3. Compare the result lists returned by step 1 and step 2. And fetch the ids which exist in both the lists. 
    Hope it will return the expected result.

  • VikasB's avatar
    VikasB
    Boss
    6 years ago

    Lindsey 
    Seems like the same constraint cannot apply multiple times in a single query. 
    Here is a workaround but you have to go a bit long way 
    1. Get message ids using below query

    select id from messages where labels.text in ('Nursing', 'Project Manager')

    2. Run below query 

    select * from messages where labels.text in ('Walmart', 'Sprint')

     3. Compare the result lists returned by step 1 and step 2. And fetch the ids which exist in both the lists. 
    Hope it will return the expected result.

    • Lindsey's avatar
      Lindsey
      Leader
      6 years ago

      The only problem with this solution is that I want to use LIMIT and OFFSET to the final solution to allow for pagination. I wouldn't know what to limit and offset each individual IN query by. Is there a workaround for this? Or is there any other way to do both OR and AND queries in one single query?