Hi guys! Assuming I have Users and Orders tables w...

# exposed

[Manasseh]

Hi guys! Assuming I have Users and Orders tables where a user can have multiple orders, how do I define this relationship in Exposed models? Also, how can I define a one-to-one relationship between a User and Profile table?

[Andromadus Naruto]

Using the DSL:

// Users

// Users
object UserTable : IntIdTable("users") {
    val uid = varchar("uid", 50).uniqueIndex()
    val email = varchar("email", 180).uniqueIndex()
    val password = varchar("password", 4096)
}

// Profile
object UserProfileTable : IntIdTable("user_profiles") {
    val full_name = varchar("full_name", 100)
    // relations
    val user_id = references("user_id", UserTable) // uses the ID field of UserTable as foreign key reference
    // val user_uid = references("user_uid", UserTable.uid) // if you want to use another field other than ID field as a foreign key reference
}

// Order
object OrderTable : IntIdTable("orders") {
    val order_no = varchar("order_no").uniqueIndex()
    val date_ordered = datetime("date_ordered")
    val status = varchar("status", 50)
    // relations
    val user_id = references("user_id", UserTable)
}

[Manasseh]

Thank you. So this seems like a one-to-many relationship for User-Profile and User-Order, no?

[Manasseh]

For context, my profile model looks like this:

object Profiles : IntIdTable() {
    val name: Column<String?> = varchar("name", 50).nullable()
    val bio: Column<String?> = varchar("bio", 50).nullable()
    val location: Column<String?> = varchar("location", 50).nullable()
    val website: Column<String?> = varchar("website", 100).nullable()
    val displayPicture: Column<ExposedBlob?> = blob("display_picture").nullable()
    val bannerPicture: Column<ExposedBlob?> = blob("banner_picture").nullable()
    val dateOfBirth: Column<LocalDate> = date("date_of_birth")
    val userId = reference("user_id", Users).uniqueIndex() // relation
}

[Andromadus Naruto]

Yes, it seems like. But looking at the las section of your code, it makes forces the User-Profile relationship to be a one-to-one

[Andromadus Naruto]

Using the DAO API can make it be more explicit but I personally prefer the DSL API due to the amount of control I have.

https://github.com/JetBrains/Exposed/wiki/DAO#referencing

[Manasseh]

Which part forces the User-Profile r/ship to be one-to-one? This part?
val user_id = references("user_id", UserTable)

[Andromadus Naruto]

This part from your code:

val userId = reference("user_id", Users).uniqueIndex() // relation

[Manasseh]

Oh, I was actually asking about your code when I said it looks like one-to-many r/ship

[Manasseh]

So you're saying my code is okay?

[Manasseh]

Because looking at your code, I was confused that one-to-one shared same syntax with one-to-many

[Andromadus Naruto]

Oh yes. There's no way, as far as I know, that you can specify a one-to-one relationship between two tables without making a unique constraint on the foreign key column.

[Manasseh]

I see... thanks for the help!