Gorm get all data from table with condition on nested table

Question

I have a table with golang struct like this:

Order {
  ID
  TransactionID
  Transaction
}

Transaction {
  ID
  ProfileID
  Profile
}

Profile {
  ID
  AccountID
  Account
}

How to get all the order with condition of account id with gorm? I have tried this:

 var orders []*Order
 res := r.db.
        Joins("Transaction").
        Preload("Transaction.Profile").
        Where("Transaction.Profile.account_id = 1").
        Find(&orders)

But it does not work.

Answer 1

This solution should work based on the structs definitions you provided. First, let me show the code and then I'll go through each step:

package main

import (
    "fmt"

    _ "github.com/lib/pq"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

type Order struct {
    Id            int
    TransactionId int
    Transaction   Transaction
}

type Transaction struct {
    Id        int
    ProfileId int
    Profile   Profile
}

type Profile struct {
    Id        int
    AccountId int
    Account   Account
}

type Account struct {
    Id int
}

func main() {
    dsn := "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic(err)
    }

    db.AutoMigrate(&Account{})
    db.AutoMigrate(&Profile{})
    db.AutoMigrate(&Transaction{})
    db.AutoMigrate(&Order{})

    db.Create(&Account{})
    db.Create(&Profile{AccountId: 1})
    db.Create(&Transaction{ProfileId: 1})
    db.Create(&Order{TransactionId: 1})

    // order + transaction + profile + account
    var order Order
    db.Debug().Preload("Transaction.Profile.Account").Joins("inner join transactions t on orders.transaction_id = t.id").Joins("inner join profiles p on p.id = t.profile_id").Joins("inner join accounts a on p.account_id = a.id").First(&order, "a.id = ?", 1)
    fmt.Println(order)
}

Let's take a closer look at the code.

Structs definitions

Nothing changed here. Be sure to know the GORM conventions when you declare the structs as GORM will create relations, foreign keys, and constraints based on this.

Preparing the database

Here, you can find the connection to Postgres, the auto-migrating commands to synchronize the tables, and the insert of some dummy data.

The query

Here, we used a lot of methods provided by the GORM package for Go. Let's recap them in a short list:

• Debug: it prints to the console the Raw SQL query. It's useful when dealing with complex queries
• Preload: loads the related entities but it doesn't include them in the final query produced by Gorm
• Joins: it specifies which tables have to be referenced in a JOIN clause. With the Joins we add the clause to the query.
• First: it's used both for fetching only one record and also for specifying some filter such as in our case (e.g. a.id = ?).

Let me know if this clarifies your issue, thanks!