Is it possible to do something like this with the ...

# exposed

[Kevin]

Is it possible to do something like this with the DSL?

select date, message.ROWID, id, text, filename, is_from_me, mime_type
from message
       left join handle on message.handle_id = handle.ROWID
       inner join chat_message_join c on message.ROWID = c.message_id and c.chat_id = 259
       left join message_attachment_join maj on message.ROWID = maj.message_id
       left join attachment a on maj.attachment_id = a.ROWID;

I’m pretty new to exposed and SQL in general, so I’m not sure if this is even the right way to do things in SQL.

[tapac]

Exposed doesn't use ROWID and I think that it is bad practice to link rows via ROWID instead of primary key value.
But it's still possible to express your query with Exposed DSL.

[Kevin]

Thank you! I was able to get it to work.

In the database I’m working with, the ROWID is the name of a column and is also the primary key most of the tables. So the 5th actual row could have a ROWID of 10 if some rows have been deleted. (It’s the chat.db for Messages on Mac).

I was mostly confused about how to chain all those joins together, but knowing it was possible let me keep trying to figure it out. I came across your comment [here](https://github.com/JetBrains/Exposed/issues/177#issuecomment-335939897) and was able to build on that. It really seems straight forward looking at it now.

Messages
    .leftJoin(Handle, {Messages.handleID}, {Handle.id})
    .innerJoin(ChatMessageJoins, { Messages.id }, { ChatMessageJoins.messageID })
    .leftJoin(AttachmentMessageJoin, {Messages.id}, {AttachmentMessageJoin.messageID})
    .leftJoin(Attachments, {AttachmentMessageJoin.attachmentID}, {Attachments.id})
    .selectAll()
    .andWhere { ChatMessageJoins.chatID eq chatID }

[tapac]

If you define proper mapping it will be able to simplify your code. Somthing like

abstract class RowIdTable(name: String) : IdTable<Int>(name) {
    override val id = integer("ROWID").entityId()
}

object Messages : RowIdTable("Messages") {
    val handleId = optReference("handleID", Handle)
}

object Handle : RowIdTable("Handle")

object ChatMessageJoins : RowIdTable("ChatMessageJoins") {
    val chatID = integer("chatID")
}

    Messages
        .leftJoin(Handle)
        .innerJoin(ChatMessageJoins)
        .select{ ChatMessageJoins.chatID eq chatID }

[Kevin]

thank you, that is much cleaner! That is my first time seeing optReference.