Order by attribute of foreign entity in ORMLite

android, foreign-keys, java, ormlite, sql-order-by

Solution

ORMLite now supports simple JOIN queries. Here the docs on the subject:

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

So your query would now look something like:

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();

Problem

How can I build a query in ORMLite so that I can use the orderBy function (using either the one with the raw string or the parametrized one) referencing an attribute of a different entity than the one of the dao I'm building the query from? My query is built like that: ``` // 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 I'm getting whenever I try to execute this query is: ``` 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 writing a raw query myself using a JOIN instead of a nested select. May this be a good solution?

Original source