SELECT COUNT

[Hell-Hope]

SELECT
user.id
(select count(*) from user_role where user_role.user_id = user.id)
FROM user

[Tapac]

Something like below should work:

val subquery = UserRoleTable.slice(UserRoleTable.id.count()).select { UserRoleTable.userId eq UserTable.id }
val countExpression = wrapAsExpression<Long>(subquery)
UserTable.slice(UserTable.id, countExpression).selectAll()

[mjjabarullah]

SELECT a.message_id, a.sent_from, a.sent_to, a.message, a.seen,
users.username, users.user_image,
b.unread_messages
FROM messages a
INNER JOIN
(SELECT sent_from,
max(message_id) AS maxid,
COUNT(CASE WHEN seen = 'NO' THEN 1 ELSE NULL END) AS unread_messages
FROM messages
GROUP BY sent_from, sent_to) AS b ON a.message_id = b.maxid
INNER JOIN users ON users.user_id = a.sent_from
WHERE a.sent_to = 5

how to write this query using this framework