Querying records with logical filters

From DreamFactory
Jump to: navigation, search
DreamFactoryTutorialsQuerying records with logical filters
Line 1: Line 1:
### Tutorial
+
=== Tutorial ===
  
 
GET records in a SQL or NoSQL database with a filter string. Try these examples in the 'API Docs' tab of the DreamFactory Admin Console or from the command line with cURL.
 
GET records in a SQL or NoSQL database with a filter string. Try these examples in the 'API Docs' tab of the DreamFactory Admin Console or from the command line with cURL.
Line 5: Line 5:
 
Note that filter strings are simply SQL queries with ordinary SQL operators, including <, <=, >, >=, =, or, and, like.
 
Note that filter strings are simply SQL queries with ordinary SQL operators, including <, <=, >, >=, =, or, and, like.
  
### API Endpoint
+
=== API Endpoint ===
  
 
<pre>GET https://{url}/api/v2/{api_name}/_table/{table_name}?filter={filter_string}</pre>
 
<pre>GET https://{url}/api/v2/{api_name}/_table/{table_name}?filter={filter_string}</pre>
  
### API Docs Screenshot
+
=== API Docs Screenshot ===
  
[[File:Swagger-filter-new.png|1000px]]
+
[[File:Swagger-filter-new.png|800px]]
  
### Example - GET contact records whose last name starts with 'Y'
+
=== Example - GET contact records whose last name starts with 'Y' ===
  
 
* Table name: contact
 
* Table name: contact
Line 19: Line 19:
 
* Request URL: <pre>https://foo.com/api/v2/db/_table/contact?filter=last_name%20like%20Y%25</pre>
 
* Request URL: <pre>https://foo.com/api/v2/db/_table/contact?filter=last_name%20like%20Y%25</pre>
  
### Example - GET contact records whose last name is 'Yang'
+
=== Example - GET contact records whose last name is 'Yang' ===
  
 
* Table name: contact
 
* Table name: contact
Line 25: Line 25:
 
* Request URL: <pre>https://foo.com/api/v2/db/_table/contact?filter=last_name%20%3D%20Yang</pre>
 
* Request URL: <pre>https://foo.com/api/v2/db/_table/contact?filter=last_name%20%3D%20Yang</pre>
  
### Example - GET contact records whose first name is 'Jon' and last name is 'Yang'
+
=== Example - GET contact records whose first name is 'Jon' and last name is 'Yang' ===
  
 
* Table name: contact
 
* Table name: contact
Line 31: Line 31:
 
* Request URL: <pre>https://foo.com/api/v2/db/_table/contact?filter=(first_name%20%3D%20Jon)%20and%20(last_name%20%3D%20Yang)</pre>
 
* Request URL: <pre>https://foo.com/api/v2/db/_table/contact?filter=(first_name%20%3D%20Jon)%20and%20(last_name%20%3D%20Yang)</pre>
  
### Example - GET contact records whose first name starts with 'J' and last name starts with 'Y'
+
=== Example - GET contact records whose first name starts with 'J' and last name starts with 'Y' ===
  
 
* Table name: contact
 
* Table name: contact
Line 37: Line 37:
 
* Request URL: <pre>https://foo.com/api/v2/db/_table/contact?filter=(first_name%20like%20J%25)%20and%20(last_name%20like%20Y%25)</pre>
 
* Request URL: <pre>https://foo.com/api/v2/db/_table/contact?filter=(first_name%20like%20J%25)%20and%20(last_name%20like%20Y%25)</pre>
  
### Example - GET contact records whose Twitter handle contains 'jon' or whose Skype handle contains 'jon'
+
=== Example - GET contact records whose Twitter handle contains 'jon' or whose Skype handle contains 'jon' ===
  
 
* Table name: contact
 
* Table name: contact
 
* Filter parameter in API call: <pre>(twitter like %jon%) or (skype like %jon%)</pre>
 
* Filter parameter in API call: <pre>(twitter like %jon%) or (skype like %jon%)</pre>
 
* Request URL: <pre>https://foo.com/api/v2/db/_table/contact?filter=(twitter%20like%20%25jon%25)%20or%20(skype%20like%20%25jon%25)</pre>
 
* Request URL: <pre>https://foo.com/api/v2/db/_table/contact?filter=(twitter%20like%20%25jon%25)%20or%20(skype%20like%20%25jon%25)</pre>

Revision as of 17:44, 3 February 2016

Tutorial

GET records in a SQL or NoSQL database with a filter string. Try these examples in the 'API Docs' tab of the DreamFactory Admin Console or from the command line with cURL.

Note that filter strings are simply SQL queries with ordinary SQL operators, including <, <=, >, >=, =, or, and, like.

API Endpoint

GET https://{url}/api/v2/{api_name}/_table/{table_name}?filter={filter_string}

API Docs Screenshot

Swagger-filter-new.png

Example - GET contact records whose last name starts with 'Y'

  • Table name: contact
  • Filter parameter in API call:
    last_name like Y%
  • Request URL:
    https://foo.com/api/v2/db/_table/contact?filter=last_name%20like%20Y%25

Example - GET contact records whose last name is 'Yang'

  • Table name: contact
  • Filter parameter in API call:
    last_name = Yang
  • Request URL:
    https://foo.com/api/v2/db/_table/contact?filter=last_name%20%3D%20Yang

Example - GET contact records whose first name is 'Jon' and last name is 'Yang'

  • Table name: contact
  • Filter parameter in API call:
    (first_name = Jon) and (last_name = Yang)
  • Request URL:
    https://foo.com/api/v2/db/_table/contact?filter=(first_name%20%3D%20Jon)%20and%20(last_name%20%3D%20Yang)

Example - GET contact records whose first name starts with 'J' and last name starts with 'Y'

  • Table name: contact
  • Filter parameter in API call:
    (first_name like J%) and (last_name like Y%)
  • Request URL:
    https://foo.com/api/v2/db/_table/contact?filter=(first_name%20like%20J%25)%20and%20(last_name%20like%20Y%25)

Example - GET contact records whose Twitter handle contains 'jon' or whose Skype handle contains 'jon'

  • Table name: contact
  • Filter parameter in API call:
    (twitter like %jon%) or (skype like %jon%)
  • Request URL:
    https://foo.com/api/v2/db/_table/contact?filter=(twitter%20like%20%25jon%25)%20or%20(skype%20like%20%25jon%25)