Order by attribute of a foreign object in ORMLite

Question

How can I build a query in ORMLite so that I can use the orderBy function (using either the source string or the parameterized one), referring to an attribute of a different object than the one I am creating the dao query from? My query is structured this way:

// Inner query for performances QueryBuilder<Performance, String> performancesQB = performanceDao.queryBuilder(); performancesQB.selectColumns("performance_id"); SelectArg performanceSelectArg = new SelectArg(); performancesQB.where().lt("date", performanceSelectArg); // Outer query for Order objects, where the id matches in the performance_id // from the inner query QueryBuilder<Order, String> ordersQB = orderDao.queryBuilder(); ordersQB.where().isNull("user_id").and().in("performance_id", performancesQB); ordersQB.orderByRaw("performances.date DESC"); pastOrdersQuery = ordersQB.prepare(); 

And the exception that I get whenever I try to execute this request:

 android.database.sqlite.SQLiteException: no such column: performances.date:, while compiling: SELECT * FROM `orders` WHERE (`user_id` IS NULL AND `performance_id` IN (SELECT `performance_id` FROM `performances` WHERE `date` < ? ) ) ORDER BY performances.date DESC 

The only solution I see here is to write the original query using a JOIN instead of a nested select. Could this be a good solution?

Answer 1

ORMLite now supports simple JOIN queries. Here are the related documents:

http://ormlite.com/docs/join-queries

So your query will look something like this:

 QueryBuilder<Performance, String> performancesQB = performanceDao.queryBuilder(); SelectArg performanceSelectArg = new SelectArg(); performancesQB.where().lt("date", performanceSelectArg); performancesQB.orderBy("date", false); // query for Order objects, where the id matches QueryBuilder<Order, String> ordersQB = orderDao.queryBuilder(); ordersQB.join(performancesQB).where().isNull("user_id"); pastOrdersQuery = ordersQB.prepare();