Open
Description
Dialect
SQLite
Failing SQL
SELECT
coach.id, coach.firstName, coach.lastName, coach.badge, coach.email,
COALESCE(jamesTemplateDatabaseCoach.jamesTemplateDatabaseId, :jamesTemplateDatabaseId) AS jamesTemplateDatabaseId,
jamesTemplateDatabaseCoach.state
FROM coach
LEFT JOIN jamesTemplateDatabaseCoach
ON jamesTemplateDatabaseCoach.coachId = coach.id AND jamesTemplateDatabaseCoach.jamesTemplateDatabaseId = :jamesTemplateDatabaseId
WHERE coach.id NOT IN :botIds AND (coach.firstName LIKE :searchTerm OR coach.lastName LIKE :searchTerm)
GROUP BY 1
ORDER BY state DESC, firstName COLLATE NOCASE ASC, lastName COLLATE NOCASE ASC
;Description
Even though I'm using a LEFT JOIN I want the jamesTemplateDatabaseId property to be always present in the generated Kotlin Data class.
This is what I came up with (if there's another solution, please let me know!)
COALESCE(jamesTemplateDatabaseCoach.jamesTemplateDatabaseId, :jamesTemplateDatabaseId) AS jamesTemplateDatabaseId,this also works fine and the generated code also looks good:
return driver.executeQuery(null, """
|SELECT
| coach.id, coach.firstName, coach.lastName, coach.badge, coach.email,
| COALESCE(jamesTemplateDatabaseCoach.jamesTemplateDatabaseId, ?) AS jamesTemplateDatabaseId,
| jamesTemplateDatabaseCoach.state
| FROM coach
| LEFT JOIN jamesTemplateDatabaseCoach
| ON jamesTemplateDatabaseCoach.coachId = coach.id AND jamesTemplateDatabaseCoach.jamesTemplateDatabaseId ${ if (jamesTemplateDatabaseId == null) "IS" else "=" } ?
| WHERE coach.id NOT IN $botIdsIndexes AND (coach.firstName LIKE ? OR coach.lastName LIKE ?)
| GROUP BY 1
| ORDER BY state DESC, firstName COLLATE NOCASE ASC, lastName COLLATE NOCASE ASC
""".trimMargin(), 4 + botIds.size) {
bindString(1, jamesTemplateDatabaseId)
bindString(2, jamesTemplateDatabaseId)
botIds.forEachIndexed { index, botIds_ ->
bindString(index + 3, botIds_)
}
bindString(botIds.size + 3, searchTerm)
bindString(botIds.size + 4, searchTerm)
}The only problem is the generated class where jamesTemplateDatabaseId is null. It should be able to infer that the value is always present:
public data class SearchJamesTemplateDatabaseCoach(
public val id: String,
public val firstName: String,
public val lastName: String,
public val badge: Int?,
public val email: String?,
- public val jamesTemplateDatabaseId: String?,
+ public val jamesTemplateDatabaseId: String,
public val state: Int?
) {
public override fun toString(): String = """
|SearchJamesTemplateDatabaseCoach [
| id: $id
| firstName: $firstName
| lastName: $lastName
| badge: $badge
| email: $email
| jamesTemplateDatabaseId: $jamesTemplateDatabaseId
| state: $state
|]
""".trimMargin()
}