better tools for join queries

[jmoiron]

Having thought over sqlx.In and the way it interacts with sqlx.BindNamed for a while, I've started to also explore other ways that sqlx can help people with their queries. Besides IN queries, one of the sticking points in SQL has been JOIN queries, especially when you've already "modeled" your tables appropriately.

@andybons asked me in IRC today if there was any better way of dealing with joins, and I said no, because the info you get from Columns() doesn't include fully qualified names. However, that's just for SELECT *; obviously, I think something like this should be possible:

type Person struct {
    Name string
}

type Place struct {
    Name string
}

// ...
func etc(db *sqlx.DB) {
    var pp struct {
        Person `db:"person,prefix=person."`
        Place  `db:",prefix=place."` // uses default Mapper
    }

    db.Get(&pp, `SELECT 
            person.name "person.name", 
            place.name "place.name" 
        FROM person, place LIMIT 1;`)
}

You could use _ or some such so you wouldn't have to quote the names in the aliases as well. If the repetition is bothersome, you could always:

func qualify(name string) string {
    return fmt.Sprintf(`%v "%v"`, name, name)
}

Or even a simple parser similar to how sqlx.In and sqlx.BindNamed work:

query := qualify("SELECT q:person.name, q:place.name FROM person, place;")

The actual missing piece is a way to tell sqlx that an embedded struct should provide a prefix for its members. I have to think this out a bit more; the straightforward uses of this seem pretty useful, but multiple levels of prefixed embeds sounds complex and confusing.

[tonyhb]

That would be pretty amazing. It would be really helpful when composing structs together.

Maybe we could use prefixes on only composed, anonymous structs like this? http://play.golang.org/p/r7--RLCoAc

That way prefix won't litter your standard struct definitions.

[jmoiron]

That's what I hinted at above with my anonymous struct typed pp which embedded two other structs. It is even possible to pretty succinctly define reflexive joins (joins of a table with itself), which would typically defeat embedding:

type Employee struct {
    Name string
    Id   int
    // BossId is an id into the employee table
    BossId sql.NullInt64
}

func etc(db *sqlx.DB) {
    type Boss Employee
    var employees []struct{
        Employee
        Boss     `db:",prefix=boss."`
    }
    db.Select(&employees, `SELECT employee.*, boss.id "boss.id", boss.name "boss.name" FROM employee JOIN employee AS boss ON employee.bossid = boss.id`)
}

Aside from the annoyance of having to alias everything in the query, I think this is actually pretty good.

[andybons]

@joyt who may have some input as well.

[tonyhb]

Ya, that would be awesome. It would make it far easier to add relations to models without having a ton of composite structs.

[pkieltyka]

@jmoiron I've been working towards this, the first part of the work like we talked about on irc is to expand on reflectx. I've done some work here: https://github.com/pkieltyka/sqlx and a compare: master...pkieltyka:master

.. I've left various comments in there, just as I was coding and thinking. The tests are all still passing btw.

[pkieltyka]

After more consideration, I feel reflectx needs to change even more. I thought I could keep the fieldMap as a map[] .. but, instead the fieldMap type should just go away entirely. Instead, it should be type fields []*fieldInfo. For a given struct, we need the object representation of the fields, their tags, options and if its embedded or not. This would also mean removing the FieldByName and FieldsByName because they'll just never work for all of these cases.

TraversalsByName would have to be reimplemented to scan the fieldInfo slice (type fields) to return the traversals for the field names.

ie.. in something like..

type Person struct {
    Name string
}
type Place struct {
    Name string
}
type PP struct {
    Person `db:"person,prefix=person."`
    Place  `db:",prefix=place."`
}

you couldnt just do mapper.FieldByName("name") .. that would clearly never work (in how its implemented now). If necessary, TraversalsByName could even be moved to sqlx. I know its more expensive to iterate through a slice a number of times, which is why the traversals should be cached for the type+columns.

This also makes reflectx much more reusable by other projects.

Another thing I suggest to look at is the way struct tags are handled in encoding/json. Look at encode.go, the typeFields() function.

these changes should be fully backwards compatible with other sqlx uses. I also suggest looking at how the ",inline" option is implemented in the mgo (mongo) driver -- its a simple addition that allows a columns to appear flat to the db driver, but with more structure in runtime, without requiring it to be embedded at runtime.

[pkieltyka]

I have a working implementation here: master...pkieltyka:master - it's been largely refactored, cleaned up with lots more tests since the weekend.

You'll notice there haven't been any changes to sqlx, just reflectx. It does however give sqlx a few more tricks in how structs can be defined and mapped to a field name list.

For example, look at these three tests: https://github.com/pkieltyka/sqlx/blob/master/reflectx/reflect_test.go#L121-L216

When a struct is embedded, without any tags, the fields of the embedded struct will be treated as inline. Though, if you specify a tag on the embed, it will calculate the named path to the field. This will also work for inline-structs in case you want to override the path at any level.

[pkieltyka]

Also, try uncommenting https://github.com/pkieltyka/sqlx/blob/master/reflectx/reflect_test.go#L246-L248 and run the tests to have a look at the fieldInfo structure to get a better idea of the structure.

[pkieltyka]

I wrote another branch that is optimized for cached types, as well using points: master...pkieltyka:ptrs

[pkieltyka]

Let's ship this thing! check out: pkieltyka@eaab23e