Joins

An example schema with foreign keys, adapted from sol_test.go, for performing joins:

var Users = sol.Table("users",
	sol.Column("id", types.Integer()),
	sol.Column("email", types.Varchar().Limit(256).NotNull()),
	sol.Column("password", types.Varchar()),
	sol.PrimaryKey("id"),
	sol.Unique("email"),
)

var Contacts = sol.Table("contacts",
	sol.Column("id", types.Integer()),
	sol.ForeignKey("user_id", Users),
	sol.Column("key", types.Varchar()),
	sol.Column("value", types.Varchar()),
	sol.PrimaryKey("id"),
	sol.Unique("user_id", "key"),
)

Example statement that joins the tables. From() is used to manually set the FROM clause, otherwise the SELECT will use both the Users and Contacts table:

stmt := sol.Select(
	Users.C("email"),
	Contacts.C("key"),
	Contacts.C("value"),
).From(Users).InnerJoin(
	Contacts,
	Contacts.C("user_id").Equals(Users.C("id")),
).Where(
	Users.C("email").Equals("[email protected]"),
)

SELECT "users"."email", "contacts"."key", "contacts"."value" FROM "users" INNER JOIN "contacts" ON "contacts"."user_id" = "users"."id" WHERE "users"."email" = $1

This statement can be queried into a struct:

type ContactWithEmail struct {
	Email string
	Key   string
	Value string
}

var contacts []ContactWithEmail
conn.Query(stmt, &contacts)
log.Println(contacts)
// [{[email protected] github example} {[email protected] twitter example}]