Advanced Query usage

SquiDB's Query object supports almost the entire SQL select grammar. This page documents how to construct some queries that are more complex than those in the basic examples and how to use some of the more advanced features that Query supports.

Reusable arguments

In Android, a common pattern is to reuse SQL queries by inserting '?' in place of variable arguments in the query and passing a String[] of arguments to bind to those placeholders--see SQLiteDatabase.rawQuery.

SquiDB doesn't entirely approve of this. For one thing, it can be hard to remember which indexes in your arguments array correspond to which arguments in your select statement, and if the query ever changes you'll need to re-figure out the whole thing. For another thing, you have to convert numeric arguments to Strings to use Android's rawQuery, which can have unexpected bugs:

--This works ok
select * from table where _id = '1';

--abs() is just absolute value, but this returns no results
select * from table where abs(_id) = '1';

SquiDB helps you solve both of these problems. It manages the position of all the variable arguments in your query for you, so you never have to remember which '?' corresponds to which index, and binds all arguments using their native types when possible, rather than Strings.

But how do you actually change the arguments if you want to re-issue the same query with a changed argument, you ask? You can tell SquiDB that you may be changing an argument by constructing a Criterion using an AtomicReference, rather than the actual object you are comparing against:

AtomicReference<String> guid = new AtomicReference("guid1");
Query queryForGuid = Query.select().from(Person.TABLE).where(Person.GUID.eq(guid));

Person personWithGuid1 = database.fetchByQuery(Person.class, queryForGuid);

guid.set("guid2"); // Change the variable argument
Person personWithGuid2 = database.fetchByQuery(Person.class, queryForGuid); // Query the database again with the same query object

SquiDB will cache the SQL string underlying the query and efficiently detect only the changed arguments when re-issuing the query, so the overhead is minimal.

fork() and freeze()

By default, Query objects are mutable. That means, if you have a Query object and you call something like where(Criterion), the new criterion will be added to the existing object and the same object will be returned. Sometimes though, you want to make absolutely sure that other threads, classes, etc. don't modify your carefully constructed, publicly accessible Query objects. In these cases, you can call freeze() on your query object.

Query immutableQuery = Query.select().from(Person.TABLE).where(Person.AGE.gte(18)).freeze();

// This will be a new object, with the new criterion applied.
// The original query will be left in its original state
Query mutatedVersion = immutableQuery.where(Person.AGE.lt(70));

Similarly, if you want to get a copy of a query and know you're not modifying the original, you can use fork():

// Same as above, but still mutable
Query immutableQuery = Query.select().from(Person.TABLE).where(Person.AGE.gte(18));

// This will be a new object, with the new criterion applied.
// The original query will be left in its original state
Query mutatedVersion = immutableQuery.fork().where(Person.AGE.lt(70));

Subqueries

SquiDB supports using a subquery in place of a table or a view. If you do this with a ViewModel, SquiDB takes care of a lot of the complicated details for you--see Model specs for SQL views. However, if you do it without the model, there are a few caveats.

When you use a subquery in place of a table in the from clause, the subquery is acting like a virtual table, with its own name. That means you can't just freely use the property objects from the models in the subquery--those property objects are qualified by their original table names. In other words, you can't do this:

select people._id from (select people._id as _id from people) as subquery

In order to manually work with subqueries in SquiDB, you'll want to convert the Query into an instance of SubqueryTable, and then use qualifyField to modify the property objects that you are selecting. Fortunately, this is pretty easy:

Query subquery = Query.select(Person.ID, Person.NAME).from(Person.TABlE).where(Person.AGE.gte(18));
SubqueryTable subqueryTable = subquery.as("subquery");

LongProperty qualifiedId = subqueryTable.qualifyField(Person.ID);
StringProperty qualifiedName = subqueryTable.qualifyField(Person.NAME);

// select subquery._id, subquery.name from
//    (select person._id, person.name from person where person.age >= 18) as subquery
Query fromSubquery = Query.select(qualifiedId, qualifiedName).from(subqueryTable);

---

See also:

• SquiDB's query builder
• Other SQLite grammar objects