Query

Overview

Couchbase Lite 2.0 includes support for N1QL like query interface. Database can be queried by constructing a query using a Query builder and then executing that query.

The Query interface in Couchbase Lite 2.0 is poweful and includes support for the following among others

  • Pattern Matching

  • Regex Matching

  • Math Functions

  • String Manipulation Functions

  • Aggregate Functions

  • Grouping

  • Joins (within single database)

  • Sorting

  • NilOrMissing properties

Implementation Pattern

The select clause and where clause in the Query statement require a CouchbaseLite.Expression type. Consider the following query.

let hotelSearchQuery = Query
  .select(SelectResult.expression(Meta.id),
          SelectResult.expression(Expression.property("name")))
  .from(DataSource.database(db))
  .where(
      Expression.property("description").like("%\(descriptionStr)%")
     .and(Expression.property("type").equalTo("hotel"))
     .and(Expression.property("country").equalTo(locationStr)
     .or(Expression.property("city").equalTo(locationStr))
     .or(Expression.property("state").equalTo(locationStr))
     .or(Expression.property("address").equalTo(locationStr)))
  )

Often times, the same Expression may be required across multiple queries. This can quickly become tedious and difficult to maintain. The recommended pattern is to define constants corresponding to the Expressions and to reuse them across queries.

Open the QueryConsts.swift file. This file defines the CouchbaseLite expressions that are used in the Travel App

  struct _Property {
  // Query: Property Expressions
  static let DOCID        = Meta.id

  static let TYPE         = Expression.property("type")
  static let USERNAME     = Expression.property("username")
  static let FLIGHTS      = Expression.property("flights")
  static let DESCRIPTION  = Expression.property("description")
  static let NAME         = Expression.property("name")
  static let COUNTRY      = Expression.property("country")
  static let CITY         = Expression.property("city")
  static let STATE        = Expression.property("state")
  static let ADDRESS      = Expression.property("address")
  static let FAA          = Expression.property("faa")
  static let ICAO         = Expression.property("icao")
  static let AIRPORTNAME  = Expression.property("airportname")
  }

  struct _SelectColumn {
      // Query: Select Results
      static let NAMERESULT         = SelectResult.expression(_Property.NAME)
      static let AIRPORTNAMERESULT  = SelectResult.expression(_Property.AIRPORTNAME)
      static let FLIGHTSRESULT      = SelectResult.expression(_Property.FLIGHTS)
      static let DOCIDRESULT        = SelectResult.expression(_Property.DOCID)
      static let COUNTRESULT        = SelectResult.expression(Function.count(1))
      static let ALLRESULT          = SelectResult.all()
  }

Simple Query

The travel app has many instances of querying the database. We will discuss a simple example here.

Open the file AirportPresenter.swift. We will review the fetchAirportsMatching method.

func fetchAirportsMatching( _ searchStr:String, handler:@escaping(_ airports:Airports?, _ error:Error?)->Void) {
  ...
}

There are 3 different queries in this function body. The query that is ran depends on the length of the search term. You can ignore this specificity, in this section we will look at the 3rd query.

The query below selects the "name" property in documents from the database where the type property is equal to airport and the "airportname" property is equal to the search term.

searchQuery = QueryBuilder
  .select(_SelectColumn.AIRPORTNAMERESULT)
  .from(DataSource.database(db))
  .where(_Property.TYPE
      .equalTo(Expression.string("airport"))
      .and (Expression.property("airportname")
      .like(Expression.string("\(searchStr)%"))))

Next, the query is executed using the execute() method. Each row in the result will contain a single property called "airportname". The final result is passed to the handler callback where the result will be displayed in a TableView.

if let searchQuery = searchQuery {
var matches:Airports = []
do {
    for row in try searchQuery.execute() {
        if let match = row.string(forKey: "airportname") {
            matches.append( match)
        }
    }
    handler(matches,nil)
}
catch {
    handler(nil,error)
  }
}

Try it out

  • Log into the Travel Sample Mobile app as “demo” user and password as “password”

  • Tap on "+"" button to make a flight reservation

  • In the “From” airport textfield, enter "DTW""

  • Verify that the first item in the drop down list is "Detroit Metro Wayne Co""

    ios simple query

Advanced Query

In this section we will discuss the JOIN query. JOIN Queries in Couchbase Lite 2.0 are intra-database joins.

If you recall from the Data Modeling section, the document with a type equal to "bookmarkedhotels" contains a hotels property which is an array of IDs of bookmarked hotels.

{
  "_id": "hotel1",
  "name": "San Francisco Hotel",
  "address": "123, Park Street, San Francisco"
}

{
  "type": "bookmarkedhotels",
  "hotels": ["hotel1", "hotel2"]
}

We will review the query that fetches documents whose _id is included in the "hotels" property array of the document of type "bookmarkedhotels".

Open the file HotelPresenter.swift. We will review the fetchBookmarkedHotels method.

func fetchBookmarkedHotels( handler:@escaping(_ hotels:Hotels?, _ error:Error?)->Void) {
    ...
}

First, we instantiate two data sources which corresponds to the two sides of the join query.

let bookmarkDS = DataSource.database(db).as("bookmarkDS")
let hotelsDS = DataSource.database(db).as("hotelsDS")

Next we write the query expressions. The first one gets the hotels property on the bookmarks data source. The seconds get the document ID on the hotels data source.

let hotelsExpr = Expression.property("hotels").from("bookmarkDS")
let hotelIdExpr = Meta.id.from("hotelsDS")

Next, we use a function expression to find document’s whose \_id property is in the hotels array. And build the join expression.

let joinExpr = ArrayFunction.contains(hotelsExpr, value: hotelIdExpr)
let join = Join.join(hotelsDS).on(joinExpr);

Finally, the query object uses that join expression to find all the hotel document referenced in the "hotels" array of the bookmark document.

let typeExpr = Expression.property("type").from("bookmarkDS")

let bookmarkAllColumns = _SelectColumn.ALLRESULT.from("bookmarkDS")
let hotelsAllColumns = _SelectColumn.ALLRESULT.from("hotelsDS")

let query = QueryBuilder.select(bookmarkAllColumns, hotelsAllColumns)
                .from(bookmarkDS)
                .join(join)
                .where(typeExpr.equalTo(Expression.string("bookmarkedhotels")));

And we use the execute() method to get the results back pass them on to the view.

for result in try query.execute() {
  if let hotel = result.dictionary(forKey: "hotelsDS")?.toDictionary() as? Hotel{
        bookmarkedHotels.append(hotel)
  }
}
handler(bookmarkedHotels,nil)

Try it out

  • Log into the Travel Sample Mobile app as "Guest" user by selecting "Proceed as Guest"

  • Tap on "Hotels"" button

  • In the "Description" text field, enter "pets"

  • In the "Location" text field, enter "London"

  • Verify that you see the "Novotel London West" listed

  • Swipe left to "bookmark" the hotel

  • Tap "Cancel" button

  • Verify that the Novatel hotel that you bookmarked earlier shows up in the list

    ios join query