How to use DSL exists function checking column fro...

# exposed

[Md Sadique Inam]

How to use DSL exists function checking column from another table like
SELECT FROM users WHERE EXISTS (SELECT FROM emails WHERE emails.userid = users.id and emails.email = 'email')

[Luis Arcos]

DSL Example of Exists clause.kt

DSL Example of Exists clause.kt

[Md Sadique Inam]

@Luis Arcos

UserEntity.find { or exists(Emails.select(Emails.id).where { (Emails.email eq uid) and (Emails.userId eq Users.id) }) }
            .firstOrNull()

Emails.userId eq Users.id eq gives this error
Type argument for a type parameter V can't be inferred because it has incompatible upper bounds: UUID, Comparable<UUID>, EntityID<UUID> (multiple incompatible classes: UUID, EntityID<UUID>)

[Luis Arcos]

(Emails.email eq uid) this statement doesn't look right to me

[Md Sadique Inam]

uid is the variable that holds email address as string this uid is working correct

[Luis Arcos]

ah ok. so that checks out. Are your ID fields UUIDs?

[Md Sadique Inam]

yes

[Md Sadique Inam]

Emails.userId: Column<EntityID<UUID>> and Users.id: Column<EntityID<UUID>>

[Luis Arcos]

I haven't used Exposed DAO. I only use SQL DSL but I can't imagine it is that different. I do recall there was a bug w/ an earlier version of Exposed concerning EntityId fields. are you on the latest version?

[Md Sadique Inam]

my current version is 0.53.0 and the Users table is

object Users : UUIDTable() {
    val name = varchar("name", length = 250)
    val username = varchar("username", length = 250).uniqueIndex()
    val password = varchar("password", length = 250)
    val createdAt = datetime("created_at")
    val updatedAt = datetime("updated_at").nullable()
}

[Md Sadique Inam]

and emails table

object Emails : UUIDTable() {
    val email = varchar("email", 255).uniqueIndex()
    val userId = reference("user_id", Users)
    val isPrimary = bool("is_primary")
    val createdAt = datetime("created_at")
    val updatedAt = datetime("updated_at").nullable()
}

[Luis Arcos]

should val userId = reference("user_id", Users) be val userId = reference("id", Users)?

[Chantal Loncle]

Hi @Md Sadique Inam Am I correct that you're using the latest version of Exposed with Kotlin 2.0? And that the IDE shows that type inference error on eq but the code still compiles and runs without exception? If so, this is a bug and a fix for it has already been merged, which will be available with the next end-of-month release when we update the Kotlin version.

The quick test I ran using your example tables does compile and run despite the error showing, but if you want to get rid of the IDE highlighting error, enabling K2 mode does that.

[Md Sadique Inam]

Thanks @Chantal Loncle and also thanks to @Luis Arcos for giving your time