JOINS and Subqueries

You've already learned how to write detailed queries for sorting, filtering, grouping, and aggregating results with GORM. In this topic, you'll learn how to perform joins to combine records from multiple tables and how to integrate subqueries with these joins to create more dynamic and flexible queries.

Joins

Sometimes, the data you need is spread across multiple tables in a database. For such cases, GORM offers the Joins() method; it allows you to retrieve information by combining rows from two or more tables based on a condition that establishes the relationship between the tables.

Let’s look at an example of retrieving data located in two different tables, such as retrieving a book’s title and its publisher's name:

func main() {
    // ... Connect to the `library` sqlite3 database using `gorm.Open()`

    type BookPublisher struct {
        Book      string
        Publisher string
    }

    var bookPublishers []BookPublisher
    result := db.Model(&Publisher{}).
        Select("books.title as book, publishers.name as publisher").
        Joins("JOIN books ON books.publisher_id = publishers.id").
        Scan(&bookPublishers)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Book Publishers: %v", result.Error)
    }

    for _, bookPublisher := range bookPublishers {
        fmt.Printf("The book %s published by %s\n",
            bookPublisher.Book, bookPublisher.Publisher)
    }
}

// Output:
// The book The Hobbit published by George Allen & Unwin
// The book The Fellowship of the Ring published by George Allen & Unwin     
// The book The Two Towers published by George Allen & Unwin
// The book The Return of the King published by George Allen & Unwin
// The book The Silmarillion published by George Allen & Unwin
// ...

In the above example, you first use the db.Model(&Publisher{}) method to run operations on the publishers table. Next, you select the columns from the books and publishers tables via Select("books.title as book, publishers.name as publisher").

Since the titles of the books are stored in the books table, you join it with the publishers table using Joins("JOIN books ON books.publisher_id = publishers.id"); this step links the rows from the books with the corresponding rows from publishers based on the specified conditions.

Left join

As previously discussed, when using a join operation, we set join conditions to determine how rows from the first table (left table) are matched with records from the second table (right table). Of course, records that fail to meet these specified conditions won't be included in the result set.

GORM provides the flexibility to specify the join type within the Join() method. For example, we can use LEFT JOIN to retrieve all entries from the left table, regardless of whether they meet the specified join conditions or not, and RIGHT JOIN, which operates comparably on the right table. In short, the Left and Right Joins are crucial for finding inclusive results or handling mismatches.

Now, let's take a look at an example using LEFT JOIN to fetch all Publisher records that haven't published any books:

func main() {
    // ... Connect to the `library` sqlite3 database using `gorm.Open()`

    var publishers []Publisher
    result := db.Model(&Publisher{}).
        Select("publishers.id, publishers.name").
        Joins("LEFT JOIN books ON books.publisher_id = publishers.id").
        Where("books.id IS NULL").
        Find(&publishers)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Publishers that haven't published any books: %v\n", result.Error)
    }

    if len(publishers) == 0 {
        fmt.Println("All publishers have published books.")
    } else {
        fmt.Println("Publishers that did not publish any book:")
        for _, publisher := range publishers {
            fmt.Printf("ID: %d, Name: %s\n", publisher.ID, publisher.Name)
        }
    }
}

// Output:
// All publishers have published books.

In the above snippet, &Publisher{} represents the left table publishers. After applying a LEFT JOIN with the books table, all records from the publishers table are included in the join result. However, if the join condition fails to find a match, the corresponding columns from the books table are filled with NULL values.

Since the objective of the above query is to find Publisher records that haven't published any books, there is a filter condition that fetches records where the books.id field is NULL, indicating publishers with no associated books.

Preloading

Preloading or eager loading is a strategy where, instead of fetching data lazily (on-demand), you fetch the necessary associated data along with the main data in a single query; this is in contrast to lazy loading, where the related data is only fetched when explicitly requested.

In the following example, we query the database to retrieve all the Author records, and we also perform preloading on the Book records. The Preload("Books") method tells GORM to fetch each author's associated books in the same database query used to fetch the authors:

func main() {
    // ... Connect to the `library` sqlite3 database using `gorm.Open()`

    var authors []Author
    result := db.Preload("Books").Find(&authors)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Authors preloaded Books: %v\n", result.Error)
    }

    for _, author := range authors {
        fmt.Printf("Author: %s\nBooks:\n", author.Name)
        for _, book := range author.Books {
            fmt.Printf("- %s\n", book.Title)
        }
    }
}

// Output:
// Author: J.R.R. Tolkien
// Books:
// - The Hobbit
// - The Fellowship of the Ring
// - ...
// Author: J.K. Rowling
// Books:
// - Harry Potter and the Philosopher's Stone
// - Harry Potter and the Chamber of Secrets
// - ...
// Author: ...
// Books:
// - ...

Eager loading helps to reduce the number of database queries and improve overall performance by fetching related data in a single query operation; this is particularly beneficial when you know you'll need the associated data for each record and want to avoid the overhead of multiple database calls. By preloading associated entities, like books for each author in this example, GORM efficiently handles the data retrieval more optimally.

However, it's crucial to use eager loading judiciously. While it reduces the number of queries, it can also lead to fetching a large amount of data that might not be necessary in all contexts; when using eager loading, the key is to maintain a balance between performance and resource utilization based on the specific data access patterns your application requires.

Integrating Subqueries with Joins

A subquery is a nested query within another SQL statement. It allows you to write a main query that retrieves data based on the results of an inner query. GORM creates subqueries when using a *gorm.DB object as a parameter, and you can use a subquery in the Where() and Having() methods.

Subqueries are useful for filtering complex conditions and data retrieval from multiple tables; they enhance your queries' readability and logical structure, making them more concise and efficient.

Consider the scenario where you want to retrieve the Author records who wrote books from the top three publishers with the highest book publication counts. This relationship might seem complex, so you need to break it down: first, locate the top publishers based on book count and then retrieve the authors affiliated with these publishers:

func main() {
   // ... Connect to the `library` database using gorm.Open()

   type AuthorName struct {
      Name string
   }
   var authors []AuthorName

   // Step 1: Identify the top 3 publishers by their book publication count
   topPublishersQuery := db.Model(&Publisher{}).
      Select("publishers.id AS publisher_id, COUNT(books.title) AS book_count").
      Joins("JOIN books ON books.publisher_id = publishers.id").
      Group("publisher_id").
      Order("book_count DESC").
      Limit(3)

   // Step 2: Retrieve authors who have books published by these top publishers
   db.Model(&Author{}).
      Select("DISTINCT authors.name").
      Joins("JOIN author_books ON authors.id = author_books.author_id").
      Joins("JOIN books ON books.id = author_books.book_id").
      Joins("JOIN (?) AS top_publishers ON top_publishers.publisher_id = books.publisher_id", topPublishersQuery).
      Scan(&authors)

   fmt.Println("Authors affiliated with the top 3 publishers: ")
   for _, author := range authors {
      fmt.Println(author.Name)
   }
}

// Output:
// Authors affiliated with the top 3 publishers:
// Suzanne Collins
// J.K. Rowling
// Neil Gaiman
// Stephen King
// Peter Straub

In the above example, the first step is to define a subquery, topPublishersQuery, to fetch the top three publishers based on their book publication counts; this is achieved by joining the publishers and books tables, grouping the results by publisher_id, and ordering them by the book_count in descending order.

The second step is to use the subquery to retrieve authors who have publications with these top publishers. By joining the authors, author_books, and books tables, and integrating the subquery, the final query effectively filters and retrieves a distinct list of authors linked to the top three publishers; this approach demonstrates the integration of subqueries with joins in GORM to accomplish a complex data retrieval task.

Conclusion

You've expanded your knowledge with GORM and learned how to perform different joins, including LEFT JOIN, RIGHT JOIN, and regular joins, through the Joins() method. Additionally, you've delved into the strategic use of subqueries, enhancing your ability to build more complex queries.

Now, it's time to solidify your knowledge by solving some theory and coding tasks; let's go!