Reusing Existing Structs on Queries with JOINs

Select Generation with Joins

What if you want to JOIN multiple tables for which you already have structs defined? Would you need to create a new struct to represent the joined columns of the two tables?

No, we actually have this covered:

KSQL has a special feature for allowing the reuse of existing structs by using composition in an anonymous struct, and then generating the SELECT part of the query accordingly:

Querying a single joined row:

var row struct{
	User User `tablename:"u"`     // (here the tablename must match the aliased tablename in the query)
	Post Post `tablename:"posts"` // (if no alias is used you should use the actual name of the table)
}
err = db.QueryOne(ctx, &row, "FROM users as u JOIN posts ON u.id = posts.user_id WHERE u.id = ?", userID)
if err != nil {
	panic(err.Error())
}

Querying a page of joined rows:

var rows []struct{
	User User `tablename:"u"`
	Post Post `tablename:"p"`
}
err = db.Query(ctx, &rows,
	"FROM users as u JOIN posts as p ON u.id = p.user_id WHERE name = ? LIMIT ? OFFSET ?",
	"Cristina", limit, offset,
)
if err != nil {
	panic(err.Error())
}

Querying all the users, or any potentially big number of users, from the database (not usual, but supported):

err = db.QueryChunks(ctx, ksql.ChunkParser{
	Query:     "FROM users as u JOIN posts as p ON u.id = p.user_id WHERE type = ?",
	Params:    []interface{}{usersType},
	ChunkSize: 100,
	ForEachChunk: func(rows []struct{
		User User `tablename:"u"`
		Post Post `tablename:"p"`
	}) error {
		err := sendRowsSomewhere(rows)
		if err != nil {
			// This will abort the QueryChunks loop and return this error
			return err
		}
		return nil
	},
})
if err != nil {
	panic(err.Error())
}

As advanced as this feature might seem we don't do any parsing of the query, and all the work is done only once and then cached.

What actually happens is that we use the "tablename" tag to build the SELECT part of the query like this:

• SELECT u.id, u.name, u.age, p.id, p.title

This is then cached, and when we need it again we concatenate it with the rest of the query.

This feature has two important limitations:

1. It is not possible to use tablename tags together with normal KSQL tags. Doing so will cause the tablename tags to be ignored in favor of the KSQL ones.
2. It is not possible to use it without omitting the SELECT part of the query. While in normal queries we match the selected field with the attribute by name, in queries joining multiple tables we can't use this strategy because different tables might have columns with the same name, and we don't really have access to the full name of these columns making, for example, it impossible to differentiate between u.id and p.id except by the order in which these fields were passed. Thus, it is necessary that the library itself writes the SELECT part of the query when using this technique so that we can control the order or the selected fields.

Ok, but what if I don't want to use this feature?

You are not forced to, and there are a few use-cases where you would prefer not to, e.g.:

var rows []struct{
	UserName string `ksql:"name"`
	PostTitle string `ksql:"title"`
}
err := db.Query(ctx, &rows, "SELECT u.name, p.title FROM users u JOIN posts p ON u.id = p.user_id LIMIT 10")
if err != nil {
	panic(err.Error())
}

In the example above, since we are only interested in a couple of columns it is far simpler and more efficient for the database to only select the columns that we actually care about, so it's better not to use composite structs.