Syntax for SQL 'WHERE IN' list of values


#1

Using the API tool I’m testing out SQL queries prior to creating the ajax call from my app, am trying to use a filter with WHERE IN to limit my query but I can’t figure out what it wants the SQL syntax to look like.

If I try
filter: projectKey in (‘jdYSeNvdygt8vi6xj’, ‘B3JsENuwGSBXEfxq9’)

I get
"message": “Failed to filter records from ‘maProject’.\nExpected list or null”,

Same for
filter: projectKey in (‘jdYSeNvdygt8vi6xj’)

This returns the expected record:
filter: projectKey in ‘jdYSeNvdygt8vi6xj’

But this returns an empty set:
filter: projectKey in ‘jdYSeNvdygt8vi6xj’, ‘B3JsENuwGSBXEfxq9’

What does it want a list of string values to look like? I’m at a complete loss.


#2

If projectKey is the primary key for this table then you can just use

http[s]://{df url}/{sql service}/{table}?ids=key1,key2

If you would like to use a filter, you could do something like

http[s]://{df url}/{sql service}/{table}?filter=projectKey%20in%20("key1","key2")

Finally, you could do a post and include your filter as a data payload:

api url: http[s]://{df url}/{sql service}/{table}
data payload:
{"filter": "projectKey in ('key1','key2')"}

Here in the documentation is where you can find more information about retrieving records with filters. https://github.com/dreamfactorysoftware/dsp-core/wiki/Database-Retrieving-Records