Kotlin Exposed - selecting based on sub-query count

Question

In my data model I have a very simple one-to-many relationship between challenges and it's whitelist items.

I am trying to select a challenge filtered by whitelist. Basically the challenge selection criteria is when the challenge is either does not have any entries in whitelist for itself or the whitelist matches by name.

This can be achieved with quite simple SQL query:

select c.* from challenge c, challenge_whitelist w where (c.id = w."challengeId" and w."userName" = 'testuser') or ((select count(*) where c.id = w."challengeId") = 0);

I am unable to translate it to Exposed though:

  // will not compile
  fun listAll(userName: String) {
    ExposedChallenge.wrapRows(
      ChallengeTable.innerJoin(ChallengeWhitelistTable)
        .slice(ChallengeTable.columns)
        .select((ChallengeWhitelistTable.userName eq userName) or (ChallengeTable.innerJoin(ChallengeWhitelistTable).selectAll().count() eq 0))
    ).toList()
  }

The userName check works correctly but ChallengeTable.innerJoin(ChallengeWhitelistTable).selectAll().count() eq 0) is not qualified as the valid expression (will not compile).

Note that the mappings are super-simple:

object ChallengeTable : IntIdTable() {
  val createdAt = datetime("createdAt")
}

class ExposedChallenge(id: EntityID<Int>) : IntEntity(id) {
  companion object : IntEntityClass<ExposedChallenge>(ChallengeTable)
  var createdAt by ChallengeTable.createdAt
  val whitelist by ExposedChallengeWhitelist referrersOn ChallengeWhitelistTable.challenge
}

object ChallengeWhitelistTable : IntIdTable(name = "challenge_whitelist") {
  var userName = varchar("userName", 50)
  var challengeId = integer("challengeId")
  val challenge = reference("challengeId", ChallengeTable).uniqueIndex()
}

class ExposedChallengeWhitelist(id: EntityID<Int>) : IntEntity(id) {
  companion object : IntEntityClass<ExposedChallengeWhitelist>(ChallengeWhitelistTable)

  val challengeId by ChallengeWhitelistTable.challengeId
  val challenge by ExposedChallenge referencedOn ChallengeWhitelistTable.challenge
}

Any help would be appreciated.

Answer 1

Your SQL query is invalid as you use select count(*) without from part. But it can be rewritten with Exposed DSL like:

ChallengeTable.leftJoin(ChallengeWhitelistTable).
    slice(ChallengeTable.columns).
    selectAll().
    groupBy(ChallengeTable.id, ChallengeWhitelistTable.userName).having {
    (ChallengeWhitelistTable.userName eq "testUser") or
    (ChallengeWhitelistTable.id.count() eq 0)
}

Another way is to use just left join:

ChallengeTable.leftJoin(ChallengeWhitelistTable).
    slice(ChallengeTable.columns).
    select {
        (ChallengeWhitelistTable.userName eq "testUser") or
        (ChallengeWhitelistTable.id.isNull())
    }