CODEWITHSUNDEEP
postgresqlgosqlx
kuzua
kuzua

Reputation: 217

How to scan into nested structs with sqlx?

Let's assume that I have two models,

    type Customer struct {
       Id      int     `json:"id" db:"id"`
       Name    string  `json:"name" db:"name"`
       Address Address `json:"adress"`
    }
    
    type Address struct {
       Street string `json:"street" db:"street"`
       City   string `json:"city" db:"city"`
    }

    // ...

    customer := models.Customer{}
    err := db.Get(&customer , `select * from users where id=$1 and name=$2`, id, name)

But this scan throws an error as: missing destination name street in *models.Customer

Am I doing something wrong? As you can see I already updated the db corresponding of the value. I doubled check so case sensitivity shouldn't be a problem. Or is it not possible using https://github.com/jmoiron/sqlx?

I can see it in the documentation but still couldn't figure out how to solve it. http://jmoiron.github.io/sqlx/#advancedScanning

The users table is declared as:

    CREATE TABLE `users` (
      `id` varchar(256) NOT NULL,
      `name` varchar(150) NOT NULL,
      `street` varchar(150) NOT NULL,
      `city` varchar(150) NOT NULL,
    )

Upvotes: 7

Views: 14246

Answers (4)

Kamil Dziedzic
Kamil Dziedzic

Reputation: 5032

You can do it by aliasing each column with fully qualified names of the entire path.

select street as "address.street" from users

source: your own question and here is better explanation

Upvotes: 1

ralonr
ralonr

Reputation: 438

Using Carta a lightweight library can help:

Sample Schema:

type Blog struct {
        Id     int    `db:"blog_id"`
        Title  string `db:"blog_title"`
        Posts  []Post
        Author Author
}
type Post struct {
        Id   int    `db:"posts_id"`
        Name string `db:"posts_name"`
}
type Author struct {
        Id       int    `db:"author_id"`
        Username string `db:"author_username"`
}

Query:

select
       id          as  blog_id,
       title       as  blog_title,
       P.id        as  posts_id,         
       P.name      as  posts_name,
       A.id        as  author_id,      
       A.username  as  author_username
from blog
       left outer join author A    on  blog.author_id = A.id
       left outer join post P      on  blog.id = P.blog_id

Using it:

// 1) Run your query
if rows, err = sqlDB.Query(blogQuery); err != nil {
    // error
}

// 2) Instantiate a slice(or struct) which you want to populate, Dummy example.
blogs := []Blog{}

// 3) Map the SQL rows to your slice
carta.Map(rows, &blogs)

Carta will map the SQL rows while keeping track of those relationships.

SQL Results:

rows:
blog_id | blog_title | posts_id | posts_name | author_id | author_username
1       | Foo        | 1        | Bar        | 1         | John
1       | Foo        | 2        | Baz        | 1         | John
2       | Egg        | 3        | Beacon     | 2         | Ed

Final Result:


blogs:
[{
    "blog_id": 1,
    "blog_title": "Foo",
    "author": {
        "author_id": 1,
        "author_username": "John"
    },
    "posts": [{
            "post_id": 1,
            "posts_name": "Bar"
        }, {
            "post_id": 2,
            "posts_name": "Baz"
        }]
}, {
    "blog_id": 2,
    "blog_title": "Egg",
    "author": {
        "author_id": 2,
        "author_username": "Ed"
    },
    "posts": [{
            "post_id": 3,
            "posts_name": "Beacon"
        }]
}]

Upvotes: 2

Adam
Adam

Reputation: 93

I had the same problem and came up with a slightly more elegant solution than @blackgreen's.

He's right, the easiest way is to embed the objects, but I do it in a temporary object instead of making the original messier.

You then add a function to convert your temp (flat) object into your real (nested) one.

    type Customer struct {
       Id      int     `json:"id" db:"id"`
       Name    string  `json:"name" db:"name"`
       Address Address `json:"adress"`
    }
    
    type Address struct {
       Street string `json:"street" db:"street"`
       City   string `json:"city" db:"city"`
    }
    
    type tempCustomer struct {
          Customer
          Address
    }

    func (c *tempCustomer) ToCustomer() Customer {
        customer := c.Customer
        customer.Address = c.Address
        return customer
    }

Now you can scan into tempCustomer and simply call tempCustomer.ToCustomer before you return. This keeps your JSON clean and doesn't require a custom scan function.

Upvotes: 2

blackgreen
blackgreen

Reputation: 45140

The very link you posted gives you an hint about how to do this:

StructScan is deceptively sophisticated. It supports embedded structs, and assigns to fields using the same precedence rules that Go uses for embedded attribute and method access

So given your DB schema, you can simply embed Address into Customer:

type Customer struct {
   Id     int    `json:"id" db:"id"`
   Name   string `json:"name" db:"name"`
   Address
}

In your original code, Address was a field with its own db tag. This is not correct, and by the way your schema has no address column at all. (it appears you edited it out of your code snippet)

By embedding the struct into Customer instead, Address fields including tags are promoted into Customer and sqlx will be able to populate them from your query result.

Warning: embedding the field will also flatten the output of any JSON marshalling. It will become:

{
    "id": 1,
    "name": "foo",
    "street": "bar",
    "city": "baz"
}

If you want to place street and city into a JSON address object as based on your original struct tags, the easiest way is probably to remap the DB struct to your original type.

You could also scan the query result into a map[string]interface{} but then you have to be careful about how Postgres data types are represented in Go.

Upvotes: 9

Related Questions