Gorm gets all data from table based on condition of nested table

php Xiaobian Xigua introduces you to an efficient data acquisition method - Gorm. Gorm is a Golang-based ORM library that makes it easy to interact with databases. When using Gorm, we can get all data from the table based on the conditions of the nested table without tedious manual queries. This method not only simplifies the code, but also improves query efficiency, allowing developers to operate data more conveniently. Both beginners and experienced developers can easily implement data acquisition functions by using Gorm.

Question content

I have a table with the following golang structure:

order {
  id
  transactionid
  transaction
}

transaction {
  id
  profileid
  profile
}

profile {
  id
  accountid
  account
}

How to get all orders with account ID conditions through gorm? I've tried:

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

But it doesn't work.

Solution

This solution should work based on the structure definition you provide. 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.

Structure definition

Nothing has changed here. When declaring a structure, be sure to understand the gorm conventions as gorm will create relationships, foreign keys, and constraints based on this.

Prepare database

Here you can find the connection to postgres, the automatic migration command for synchronizing tables, and the insertion of some dummy data.

Inquire

Here, we use many methods provided by go's gorm package. Let's review them in a short list:

• debug: It prints the raw sql query to the console. Very useful when dealing with complex queries
• preload: Load related entities but do not include them in the final query generated by gorm
• joins: It specifies which tables must be referenced in the join clause. Using joins we add this clause to the query.
• first: It is used both to get just one record and also to specify some filters, like in our example (e.g. a.id = ?).

If this solves your problem, please let me know, thank you!

The above is the detailed content of Gorm gets all data from table based on condition of nested table. For more information, please follow other related articles on the PHP Chinese website!