Exposed: Order by sub-query

[Edvinas01]

I have the following query which returns results from table_1 and orders them based on how many rows are owned by table_1 in table_2:

SELECT table_1.id
FROM table_1
ORDER BY (
  SELECT count(*)
  FROM table_2
  WHERE table_1.id = table_2.table_1_id
) DESC

Is it possible to construct such a query?

For the time being, I'm using this hack:

fun Query.toExpression(): Expression<Any> = QueryExpressionAdapter(this)

class QueryExpressionAdapter(val query: Query) : Expression<Any>() {
    override fun toSQL(queryBuilder: QueryBuilder): String {
        return "(${query.prepareSQL(queryBuilder)})"
    }
}

Table1
    .selectAll()
    .orderBy(Table2
        .slice(Table2.id.count())
        .select {
            Table1.id.eq(Table2.table1Id)
        }
        .toExpression() to false
    )

[Tapac]

There is wrapAsExpression function for that purpose.

val orderByExp = wrapAsExpression(Table2
        .slice(Table2.id.count())
        .select {
            Table1.id.eq(Table2.table1Id)
        })

Table1
    .selectAll()
    .orderBy(orderByExp, SortOrder.DESC)

[Edvinas01]

@Tapac just the right thing! I guess I should close the issue? Maybe a link should be added to https://github.com/JetBrains/Exposed/wiki/FAQ regarding the documentation part? Similarly as with https://github.com/JetBrains/Exposed/wiki/FAQ#q-how-can-i-implement-nested-queries

[Tapac]

I suppose wrapAsExpression deserves a section at DAO page of the wiki. I'll close an issue after update of the wiki.

[Edvinas01]

@Tapac I've added an example at:
https://github.com/JetBrains/Exposed/wiki/DAO#use-queries-as-expressions

Verified with a small test under org.jetbrains.exposed.sql.tests.shared.DMLTestsData:

@Test
fun testExpressions() {
    withCitiesAndUsers { cities, users, userData ->
        val expression = wrapAsExpression<Int>(users
                .slice(users.id.count())
                .select {
                    cities.id eq users.cityId
                })

        val result = cities
            .selectAll()
            .orderBy(expression, SortOrder.DESC)
            .toList()

        // Munich - 2 users
        // St. Petersburg - 1 user
        // Prague - 0 users
        println(result)
    }
}

[Tapac]

Thank you very much for updating wiki!
I will add your test to a master branch.