Nested structures invoke missing destination name error

[webRat]

I reached out to @jmoiron on Gopher Slack recently. He asked me to open up an issue.

I presented an issue that this code is not working as expected: http://play.golang.org/p/yvwh5eoiLZ

If I change

type User struct {
    ID         string `db:"id"`
    Name       string `db:"name"`
    ModifiedBy ModifiedBy
}

to:

type User struct {
    ID         string `db:"id"`
    Name       string `db:"name"`
    ModifiedBy
}

It works, but this is an embedded struct, not a nested one. I'm trying to get nested structs up and running.

Generic mysql table:

CREATE TABLE `tempuser` (
  `id` char(36) NOT NULL,
  `name` varchar(200) NOT NULL,
  `last_modified_by` char(36) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Generic data:

INSERT INTO `tempuser` (`id`, `name`, `last_modified_by`) VALUES 
    ('ff7c32f3-dcb4-11e5-bc44-0242ac110002','Todd Rafferty','ff7c32f3-dcb4-11e5-bc44-0242ac110002'),
    ('2039b523-dcb5-11e5-bc44-0242ac110002','Jason Morion','ff7c32f3-dcb4-11e5-bc44-0242ac110002');

Thanks. :+1:

[glaslos]

I just run into the same issue. Without looking at the code, I assume sqlx gets a flat list of rows and then checks the root of the destination struct for those. I assume they could do something similar to named prepared statements :modified_by.name in a select SELECT name as :modified_by.name

[chespinoza]

I'm having the same issue on Postgresql and so far the workaround has been refer the embedded struct without a name.

[Robbie-Perry]

Any solution to this now? The only workaround that works for me is making the nested struct anonymous, without a name. I can't use this in practice, since I am using the same structs as gorm models, and it requires the nested structs be named in order to migrate the database.

[nicoleta]

Having a nested struct on only the first level would be ok if you use AS in your queries and prefix the columns with the correct tag. See the example below.

type Address struct {
	ID          int64       `db:"id"`
	Street      string      `db:"street"`
	Number      int64       `db:"number"`
}

type Person struct {
	ID      int64   `db:"id"`
	Name    string  `db:"name"`
	Address Address `db:"address"` // any db tag would be ok as long as you use the same one in the query
}

query := `SELECT p.id, p.name,
              address.id AS "address.id", address.street AS "address.street"	 
          FROM person as p
          INNER JOIN address AS address ON p.address_id=address.id
          WHERE p.id=$1`
var p = &Person{}
result := sqlxDB.QueryRowxContext(ctx, query, 1)
err := result.StructScan(p) // -> ok

My problem now is with nested structs on more than one level:

type GeoLocation struct {
	ID   int64   `db:"id"`
	Long float64 `db:"long"`
	Lat  float64 `db:"lat"`
}

type Address struct {
	ID          int64       `db:"id"`
	Street      string      `db:"street"`
	Number      int64       `db:"number"`
	GeoLocation GeoLocation `db:"geolocation"`
}

type Person struct {
	ID      int64   `db:"id"`
	Name    string  `db:"name"`
	Address Address `db:"address"`
}
query := `SELECT p.id, p.name,
                 address.id as "address.id", address.street as "address.street",
		 geolocation.id as "geolocation.id", geolocation.long as "geolocation.long",
                 geolocation.lat as "geolocation.lat"	 
	FROM person as p
	INNER JOIN address as address on p.address_id=address.id
	INNER JOIN geo_location as geolocation on address.geo_location_id=geolocation.id
	WHERE p.id=$1`
var p = &Person{}
result := sqlxDB.QueryRowxContext(ctx, query, 1)
err := result.StructScan(p) // -> "missing destination name geolocation.id in *Person"

It makes sense, there is no geolocation attribute on the main Person struct... I couldn't find any workaround for this. Has anyone?

[spwats]

@nicoleta I believe I've found a workaround. You should be able to achieve additional levels of nesting by continuing to prepend your table names with their "parent" table names. So in your code, instead of

geolocation.id as "geolocation.id", geolocation.long as "geolocation.long", geolocation.lat as "geolocation.lat"

try

geolocation.id as "address.geolocation.id", address.geolocation.long as "adddress.geolocation.long", geolocation.lat as "address.geolocation.lat"

[psahni]

Facing the same issue. Its disgusting and waste of effort