CODEWITHSUNDEEP
kotlinkotlin-exposed
alexs333
alexs333

Reputation: 12593

Kotlin Exposed - selecting based on sub-query count

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.

Upvotes: 1

Views: 4140

Answers (1)

Tapac
Tapac

Reputation: 2337

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())
    }

Upvotes: 2

Related Questions