How to Use SQL-Based Search for Data Objects in Backendless Console

As soon as you have data in persistent storage, the question of searching would be one of the first to come up. Indeed, how can you query the backend for data? We considered multiple options and elected to use the most popular one – SQL.

However, we had to take a few shortcuts to allow SQL searches – the most notable is you can use only the “where” part of an SQL query to search for your data. Consider the following data table:

There are 2 columns with primitive data types (cuisine and name) and another one with a relation pointing to the Location table. Suppose you want to find all the ‘fastfood’ restaurants, the following query can do that:

cuisine = 'fastfood'

The query references the “cuisine” column. The expression in the query matches any restaurant object where the “cuisine” property (or column) contains the value of “fastfood”.

Queries may get very complex. For example, they may reference more than one column/property. The following query finds all the restaurants serving the Mexican cuisine where the name of the restaurant contains the word “laredo”. Notice that the syntax of the query follows the SQL-92 standard:

cuisine = 'mexican' and name LIKE '%laredo%'

When you develop an application with Backendless, it is quite often when you need to debug a query or simply make sure it returns what you expect. In these cases, Backendless Console is the tool of choice – it provides a test-drive for running queries against your mBaaS backend.

To see the queries from this post in action follow the steps below:

1. Download the file from the URL below:
   https://backendless.com/documentation/samples/restaraunt-app-tables-with-data.zip
2. Follow the instructions on how to import schema/data into a Backendless application. You will need to import the file from step 1.
3. Login to Backendless Console, select the app and click the Data icon.
4. Select the Restaurant table.
5. Click the SQL Search toggle to set it to the ON state.
6. Type in the following query into the Search data..text field:

   cuisine = 'mexican' and name LIKE '%laredo%'

7. Backendless runs the search and shows the following result:
   

In addition to the primitive properties, a query may reference relations as well. Notice the Restaurant table contains a relation property called locations. The property has a one-to-many cardinality. The data you imported into your app (see the instructions above) includes a relation between a restaurant object and a location. The following query demonstrates how to run an SQL search by referencing a related property:

cuisine = 'mexican' and locations.city = 'Frisco'

As you can see to reference a related property, you simply use the dot notation to hop over to the properties of the related objects. In this case, the query searches for all Mexican restaurants located in ‘Frisco’. The part of the expression referencing the city name uses the ‘city’ column/property of the related Location objects.