Query Language Guide for iOS

1. Introduction

This tutorial will show you what a query language is and how to use it. A query language is a representation of the SQL language in the form of Objective-C code.

The examples in this tutorial are based on a database consisting of the tables book, author, book_author and genre.

The examples will be presented in the following way. First, we'll show you a database task in standard SQL query form. Second, we'll show the corresponding Objective-C code for Vertabelo Mobile ORM. For example:

SQL

select * from book;
            

Objective-C

NSArray *books = [bookDao getBookArray];
            

2. A database model in Objective-C code

There are two types of classes used for building queries in Vertabelo Mobile ORM

  • classes that map your database model
  • classes to create SQL expressions

Database models are mapped using the following classes:

  • class MOTableExpression, which represents a table expression (a table or a view) and can be accessed from each DAO as bookDao.TABLE_EXPRESSION
  • class MOColumn, which represents a table column and can be accessed from corresponding DAO as bookDao.COLUMN_NAME, for example bookDao.ID

The following classes are used to create SQL expressions:

  • classes MOSelectQuery, MOUpdateStatement, MOInsertStatement, MODeleteStatement - used to build queries or statements of the given type
  • class MOLExp - used to create logical expressions
  • class MOAExp - used to create arithmetical expressions

3. Query language

This section covers supported constructions from the SQL language - their representations and their limitations.

3.1. Retrieving data (SELECT)

An object of the type MOSelectQuery is used to build SELECT statements for retrieving data. To construct a query using this object, you can perform the following steps:

Create the object, stating which tables you are accessing. In this example, the table being accessed is called book.

MOSelectQuery *selectQuery = [MOSelectQuery selectFromTable:bookDao.TABLE_EXPRESSION];
              

Construct your query (a more detailed description will follow):

select.where = [bookDao.TITLE likeString:@"sql"];

// select's orderBy property is an array, so we create one-element array
select.orderBy = @[[MOOrderBy orderAscByColumn:bookDao.ID]];
              

Execute the query, using a DAO (data access object):

NSArray *books = [bookDao select:selectQuery];
              

This concludes the general example for MOSelectQuery. More details will be given in the following sections.

3.1.1. Retrieving records

3.1.1.1. An entire table

Here is an example that shows how to retrieve the contents of an entire table from a database:

SQL

select * from book;
                

Objective-C

MOSelectQuery *selectQuery = [MOSelectQuery selectFromTable:bookDao.TABLE_EXPRESSION];
NSArray *books = [bookDao select:selectQuery];
                

Or just:

NSArray *books = [bookDao getBookArray];
                
3.1.1.2. Sorting (ORDER BY)

Suppose you would like to retrieve the contents of the same table again, but this time sorted by title (default sort is ascending):

SQL

select * from book order by title;
                

Objective-C

MOSelectQuery *selectQuery = [MOSelectQuery selectFromTable:bookDao.TABLE_EXPRESSION];
selectQuery.orderBy = @[[MOOrderBy orderAscByColumn:bookDao.ID]];

NSArray *books = [bookDao select:selectQuery];
                

Or, more concisely:

NSArray *books = [bookDao getBookArrayOrderAscByColumn:bookDao.TITLE];
                
3.1.1.3. Sorting (ORDER BY DESC)

In this case, we would like to sort in descending order:

SQL

select * from book order by title desc;
                

Objective-C

MOSelectQuery *selectQuery = [MOSelectQuery selectFromTable:bookDao.TABLE_EXPRESSION];
selectQuery.orderBy = @[[MOOrderBy orderDescByColumn:bookDao.ID]];

NSArray *books = [bookDao select:selectQuery];
                

Or, more concisely:

NSArray *books = [bookDao getBookArrayOrderDescByColumn:bookDao.TITLE];
                
3.1.1.4. Limiting the number of records (LIMIT, OFFSET)

If, for example, you want to retrieve just the first 10 records, use the following code:

SQL

select * from book order by id desc limit 10;
                

Objective-C

MOSelectQuery *selectQuery = [MOSelectQuery selectFromTable:bookDao.TABLE_EXPRESSION];
selectQuery.orderBy = @[[MOOrderBy orderDescByColumn:bookDao.ID]];
selectQuery.limit = @10;

NSArray *books = [bookDao select:selectQuery];
                

And if you would like to read the succeeding ten rows (for the purpose of paging, for example), execute the following statement:

SQL

select * from book order by id desc limit 10 offset 10;
                

Objective-C

MOSelectQuery *selectQuery = [MOSelectQuery selectFromTable:bookDao.TABLE_EXPRESSION];
selectQuery.orderBy = @[[MOOrderBy orderDescByColumn:bookDao.ID]];
selectQuery.limit = @10;
selectQuery.offset = @10;

NSArray *books = [bookDao select:selectQuery];
                

3.1.2. Retrieving records that fulfill a given condition (WHERE)

In Vertabelo Mobile ORM, like in SQL, the WHERE clause is used to select records according to certain criteria. For example, in order to select rows with an identifier smaller than 100:

SQL

select * from book where id < 100;
                

Objective-C

MOSelectQuery *selectQuery = [MOSelectQuery selectFromTable:bookDao.TABLE_EXPRESSION];
selectQuery.where = [bookDao.ID isLessThanInteger:100];

NSArray *books = [bookDao select:selectQuery];
                

To select rows that have identifiers in the range of 90 to 100:

SQL

select * from book where id > 90 and id < 100;
                

Objective-C

MOSelectQuery *selectQuery = [MOSelectQuery selectFromTable:bookDao.TABLE_EXPRESSION];
selectQuery.where = [[bookDao.ID isGreaterThanInteger:90] and:[bookDao.ID isLessThanInteger:100]];

NSArray *books = [bookDao select:selectQuery];
                

Alternately, you could write the query like this:

SQL

select * from book where id between 90 and 100;
                

Objective-C

MOSelectQuery *selectQuery = [MOSelectQuery selectFromTable:bookDao.TABLE_EXPRESSION];
selectQuery.where = [bookDao.ID betweenInteger:90 and:100];

NSArray *books = [bookDao select:selectQuery];
                

This next example shows how to retrieve the same subset, but omit the rows that have no publisher:

SQL

select * from book where id between 90 and 100 and publisher is not null;
                

Objective-C

MOSelectQuery *selectQuery = [MOSelectQuery selectFromTable:bookDao.TABLE_EXPRESSION];
selectQuery.where = [[bookDao.ID betweenInteger:90 and:100] and:[bookDao.PUBLISHER isNotNull]];

NSArray *books = [bookDao select:selectQuery];
                

More information about the construction of expressions can be found at the end of this document (the MOLExp and MOAExp classes).

3.1.3. Aggregate functions

To execute a query with a function, use method select:withRowHandler: and supply the appropriate RowHandler.

For example:

SQL

select count(*)  from book ;
                

Objective-C

MOSelectQuery *selectQuery = [MOSelectQuery selectColumn:[MOAExp funWithName:@"COUNT" forArgument:[MOColumn asterisk]] fromTable:bookDao.TABLE_EXPRESSION];
NSNumber *count = [bookDao select:query withRowHandler:[MORowHandlerForNumber new]][0];
                

In this case [MOAExp funWithName:@"COUNT" forArgument:[MOColumn asterisk]] is the query column. The result of the count function will be a number, so the MORowHandlerForNumber used in the example will provide an object of the correct type.

DAOs provide a special method for calling aggregate functions. The above example can also be written in the following, easier way:

NSNumber *count = [bookDao invokeFunction:@"COUNT" withArgument:[MOColumn asterisk]];
                

If the count function is operating on an entire table, the code can be simplified even more:

NSNumber *count = [bookDao invokeForAllFunction:@"COUNT"];
                

Using function COUNT is so common that we provided DAOs with special method for it.

NSUInteger count = [bookDao getCount];
                

There's also a version with WHERE clause for this method.

NSUInteger count = [bookDao getCountWhere:[bookDao.ID isGreaterThanInteger:20]];
                

3.1.4. Grouping (GROUP BY, HAVING)

Here's an example of a query using grouping (GROUP BY) and filtering the outcome of an aggregate function. Specify the column that you want to "group by" by adding columns to array and setting it as a groupBy property of MOSelectQuery. Then set the MOLExp filter condition as having property.

SQL

select count(*), author_id from book group by author_id having count(*) > 3;
                

Objective-C

MOAExp *count = [MOAExp funWithName:@"COUNT" forArgument:[MOColumn asterisk]];

MOSelectQuery *selectQuery = [MOSelectQuery selectColumns:@[count, bookDao.GENRE_ID] 
                                                fromTable:bookDao.TABLE_EXPRESSION];
selectQuery.groupBy = @[bookDao.GENRE_ID];
selectQuery.having = [count isGreaterThanInteger:3];

NSArray *result = [bookDao select:selectQuery withRowHandler:[MORowHandlerRawData new]]
                

Here, you retrieve records using the method select:withRowHandler:. The second parameter is set as an object of the type MORowHandlerRawData, which specifies that each row will have a form of an array of objects. For your convenience, you can also create your own row handler conforming to our RowHandler protocol and handle the data in more explicit way.

3.1.5. Removing duplicate rows (DISTINCT)

You may want to remove duplicate rows from the query results. In SQL you can do this by adding the keyword DISTINCT to your query. Vertabelo Mobile ORM uses a similar solution. Have a look at this example:

SQL

select distinct title from book;
                

Objective-C

MOSelectQuery query = new MOSelectQuery(BookDAO.TABLE_EXPRESSION, BookDAO.TITLE);
MOSelectQuery *selectQuery = [MOSelectQuery selectColumn:bookDao.TITLE fromTable:bookDao.TABLE_EXPRESSION];
selectQuery.isDistinct = YES;
NSArray *distinctTitles = [bookDao select:selectQuery withRowHander:[MORowHandlerForString new]];
                

3.1.6. Selecting subset of columns (Row Handlers)

If you're selecting a subset of columns, you have to somehow handle the rows returned by a database. One way is to use MORowHandlerRawData, which will give you just an array of objects for each row. In this case, it's on you to take care of attaching correct type to each one. Another, more secure, way is to create your own row handler, which will transform an array of objects returned by a database to your desired form. Every row handler has to conform to MORowHandler protocol.

@protocol MORowHandler 
- (id)getObjectFromRow:(NSArray *)row;
@end
                

Argument row is an array representing one row returned by a database. The role of row handler is to wrap the query result in a more convenient way than just an array of objects. Let's take a look on a row handler generated for table author.

@implementation AuthorRowHandler

- (id)getObjectFromRow:(NSArray *)row {
    Author *author = [[Author alloc] init];

    author.id = row[0] == [NSNull null] ? nil : row[0];
    author.fullName = row[1] == [NSNull null] ? nil : row[1];

    return author;
}

@end
                

At first, row handler creates an object of Author class. Then, it assigns values from row array to respective field and returns the object. Note that NSArray can't contain nil values, so in the event of database returning NULL for some column, you will find an NSNull instance at this place - row handlers generated by us are converting NSNull objects to just nil.

The last thing that you need to know about row handlers is how to deal with NSDate (representing timestamp) and MODate (representing only date, like a date of birth) objects - for the rest, the approach from above is sufficient. Vertabelo Mobile ORM stores NSDate in database as a time interval and when you're retreving this data, you have to go back from time interval to NSDate object, just like in an example:

object.timestamp_field = row[0] == [NSNull null] ? nil : [NSDate dateWithTimeIntervalSince1970:[row[0] doubleValue]];
                

You have to get double value from NSNumber returned by a database and then create an NSDate object from that time interval.

MODate type is stored in a database as a string in format YYYY-MM-DD. To convert string in this format to MODate object, you have to use fromString: factory method.

object.date_field = row[1] == [NSNull null] ? nil : [MODate fromString:row[1]];
                

3.2. Adding rows (INSERT)

This section covers INSERT statements.

3.2.1. Adding a single record

If you want to add a single record to a table, you must create an object, fill it out with data, and then call the method, for example insertBook:

SQL

insert into book(author_id, edition_id, isbn, title) 
     values (1, 1, 'isbn', 'title'); 
                

Objective-C

Book *book = [Book book];

book.columnTitle = @"title";
book.columnIsbn = @"my-isbn";
book.columnPublication_year = @2015;
book.columnNumber_of_pages = @42;
book.columnGenreId = @1;

[bookDao insertBook:book];
                

As id column is an integer and a table's primary key, it's going to be filled in automatically - it's guaranteed by SQLite. Learn more about it here.

3.2.2. A general mechanism for inserting rows

A general mechanism for building row-insertion queries is provided by the class MOInsertStatement. With this class you have full control over the values being inserted. Note that you will have to set an ID for the object on your own - SQLite increments integer primary keys automatically, but MOInsertStatement don't assign this incremented ID to the object (what insertBook from DAO did). To not worry about that, you can still set an ID for the object manually.

Here is an example of inserting a row:

SQL

insert into book(id, author_id, edition_id, isbn, title) 
       values (100, 1, 1, 'isbn', 'title'); 
                

Objective-C

MOInsertStatement *query = [MOInsertStatement statementWithTableExpression:bookDao.TABLE_EXPRESSION];

[query addColumn:bookDao.ID withValue:@100];
[query addColumn:bookDao.TITLE withValue:@"My book"];
[query addColumn:bookDao.ISBN withValue:@"my-isbn-number"];
[query addColumn:bookDao.PUBLICATION_YEAR withValue:@2015];
[query addColumn:bookDao.NUMBER_OF_PAGES withValue:@42];
[query addColumn:bookDao.GENRE_ID withValue:@1];

[bookDao insert:query];
                

3.3. Updating records (UPDATE)

In this section, you'll learn how to modify data in a database using UPDATE statements.

3.3.1. Updating a single record

To start with, here is the simplest example of how to modify a single row:

SQL

update book set title = 'new' where id = 1
                

Objective-C

Book *book = [bookDao getById:@1];
book.columnTitle = @"New title.";
[bookDao updateBook:book];
                

3.3.2. A general mechanism for data modification

Sometimes you may need to modify several rows in one statement, or you may want to modify selected fields. Use MOUpdateStatement to do this.

In this example, you'll change a book's number of pages type for the given genre:

SQL

update book set number_of_pages = 1000 where genre_id = 1;
                

Objective-C

BookDAO *bookDao = [BookDAO daoWithDataSource:dataSource];
MOUpdateStatement *query = [MOUpdateStatement statementWithTableExpression:bookDao.TABLE_EXPRESSION];

[query updateColumn:bookDao.NUMBER_OF_PAGES withValue:@1000];
query.where = [bookDao.GENRE_ID isEqualToInteger:1];

[bookDao update:query];
                

3.3.3. Updating with reference to another table (UPDATE ... FROM)

In a situation where you want to modify rows in one table, but base the condition for the update on another table, you can use a query of the type UPDATE ... FROM. Here's an example:

update book set number_of_pages = 2000 from genre
where genre.name = 'Programming'  and 
      genre.id = book.columnGenre_id;
                

Queries of this type are not directly supported by Vertabelo Mobile ORM. You can write them as subqueries, however. Here is an example:

SQL

update book set number_of_pages = 2000 where genre_id =
   (select id from genre where name = 'Programming');
                

Objective-C

GenreDAO *genreDao = [GenreDAO daoWithDataSource:dataSource];

MOUpdateStatement *query = [MOUpdateStatement statementWithTableExpression:bookDao.TABLE_EXPRESSION];

[query updateColumn:bookDao.NUMBER_OF_PAGES withValue:@2000];

MOSelectQuery *subselect = [MOSelectQuery selectColumn:genreDao.ID fromTable:genreDao.TABLE_EXPRESSION];
subselect.where = [genreDao.NAME isEqualToString:@"Programming"];
query.where = [bookDao.GENRE_ID isEqualToQuery:subselect];

[bookDao update:query];
                

3.4. Deleting rows (DELETE)

Our last topic will be deleting rows using DELETE statements.

3.4.1. Deleting a single row

To start with, we'll look at deleting a single row. There are several ways of doing this:

delete from book where id = 1;
                

First, you can use an object:

Book *book = [bookDao getById:@1];
[bookDao deleteBook:book];
                

Second, you can use a primary key:

[bookDao deleteById:@1];
                

Third, you can use a conditional expression:

[bookDao deleteWhere:[bookDao.ID isEqualToInteger:1]];
                

Fourth, you can use MODeleteStatement:

MODeleteStatement *query = [MODeleteStatement statementWithTableExpression:bookDao.TABLE_EXPRESSION];
query.where = [bookDao.ID isEqualToInteger:1];

[bookDao delete:query];
                

3.4.2. Deleting multiple rows

Now we'll look at deleting multiple rows. This example removes all books about programming:

SQL

delete from book where title like '%programming%';
                

Objective-C

[bookDao deleteWhere:[bookDao.TITLE likeString:@"%programming%"]];
                

Your condition may be more complex. In this example, we'll use a subquery:

SQL

delete from book where id in (select book_id from book_author where author_id = (select id from author where name = 'Miguel de Cervantes'));
                

Objective-C

AuthorDAO *authorDao = [AuthorDAO daoWithDataSource:dataSource];
BookAuthorDAO *bookAuthorDao = [BookAuthorDAO daoWithDataSource:dataSource];

MOSelectQuery *subselectAuthorId = [MOSelectQuery selectColumn:authorDao.ID fromTable:authorDao.TABLE_EXPRESSION];
subselectAuthorId.where = [authorDao.NAME isEqualToString:@"Miguel de Cervantes"];

MOSelectQuery *subselectBookId = [MOSelectQuery selectColumn:bookAuthorDao.BOOK_ID fromTable:bookAuthorDao.TABLE_EXPRESSION];
subselectBookId.where = [bookAuthorDao.AUTHOR_ID isEqualToQuery:subselectAuthorId];

[bookDao deleteWhere:[bookDao.ID in:subselectBookId]];
                

3.5. Expressions

We'll finish off the tutorial with a bit of grammar for how to code arithmetic and logical expressions in the Vertabelo Mobile ORM query language.

3.5.1. Arithmetic expressions

Arithmetic expressions are accessed via the static methods of the MOAExp class. Here are the names of the arithmetic operators and their methods:

Operator Method name
+ add[AExp, Number, String, Data, Date, Integer]:
- sub[AExp, Number, String, Data, Date, Integer]:
* mul[AExp, Number, String, Data, Date, Integer]:
/ div[AExp, Number, String, Data, Date, Integer]:

If you want to use a constant, wrap it using the method exp. If you want to change the sign of an expression (negate it), use neg.

Let's see a couple of examples.

Example 1:

SQL

select id + 1000 from book;
                

Objective-C

MOSelectQuery *query = [MOSelectQuery selectColumn:[bookDao.ID addInteger:10000] fromTable:bookDao.TABLE_EXPRESSION];

NSArray *result = [bookDao select:query withRowHandler:[MORowHandlerForNumber new]];
                

Example 2:

SQL

select (-(id + 1000)) * 10 from book;
                

Objective-C

MOSelectQuery *query = [MOSelectQuery selectColumn:[[[bookDao.ID addInteger:10000] neg] mulInteger:10] fromTable:bookDao.TABLE_EXPRESSION];

NSArray *result = [bookDao select:query withRowHandler:[MORowHandlerForNumber new]];
                

3.5.2. Logical expressions

Methods for creating logical expressions can be found in the class MOLExp. It contains methods used to express the following logical relations:

Relation Method
IS NULL isNull
IS NOT NULL isNotNull
= isEqualTo[BOOL, Exp, Number, String, Data, Date, Integer, Query]:
<> (!=) isNotEqualTo[BOOL, Exp, Number, String, Data, Date, Integer, Query]:
> isGreaterThan[BOOL, Exp, Number, String, Data, Date, Integer, Query]:
< isLessThan[BOOL, Exp, Number, String, Data, Date, Integer, Query]:
>= isGreaterThanOrEqualTo[BOOL, Exp, Number, String, Data, Date, Integer, Query]:
<= isLessThanOrEqualTo[BOOL, Exp, Number, String, Data, Date, Integer, Query]:
a > b AND a < c [[a isGreaterThan:b] and:[a isLessThan:c]]
a IS BETWEEN b AND c [a between:b and:c]
LIKE like[Exp, String]:

Methods for creating logical expressions:

Operation Method
negation (NOT) not
conjunction (AND) and:
alternative (OR) or:

Example 1:

SQL

id < 10;
                

Objective-C

[bookDao.ID isLessThanInteger:10];
                

Example 2:

SQL

id < 10 and publisher is null;
                

Objective-C

[[bookDao.ID isLessThanInteger:10] and:[bookDao.PUBLISHER isNull]];
                

Example 3:

SQL

SELECT Genre.id, Genre.name FROM Genre WHERE NOT (EXISTS (SELECT * FROM Book WHERE Genre.id = book.columnGenre_id))
                

Objective-C

MOSelectQuery *subselect = [MOSelectQuery selectFromTable:bookDao.TABLE_EXPRESSION];
subselect.where = [genreDao.ID isEqualToExp:bookDao.GENRE_ID];

NSArray *result = [genreDao getGenreArrayWhere:[[MOLExp exists:subselect] not]];