CODEWITHSUNDEEP
kotlin-exposed
Zouyiu Ng
Zouyiu Ng

Reputation: 193

Select query as where condition in Exposed

How to use DSL method to generate a SELECT query in WHERE?

Example:

SELECT *
FROM table
WHERE id IN (
    SELECT MAX(id)
    FROM table
    GROUP BY name
);

Upvotes: 0

Views: 2923

Answers (1)

Zouyiu Ng
Zouyiu Ng

Reputation: 193

Update:

Now Exposed have added InSubQueryOp.

Table.run {
    select { id inSubQuery slice(id.max()).selectAll().groupBy(column) }
}

After searching I found this issue on GitHub. wrapAsExpression can be use to compose subquery.

  1. Copy EqOp and transform it to InOp

    class InOp(expr1: Expression<*>, expr2: Expression<*>) : ComparisonOp(expr1, expr2, "IN")

  2. Copy eq then transform it to inExpr extension function

    infix fun<T, S1: T?, S2: T?> Expression<in S1>.inExpr(other: Expression<in S2>) : 
Op<Boolean> = InOp(this, other)

  3. Use them

    Table.run {
    select { id inExpr wrapAsExpression(slice(id.max()).selectAll().groupBy(column)) }
}

Example output:

SELECT `user`.id, `user`.name, FROM `user` WHERE `user`.id IN (SELECT MAX(`user`.id) FROM `user` GROUP BY `user`.name)

Upvotes: 1

Related Questions