1

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.

0

Browse other questions tagged or ask your own question.