Best practices

Currently, we have a lot of code like this one to query data inside our repositories:

database.suspendedTransaction {
    Items
        .selectAll()
        .where { Items.userId eq userId }
        .map {
            Items.toItem(
                it,
                fees = Fees
                    .selectAll()
                    .where { Fees.itemId eq it[Items.id].value }
                    .map(Fees::toFee)
            )
        }
}

This is an issue because when having a lot of items, it makes the code do N+1 queries (which is really slow sometimes)

So we're thinking about including everything inside one single query. For now, we've achieved something like this:

database.suspendedTransaction {
    val items = Items
        .selectAll()
        .where { Items.userId eq userId }
    val itemIds = items.map { it[Items.id].value }.toSet()
    val fees = Fees
        .selectAll()
        .where { Fees.itemId inList itemIds }
        .map(Fees::toFee)
    items.map {
        Items.toItem(
            it,
            fees = fees.filter { fee -> fee.itemId == it[Items.id].value }
        )
    }
}

This is better since we have 2 queries instead of N+1.

But:

  • I'm not sure about if it's the best way (simplest and most performant way) to do it
  • Maybe there is a way to do a simple query that returns everything at once

I though about using a left join, but it means we will return the data of the items a lot of times and might not be optimal at all (for example, if I have 10 fees for one item I will get 10 times the data of the item), because it makes the result way heavier. And when you have tables with multiple joins, you multiply the volume of data each time you add a left join (so you can get the same data duplicated hundred of times!)

database.suspendedTransaction {
    Items
        .leftJoin(Fees, Items.id, Fees.itemId)
        .selectAll()
        .where { Items.userId eq userId }
        .groupBy { it[Items.id].value }
        .map {
            Items.toItem(
                it.value.first(),
                fees = it.value.map(Fees::toFee) // This would need complex grouping if having multiple joins!
            )
        }
}

So what's the appropriate way to do this, reducing the number of queries and the volume of transmited data?

NOTE: Items::toItem and Fees::toFee are additional methods mapping a ResultRow to a simple data class, to later be used and serialized to JSON when responding to API requests.

Improve this question