Making queries

Androrm does not only provide automatic serialization/ deserialization of your models, but also gives you the opportunity, to execute complex queries on them. The only precondition to get started is, that you added all the correct relational fields to your models.

In the following examples, we assume to have the following two models set up: Book and Author.

public class Author extends Model {
    
    protected CharField mName;
    
    public Author() {
        super();
        
        mName = new CharField(80);
    }
}

public class Book extends Model {
    
    public static final QuerySet<Book> objects(Context context) {
        return objects(context, Book.class);
    }
    
    protected CharField mTitle;
    protected ForeignKeyField<Author> mAuthor;
    
    public Book() {
        super();
        
        mTitle = new CharField(80);
        mAuthor = new ForeignKeyField<Author>(Author.class);
    }
}

Also note, that even though the objects() method on the Model class of androrm is public, it is a good choice to have all of your models implement their own version of objects(), that calls the one of Model and hands in the correct value for the class parameter.


QuerySet<T extends Model> Since version 0.2

Query sets are androrm mechanism to build up your queries for the database. They are returned by the objects() function and also if you call get on a many to many or one to many field. That way you can add further restrictions without actually doing work on the database. Query sets are lazy, meaning, that they will only execute a query, if you ask for a specific result.

Another cool thing is, that query sets enable you to chain your calls to them. So you can for example drill down the end result by a first filter and then narrow down this result with a second one. Methods like count() are also a powerful mechanism, because they don't invoke object creation, but only execute the COUNT statement on the database, which is way faster.

As query sets implement Iterablefor loop. Just check out the following function descriptions and decide which fits your needs best.

QuerySet<T> all ( )

Apply a raw search, that will simply return the whole data-set present in the database.


int count ( )

Returns the number of objects, that will be returned by the current query.


QuerySet<T> distinct ( )

Query sets do not guarantee, that every item appears only once. In fact this could result in dramatic errors. So if you want a distinct list of items, call this method.


QuerySet<T> filter ( Filter filter )

Applies a filter to the current part of the query. If you apply multiple filters after each other, the most recent ones will only affect of the result prior to them. Meaning, if you first filter for name and then filter for age, the age filter will only apply to the results of the name filter.

filter

A Filter object, containing all rules, that should be applied to a query.


T get ( int id )

The get function can be used for an id-based lookup. You have to provide the exact id of the object, you are looking for. After you called get on a QuerySet you end the chaining.

id

ID of the model instance.


QuerySet<T> limit ( int offset, int limit )

If you want to limit the result of the current query to a certain number, call this function. Please read the descriptions for the parameters very carefully. This function is also available with only the limit parameter or in a version, where it accepts a Limit object.

offset

The start offset for a limit. This parameter is inclusive. Meaning that the index is starting at 0 for the first element and that this element will then also be included in the result.

limit

When slicing an output with offset and limit the limit is the exclusive end index for the slice. Meaning if you want the first 5 items, the offset would be 0 and the limit 5, which would result in returning the objects 0, 1, 2, 3 and 4.


QuerySet<T> orderBy ( String... columnNames )

With the orderBy function, you can sort the resulting output of the query. This can be very handy some times. For further description on how the order by mechanism works, please proceed to the order by section.

columnNames

All colum names after which the output should be ordered. You can influence the ordering with a preceding + or -. The + operator will force a ascending ordering and the - will force an descending ordering. If neither + nor - is supplied, the default case is ascending.


Filter

Again, if you already worked with django in the past, the following examples should look familiar to you.

The central class, when it comes to making queries is the Filter class. After you instantiated it, you will be equipped with three basic methods: in, is and contains. Each accepting two parameters. The first paramter hereby is a String, that describes the field, you want to look up. The second parameter is the value this field should have.

in

If you want to specify, that a field value should be contained in a list of values, than this is the operation to go for. Let's for example assume, that you want to get all books, that are either called "Awesome book" or "The boring book". First of, you have to create a list, containing all your values. After that create the FilterSet instance and add the list by handing it to the in function.

List<String> values = Arrays.asList(new String[] {
    "Awesome book",
    "The boring book"
});

Filter filter = new Filter();
filter.in("mTitle", values);

QuerySet<Book> books = Book.objects(getApplicationContext()).filter(filter);

After we have set up the filter, we can then use the objects(...).filter method on the Book model, to acutally execute it. Note, how I hand in the result of getApplicationContext(), to get the context I need. I do this, to not accidentially leakmemory. If you want to read more on this topic, I can recommend this article on the android blog.


is

The most commonly used filter is the is-filter. This one let's you exactly specify, what you are looking for. For example, if we only want books with the title "Awesome book", then our code would look like this:

Filter filter = new Filter();
filter.is("mTitle", "Awesome book");

QuerySet<Book> books = Book.objects(getApplicationContext()).filter(filter);

contains

One really neat filter function, if you for example implement a keyword search is the contains method. This one will take a String and find any instance of the model, where the field value contains this string. Be careful here! This simply translates into a field_name LIKE "%value%" on the SQLite database and SQLite doesn't care about case at all, when it comes to LIKE statements. So in any case the resulting search will be case insensitive!

In this example we will search for "some book" (get the joke?).

Filter filter = new Filter();
filter.contains("mTitle", "some book");

QuerySet<Book> books = Book.objects(getApplicationContext()).filter(filter);

Spanning relationships

Up till now the filter functions are cool, but nothing special actually. Why? Because writing queries for these on your own wouldn't be too hard. This changes, if we take all the relations into account. What for example happens, if you want all books, that an author called "Dan Brown" wrote? This is the point, where you write your first join. It get's even more complicated, if you for example have suppliers, that have branches, that sell products and you only want suppliers, where some branches sell a product with a specific name. Luckily androrm helps you here. For now, we go back to our example. So, how do we get all books, that have an author who is called "Dan Brown"?

Filter filter = new Filter();
filter.is("mAuthor__mName", "Dan Brown");

QuerySet<Book> books = Book.objects(getApplicationContext()).filter(filter);

Suprised, how few changes we had to make? If you want to traverse relationships, you can do this putting two underscores between the fieldnames. This mechanism scales for any number of fields, as long as the model classes are connected with the correct relations. More information on relationship fields can be found in the relational field section.

Implicit field types

Ok, what if you are more interested in getting all books, that are associated with a certain author? You can easily achieve this functionality, by just adding a OneToManyField to the Author class.

public class Author extends Model {
    
    public static final QuerySet<Author> objects(Context context) {
        return objects(context, Author.class);
    }
    
    protected CharField mName;
    protected OneToManyField<Author, Book> mBooks;
    
    public Author() {
        super();
        
        mName = new CharField(80);
        mBooks = new OneToManyField<Author, Book>(Author.class, Book.class);
    }
}

This kind of field expects a ForeignKeyField on the target class, that points back to it. Thus androrm knows how to handle it. So, if you now want all authors, that wrote an awesome book, you do it like this:

Filter filter = new Filter();
filter.is("mBooks__mTitle", "Awesome book");

QuerySet<Author> authors = Author.objects(getApplicationContext()).filter(filter);

Ordering

When you call orderBy() on a query set, an instance of the OrderBy class will be created. The columns you have specified will then be handed to it's constructor. To learn, how to get the ordering right take 5 minutes and read the following section.

OrderBy Since version 0.2

The OrderBy class can be used to order the output of queries. The constructor takes an arbitrary number of column names.

OrderBy ( String... columnNames )

Constructor, that will set-up the class. See the description for the columnNames parameter for further information.

columnNames

All colum names after which the output should be ordered. You can influence the ordering with a preceding + or -. The + operator will force a ascending ordering and the - will force an descending ordering. If neither + nor - is supplied, the default case is ascending.


In the next example, we will get all books of an author, ordered by their title. Also we don't want them ordered ascending, but rather descending.

// Class definition for author with method to retrieve books
public class Author extends Model {

    public static final QuerySet<Author> objects(Context context) {
        return objects(context, Author.class);
    }

    protected CharField mName;
    protected OneToManyField<Author, Book> mBooks;

    public Author() {
        mBooks = new OneToManyField<Author, Book>(Author.class, Book.class);
        mName = new CharField();
    }

    public QuerySet<Book> getBooks(Context context) {
        return mBooks.get(context, this);
    }

    public void setName(String name) {
        mName.set(name);
    }

}

// get a context instance
Context context = getApplicationContext();

// Get the author with id = 1
Author author = Author.objects(context).get(1);

// Querying for books
QuerySet<Book> books = author.getBooks(context).all().orderBy("-mTitle");

for(Book book : books) {
    ...
}

You could have also first applied a filter to the books query set, that would restrict the output further. Note that with query sets you have full power over the query at any time.


Transactions

Androrm is designed in a way that each time you call save on one of your model classes a discrete connection to the database is opened up, the necessary actions are performed, and the connection is closed again. This mechanism prevents you from accidently corrupting your data. The downside of this approach comes to light if you try to save or modify large amounts of data. You will most certainly feel the overhead produced by handling all the different connections. But fortunately there is a solution to that problem.

With transaction you can perform an arbitrary number of operations on the database and androrm will only open and close the connection once. Also if something goes wrong in between all your actions are rolled back automatically in order to leave your data in a consistent state. The following example will show you how to use transactions.

Context ctx = getApplicationContext();

// Do _NOT_ instantiate with default constructor! Transactions only work if
// DatabaseAdapter is used as a singleton.
DatabaseAdapter adapter = DatabaseAdapter.getInstance(ctx);
adapter.beginTransaction();

Author brown = new Author();
brown.setName("Dan Brown");
brown.save(ctx);

Author grisham = new Author();
grisham.setName("John Grisham");
grisham.save(ctx);

adapter.commitTransaction();

As you can see in the above example androrm somehow needs to know that you want to perform a complex transaction. You inform androrm about your plans by calling beginTransaction on a DatabaseAdapter instance.

Behold! In order for transactions to work you need to use the DatabaseAdapter class as a singleton. In order to do so only retrieve an instance of it with the getInstance method.

After you called beginTransaction everything that follows will be wrapped into a transaction. That means that if some error occurs during any of the operations you perform, all of them will be rolled back! It is also very important that you commit each transaction by calling commitTransaction. If you monitor your running code and wish to rollback the transaction manually, you can do so by calling rollbackTransaction on the DatabaseAdapter instance.


In this section you learned, how to use the objects method, to construct complex queries and to limit the results by utilizing the Filter class. You now also know, that you can easily span relationships, by only providing the correct field names. Now go and try it yourself and if you find any bugs, report them!

androrm on github

Django is a registered trademark of the Django Foundation. Android is a registered trademark of Google Inc.