I want to list entities ordered by the sum of a column from another table in descending order.
entities
---------------------------------------
| id | name | created_at | updated_at |
---------------------------------------
| 1 | foo | ...
| 2 | bar | ...
other_table
----------------------
| entity_id | amount |
| 1 | 35 |
| 1 | 1 |
| 2 | 125 |
result
---------------------------------------
| id | name | created_at | updated_at |
---------------------------------------
| 2 | bar | ...
| 1 | foo | ...
Unfortunately, this isn't possible to pass a subquery to the desc() method of Ebean.
String sql = "SELECT SUM(amount)";
sql += " FROM other_table";
sql += " WHERE entities.id = other_table.entity_id";
RawSql rawSql = RawSqlBuilder.parse(sql).create();
Query<OtherTable> subQuery = Ebean.createQuery(OtherTable.class)
.setRawSql(rawSql);
List<Entity> list = Ebean.find(Entity.class)
.query().where()
.orderBy().desc(subQuery) // <- should by allowed
.setMaxRows(8)
.findList();
This is very common to perform this kind of query so I guess there is an obvious way to do that but I cannot find it.