sqlx
sqlx copied to clipboard
jmoiron
better tools for join queries
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.
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.
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.
Ya, that would be awesome. It would make it far easier to add relations to models without having a ton of composite structs.
@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: https://github.com/jmoiron/sqlx/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.
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.
I have a working implementation here: https://github.com/jmoiron/sqlx/compare/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.
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.
I wrote another branch that is optimized for cached types, as well using points: https://github.com/jmoiron/sqlx/compare/master...pkieltyka:ptrs
Let's ship this thing! check out: https://github.com/pkieltyka/sqlx/commit/eaab23ebcbb10596ecb51da75568fb729ce4247d
Hi, this is a great addition to this cool library. May I know when will this be merged in the master branch? Can't wait to use this. :)
hmm.. I feel we should close this since and open more specific ideas for how to improve Join further. @nubbel most of the functionality discussed above has been merged for a few months now
Damn this looks like a cool feature. Sad I didn't find this earlier. But I guess that inserting is not covered, right?
@nubbel just in case you or someone else is looking for docs on this, check out the example checked in or the tests
Hey @pkieltyka , this is very cool and almost exactly what I need. In its present form it seems to work well for scanning a SELECT into a struct, but I found that to use a name with a '.' in it in BindNamed or ExecNamed I needed to tweak compileNamedQuery() in named.go. In its present form, '.' is not considered part of a name (e.g. in :place.name).
The tweak was add the change in bold below:
// if we're in a name, and this is an allowed character, continue
} else if inName && (unicode.IsOneOf(allowedBindRunes, rune(b)) || b == '_'|| b == '.') && i != last {
this sounds like a big improvement, it's a game changer, is it going to be implemented soon?
@eslammostafa my proposal was merged a long time ago, so I believe it should work today with sqlx (it did at some point). Another option is https://github.com/upper/db which supports these kinds of joins as well.
i just tested it, it's working like a charm, thank you. i don't know how come i didn't saw that in the docs.
Is this functionality still in master? @pkieltyka suggested on 2016-11-02 that it has been merged in some point in time. I tried using the 'prefix' tag without success, like this:
type PP struct {
Person `db:"person,prefix=person."`
Place `db:",prefix=place."`
}
But aliased columns from the SQL query are throwing 'missing destination name'.
I also did search this package's source code for implementation of a 'prefix' tag, or unit tests, and found nothing (I could have not been searching correctly, though). Maybe someone could give some clarification on this?
@bpizzi may be you need to look at it https://snippets.aktagon.com/snippets/757-how-to-join-two-tables-with-jmoiron-sqlx
prefix was just a proposal as far as I know. The big problem I have is the lack of applying what's described in the above blog post, to be applicable in a generic way, e.g. you need to name every field column by hand:
SELECT sessions.*,
users.id "user.id",
users.username "user.username",
users.display_name "user.display_name"
...
FROM sessions
JOIN users ON sessions.user_id = users.id
It would be much better, if the following would be possible:
SELECT sessions.*, users.*
FROM sessions
JOIN users ON sessions.user_id = users.id
Which is obviously not possible because sqlx wouldn't know which column belongs to which struct.
Hey thanks @ankitpatial, I came to the same conclusion on my own later on. Indeed a 'prefix' tag would be more intuitive (to me) than aliases columns, but in the end both solutions are not that much different.
@konradreiche yes, aliases a required and '*' is a dead end, but given a proper struct and a smart function, the sql query could be constructed without handwritten aliases, but it would be leaving manually crafted sql queries for a query builder. And both have their own pros and cons...
