Gorm eager loading joins with multiple relationships

Question

I have this data design: A Transaction belongs to a Mandate, which in turn belongs to a Client. Or expressed with structs:

type Transaction struct { 
    shared.Transaction // transaction data
    MandateID   string
    Mandate     *Mandate
}

type Mandate struct {
    shared.Mandate // mandate data
    ClientID      string
    Client        *Client
    Transactions  []Transaction
}

type Client struct {
    shared.Client // client data
    Mandates   []Mandate

}

I've been trying to preload transactions along with their mandate and client data using joins preloading. Where it is noted that this preloading only works for "has-one" or "belongs-to" type relationships.

This works nicely if I just want the Transaction and Mandate data:

var trx []rdb.Transaction
err = t.db.
    Joins("Mandate").
    Find(&trx, "batch_id = ?", batchID).Error

However as soon as I try to also load the clients, then gorm generates invalid SQL. Code:

var trx []rdb.Transaction
err = t.db.
    Joins("Mandate").
    Joins("Mandate.Client").
    Find(&trx, "batch_id = ?", batchID).Error

I used to use normal preloading before, but the multiple queries that Preload kicks off started to become a serious performance concern. Writing the entire query in SQL and scanning the result is also an option, but that defeats the purpose of using an ORM in the first place.

How can I use gorm to fetch the transactions with their mandate and client data in a single query?

Answer 1

To load nested associations, like Mandate.Client, you need to use the Preload function.

var trx []rdb.Transaction
err = t.db.
    Preload("Mandate.Client").
    Find(&trx, "batch_id = ?", batchID).Error

Regarding the performance issue, yes, the Preload function does execute multiple queries. However, if you look at the queries, you can see that they are optimized as much as they can be. The queries should look something like this:

SELECT * FROM clients WHERE id IN (23,43,12,3,2,73,121,1,9,21,39);

SELECT * FROM mandates WHERE id IN (1,2,3,4,5,6,21,43,54);

It does scan the entire tables, but you can see that it tries to load a very specific set of data. I don't think this ORM can help much more than this. The fact is that if you have a lot of transactions in your batch, and you want all details loaded immediately, you will have some performance hits.

To improve performance with your approach, you can always try to introduce paging by using Offset and Limit functions to limit the number of transactions that are returned. Or, use a raw SQL query to return a denormalized result and use for loops and Scan to read the rows.