Grouping and Filtering records

Up to now, you've learned how to create and fetch records from database tables using GORM; this base knowledge paves the way for you to explore more complex data-processing queries.

In this topic, you'll discover how to group and filter records and implement SQL aggregate functions using GORM.

Aggregate functions in GORM

As you know, SQL aggregate functions are useful for performing calculations on sets of values to return a single value. In GORM, integrating SQL aggregate functions is straightforward by leveraging the Select() method with plain SQL queries.

Now, suppose you wanted to find the total number of books in the library database. To achieve this, you would use the Select() method and combine it with the count() aggregate function:

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

    var numberOfBooks int

    result := db.Model(&Book{}).
        Select("count(title) as number_of_books").
        First(&numberOfBooks)
    if result.Error != nil {
        log.Fatalf("cannot retrieve number of books: %v\n", result.Error)
    }

    fmt.Printf("We have %d books.\n", numberOfBooks)
}

// Output:
// We have 40 books.

In the above example, you first use db.Model(&Book{}) to specify that you want to run operations on the books table, then you chain Select("count(title) as number_of_books").First(&numberOfBooks) to specify the aggregation operation to be performed and get the single result in numberOfBooks.

As you can see, if you are proficient with aggregate functions, you can seamlessly integrate them with GORM to perform more intricate queries to derive information not directly saved in the database by summarizing multiple records in a single value.

Grouping records

So far, you have learned how aggregate functions can perform simple analytical tasks for a whole table or one specific category. Usually, we need to compute an aggregate value for many separate categories or groups; for this reason, we will use grouping records.

Grouping records allows you to segment your data based on one or more columns. Performing aggregate functions with grouped records enables you to apply these functions to specific groups of data separately rather than the entire dataset, which allows you to observe the aggregated value within each group.

In the following example, we will determine the number of books for each Publisher:

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

    type PublisherBookCount struct {
        PublisherID   uint
        NumberOfBooks int
    }

    var publisherCounts []PublisherBookCount
    result := db.Model(&Book{}).
        Select("publisher_id, count(title) as number_of_books").
        Group("publisher_id").
        Find(&publisherCounts)
    if result.Error != nil {
        log.Fatalf("cannot retrieve book count per Publisher: %v\n", result.Error)
    }

    for _, publisherCount := range publisherCounts {
        fmt.Printf("Publisher with ID:%-2d has %-2d book/s.\n",
            publisherCount.PublisherID,
            publisherCount.NumberOfBooks)
    }
}

// Output:
// Publisher with ID:1  has 5  book/s.
// Publisher with ID:2  has 2  book/s.
// Publisher with ID:3  has 8  book/s.
// …
// Publisher with ID:12 has 1  book/s.

In the above code, we grouped the data by publisher_id Group("publisher_id") and calculated the number of books for each group/publisher with Select("count(title) as number_of_books, publisher_id")

Selecting a column beside an aggregation function without mentioning it in the Group() method is not recommended; this might cause an error, or, more importantly, the result may not accurately reflect the intended grouping and aggregation.

Filtering records

Previously, we used the Where() method to filter the records in a query's results. Well, we can't use the Where() to filter on an aggregated value since it only works on the table's original columns.

GORM's Having() method becomes essential when dealing with aggregate functions and grouped records. Unlike Where(), which filters individual records based on table columns, Having() allows you to apply conditions to the results of aggregate functions within grouped data.

We will extend the previous example to determine the number of books for each publisher that has published more than one book:

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

    type PublisherBookCount struct {
        PublisherID   uint
        NumberOfBooks int
    }

    var publisherCounts []PublisherBookCount
    result := db.Model(&Book{}).
        Select("publisher_id, count(title) as number_of_books").
        Group("publisher_id").
        Having("count(title) > 1").
        Find(&publisherCounts)
    if result.Error != nil {
        log.Fatalf("cannot retrieve book count per Publisher: %v\n", result.Error)
    }

    for _, publisherCount := range publisherCounts {
        fmt.Printf("Publisher with ID:%-2d has %-2d book/s.\n",
            publisherCount.PublisherID,
            publisherCount.NumberOfBooks)
    }
}
// Output:
// Publisher with ID:1  has 5  book/s.
// Publisher with ID:2  has 2  book/s.
// Publisher with ID:3  has 8  book/s.
// …
// Publisher with ID:10 has 8  book/s.

Since the restriction is on an aggregated value, you had to use Having() and set the aggregated value inside it Having("count(title) > 1"). Now you can filter the result either on a table's column using Where() or on an aggregated value using Having().

Ordering grouped records

You might remember that you can sort retrieved records using Order(); this sorting feature becomes particularly useful when working with grouped data, allowing for an organized presentation of aggregated results.

To further explain how you can apply the Order()method after grouping records, suppose you wanted to retrieve the publishers with the most published books. You would first group the publishers, then count the number of books each has published, and finally order the records in descending order of their book counts.

The following example displays the Publisher records arranged from the most to the least prolific:

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

    type PublisherBookCount struct {
        PublisherID   uint
        NumberOfBooks int
    }

    var publisherCounts []PublisherBookCount
    result := db.Model(&Book{}).
        Select("publisher_id, count(title) as number_of_books").
        Group("publisher_id").
        Order("number_of_books desc").
        Find(&publisherCounts)
    if result.Error != nil {
        log.Fatalf("cannot retrieve top publishers: %v\n", result.Error)
    }

    for _, count := range publisherCounts {
        fmt.Printf("Publisher with ID: %d has published %d book/s.\n",
            count.PublisherID,
            count.NumberOfBooks)
    }
}

// Output:
// Publisher with ID: 10 has published 8 book/s.
// Publisher with ID: 3 has published 8 book/s.
// Publisher with ID: 5 has published 5 book/s.
// ...
// Publisher with ID: 6 has published 1 book/s.

Conclusion

Congrats, you have extended your knowledge using GORM. You have learned to perform more complex queries by aggregating multiple records in a single value to derive information not directly saved in the database.

You have also learned how to apply aggregate functions with grouping records. Furthermore, you have learned to filter based on an aggregated value and sort the result.

Now it's time to test your knowledge with a few theory and coding tasks; let's go!