Backendless Support
 

SQL-based search for data objects using console

As soon as a developer has data in a persistent storage, the question of searching would be one of the first to come up. Indeed, how can a developer queries the backend for data? 

Backendless offers the most popular one from the variety of options  - SQL. However, to allow SQL searches a few shortcuts had to be done - the most notable is that a developer can use only the "where" part of an SQL query to search for the  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: the syntax of the query follows the SQL-92 standard: 

cuisine = 'mexican' and name LIKE '%laredo%'
<span id="selection-marker-2" class="redactor-selection-marker"></span>

When an application is developers with Backendless, it is quite often when a developer needs 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 topic in action one should 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. It is necessary 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 it is shown in the topic, to reference a related property a developer simply uses 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. 

Review related topic:

Is article helpful?