UserId is a foreign key in UserHasType table . Now if we want to get UserHasTypeTable data as child data in UsersTable we need to point it as val userType by UserHasTypeEntity backReferencedOn UserHasTypeTable.user_id
object UsersTable : IdTable<String>("users") {
override val id: Column<EntityID<String>> = text("id").uniqueIndex().entityId()
val user_name = text("user_name")
val email = text("email")
val password = text("password")
val mobile_number = text("mobile_number").nullable()
val email_verified_at = text("email_verified_at").nullable() // so far unkmown
val remember_token = text("remember_token").nullable()
val verification_code = text("verification_code").nullable() // verification_code
val created_at = datetime("created_at").defaultExpression(CurrentDateTime()) // UTC time
val updated_at = datetime("updated_at").nullable()
val is_verified = text("is_verified").nullable() // email verified by validation code
override val primaryKey = PrimaryKey(id)
}
class UsersEntity(id: EntityID<String>) : Entity<String>(id) {
companion object : EntityClass<String, UsersEntity>(UsersTable)
var user_name by UsersTable.user_name
var email by UsersTable.email
var password by UsersTable.password
var mobile_number by UsersTable.mobile_number
var email_verified_at by UsersTable.email_verified_at
var remember_token by UsersTable.remember_token
var verification_code by UsersTable.verification_code
var created_at by UsersTable.created_at
var updated_at by UsersTable.updated_at
var is_verified by UsersTable.is_verified
val userType by UserHasTypeEntity backReferencedOn UserHasTypeTable.user_id
fun userResponse() = UsersResponse(
id.value,
user_name,
email,
mobile_number,
email_verified_at,
remember_token,
is_verified,
userType.userHasTypeResponse()
)
}
data class UsersResponse(
val id: String,
val userName: String,
val email: String,
val mobileNumber: String?,
val emailVerifiedAt: String?,
val rememberToken: String?,
val isVerified: String?,
var userType: UserHasType
)UserHasTypeTable and UserHasTypeEntity
object UserHasTypeTable : IdTable<String>("user_has_type") {
override val id: Column<EntityID<String>> = text("id").uniqueIndex().entityId()
val user_id = reference("user_id", UsersTable.id)
val user_type_id = text("user_type_id")
val created_at = text("created_at")
val updated_at = text("updated_at")
override val primaryKey = PrimaryKey(id)
}
class UserHasTypeEntity(id: EntityID<String>) : Entity<String>(id) {
companion object : EntityClass<String, UserHasTypeEntity>(UserHasTypeTable)
var user_id by UserHasTypeTable.user_id
var user_type_id by UserHasTypeTable.user_type_id
var created_at by UserHasTypeTable.created_at
var updated_at by UserHasTypeTable.updated_at
//var users by UsersEntity referencedOn UserHasTypeTable.user_id
fun userHasTypeResponse() = UserHasType(id.toString(), user_type_id)
}
data class UserHasType(
val id: String, val user_type_id: String
)User controller
class UserController {
fun login(loginBody: LoginBody) = transaction {
val query = UsersTable.leftJoin(UserHasTypeTable).select { UsersTable.email eq loginBody.email }
val result = UsersEntity.wrapRows(query).first() if(loginBody.password == result.password)
return@transaction result.userResponse()
else
null
}
}UserRoute
fun Route.userRoute(userController: UserController) {
post("login") {
val loginBody = call.receive<LoginBody>()
val db = userController.login(loginBody)
db.let {
call.respond(JsonResponse.success(loginResponse,HttpStatusCode.OK))
}
}
}Ref: https://piashcse.medium.com/get-child-data-in-parent-table-by-backreferencedon-in-kotlin-exposed-ktor-part-3-80bb14675871