The upper/db tour

< back
14/18
next >

a) SQL Builder: JOIN Queries and Struct Composition

Now, let’s suppose you have independent Go structs (each one mapped to a different table) and a JOIN query that returns a result combining columns from all the mapped tables.

In this scenario, you can create a new struct and embed all the structs using the inline option (db:",inline"), like in the following example (where Book, Author, and Subject are independent structs):

type BookAuthorSubject struct {
  Book    `db:",inline"`
  Author  `db:",inline"`
  Subject `db:",inline"`
}

… and then create the JOIN query using the builder:

q := sess.Select("b.id AS book_id", "*").
  From("books AS b").
  Join("subjects AS s").On("b.subject_id = s.id").
  Join("authors AS a").On("b.author_id = a.id").
  OrderBy("a.last_name", "b.title")

Note that an alias for book_id is created with Select("b.id AS book_id", "*"). This is because all three embedded structs have a field with the same name (id), which is ambiguous. The final struct (BookAuthorSubject) should look like this:

type BookAuthorSubject struct {
  BookID   uint `db:"book_id"`

  Book    `db:",inline"`
  Author  `db:",inline"`
  Subject `db:",inline"`
}

Finally, use All to copy every single result of the query into the books slice:

var books []BookAuthorSubject
err := q.All(&books)

< back
14/18
next >

              package main

import (
	"fmt"
	"log"

	"github.com/upper/db/v4/adapter/postgresql"
)

var settings = postgresql.ConnectionURL{
	Database: "booktown",
	Host:     "postgres",
	User:     "demo",
	Password: "b4dp4ss",
	Options: map[string]string{
		"sslmode": "disable", // Disable TLS
	},
}

// Book represents a record from the "books" table.
// booktown=> \d books
//
//	   Table "public.books"
//	Column   |  Type   | Modifiers
//
// ------------+---------+-----------
//
//	id         | integer | not null
//	title      | varchar | not null
//	author_id  | integer |
//	subject_id | integer |
//
// Indexes:
//
//	"books_id_pkey" PRIMARY KEY, btree (id)
//	"books_title_idx" btree (title)
type Book struct {
	ID        uint   `db:"id,omitempty"`
	Title     string `db:"title"`
	AuthorID  uint   `db:"author_id,omitempty"`
	SubjectID uint   `db:"subject_id,omitempty"`
}

// Author represents a record from the "authors" table.
// booktown=> \d authors
//
//	   Table "public.authors"
//	Column   |  Type   | Modifiers
//
// ------------+---------+-----------
//
//	id         | integer | not null
//	last_name  | text    |
//	first_name | text    |
//
// Indexes:
//
//	"authors_pkey" PRIMARY KEY, btree (id)
type Author struct {
	ID        uint   `db:"id,omitempty"`
	LastName  string `db:"last_name"`
	FirstName string `db:"first_name"`
}

// Subject represents a record from the "subjects" table.
// booktown=> \d subjects
//
//	  Table "public.subjects"
//	Column  |  Type   | Modifiers
//
// ----------+---------+-----------
//
//	id       | integer | not null
//	subject  | text    |
//	location | text    |
//
// Indexes:
//
//	"subjects_pkey" PRIMARY KEY, btree (id)
type Subject struct {
	ID       uint   `db:"id,omitempty"`
	Subject  string `db:"subject"`
	Location string `db:"location"`
}

func main() {
	sess, err := postgresql.Open(settings)
	if err != nil {
		log.Fatal("Open: ", err)
	}
	defer sess.Close()

	// The BookAuthorSubject type represents an element that has columns from
	// different tables.
	type BookAuthorSubject struct {
		// The book_id column was added to prevent collisions with the other "id"
		// columns from Author and Subject.
		BookID uint `db:"book_id"`

		Book    `db:",inline"`
		Author  `db:",inline"`
		Subject `db:",inline"`
	}

	// This is a query with a JOIN clause that was built using the SQL builder.
	q := sess.SQL().
		Select("b.id AS book_id", "*"). // Note the alias set for book.id.
		From("books AS b").
		Join("subjects AS s").On("b.subject_id = s.id").
		Join("authors AS a").On("b.author_id = a.id").
		OrderBy("a.last_name", "b.title")

	// The JOIN query above returns data from three different tables.
	var books []BookAuthorSubject
	if err := q.All(&books); err != nil {
		log.Fatal("q.All: ", err)
	}

	for _, book := range books {
		fmt.Printf("Book %d:\t%s. %q on %s\n", book.BookID, book.Author.LastName, book.Book.Title, book.Subject.Subject)
	}
}

            