CRUD Operations — Read

So far, you've learned the basics of using GORM to retrieve records from database tables. You have also been acquainted with GORM's CRUD Interface and know how to create and insert records into tables.

In this topic, you'll learn about advanced read operations — specifically, how to retrieve records with diverse conditions to perform robust queries using GORM.

Retrieving records overview

You might remember that GORM performs a SELECT query every time you use it to retrieve a record from a table. Let's briefly recap the most basic methods that GORM provides to retrieve records:

• db.First() returns one record — the first record, ordered by the primary key in ascending order;

• db.Last() returns one record — the last record, ordered by the primary key in descending order;

• db.Find() returns all records, ordered by the primary key in ascending order.

Even though these three methods are handy, you'll need to perform more complex queries when working with large databases with multiple tables. For example: retrieving records using logical expressions or in a specific order, retrieving unique records, or selecting specific columns to retrieve.

After you've downloaded the example-library project files, you can start using GORM to perform advanced queries.

Retrieving records with string conditions

When you need to fetch specific records from your database, you can use GORM's db.Where() method; it allows you to pass in plain SQL as string conditions, making it possible to use logical expressions in your queries.

Here's how you could use db.Where() to retrieve a single Book record by its title:

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

    // Retrieve a `Book` by its `title`:
    var book Book
    result := db.Where("title = ?", "The Two Towers").First(&book)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Book: %v\n", result.Error)
    }

    fmt.Printf("%-2d | %-41s | %s\n", book.ID, book.Title, book.DatePublished)
}

// Output:
// 3  | The Two Towers                            | 1954-11-11 00:00:00 +0000 UTC

Complex queries with string conditions

String conditions can be very flexible if you are familiar with SQL. You can specify exact match conditions, leverage pattern matching, select ranges, and even combine multiple conditions to perform more complex queries using SQL's rich set of operators.

As an illustration of a complex query, suppose you wanted to retrieve all Book records whose title begins with "Harry Potter" and published between "1998-01-01" and "2006-01-01"; such a complex query requires both pattern matching and range selection.

To execute the above query, you would use the LIKE and BETWEEN operators by chaining two Where() methods together, effectively combining the two string conditions using the AND operator:

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

    var books []Book
    result := db.Where("title LIKE ?", "Harry Potter%").
        Where("date_published BETWEEN ? AND ?", "1999-01-01", "2006-01-01").
        Find(&books)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Books: %v\n", result.Error)
    }

    for _, book := range books {
        fmt.Printf("%-2d | %-41s | %s\n", book.ID, book.Title, book.DatePublished)
    }
}

// Output:
// 9  | Harry Potter and the Prisoner of Azkaban  | 1999-07-08 00:00:00 +0000 UTC
// 10 | Harry Potter and the Goblet of Fire       | 2000-07-08 00:00:00 +0000 UTC
// 11 | Harry Potter and the Order of the Phoenix | 2003-06-21 00:00:00 +0000 UTC
// 12 | Harry Potter and the Half-Blood Prince    | 2005-07-16 00:00:00 +0000 UTC

And if you need to create conditions with the OR operator, you can use the Or() method. The below example will retrieve all books where the title is either "Carrie" or "It":

result = db.Where("title = ?", "Carrie").Or("title = ?", "It").Find(&books)
if result.Error != nil {
    log.Fatalf("cannot retrieve Books: %v\n", result.Error)
}

If you master SQL syntax, you can get creative with string conditions! You can look at additional examples of queries with string conditions in GORM's official docs.

Retrieving records by selecting specific columns

In the previous sections, the queries selected every column from the tables via the SELECT * FROM table_name WHERE ...; syntax because GORM's default behavior is to select ALL columns.

However, sometimes, you don't need to retrieve all the information from a record. For example, if you were creating a list of Book records for the library's website front page, you might only need the values from the title and date_published columns; this list would give website visitors a quick view of the library's books.

To achieve this, GORM provides the db.Select() method, which allows you to specify which columns you want to retrieve:

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

    var books []Book
    result := db.Select("title, date_published").Find(&books)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Books: %v\n", result.Error)
    }

    for _, book := range books {
        fmt.Printf("%-2d | %-41s | %s\n", book.ID, book.Title, book.DatePublished)
    }
}

// Output:
// 0  | The Hobbit                                | 1937-01-21 00:00:00 +0000 UTC
// 0  | The Fellowship of the Ring                | 1954-07-29 00:00:00 +0000 UTC
// ..............................................................................
// 0  | The Talisman                              | 1984-11-08 00:00:00 +0000 UTC

Since you only selected the title and date_published columns, GORM didn't retrieve any values from the id column.

In future topics, you'll learn how to use GORM to perform JOIN operations between tables and apply the GROUP BY clause to categorize your data. The db.Select() method becomes crucial in such complex queries, enabling selective data retrieval from multiple tables or groups.

Retrieving distinct records

Inside a table, a column often contains many duplicate values. For example, since a Publisher can publish many books, the publisher_id values in the books table can repeat multiple times. If you want to retrieve only the distinct (unique) publisher_id values, you can use the db.Distinct() method:

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

    var publisherIDs []uint
    result := db.Model(&Book{}).Distinct("publisher_id").Find(&publisherIDs)
    if result.Error != nil {
        log.Fatalf("cannot retrieve distinct Publisher IDs: %v\n", result.Error)
    }

    fmt.Printf("Distinct Publisher IDs: %v\n", publisherIDs)
}

// Output:
// Distinct Publisher IDs: [1 2 3 4 5 6 7 8 9 10 11 12]

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 the Distinct("publisher_id") to ensure that unique values are retrieved from the publisher_id column. Finally, you use Find(&publisherIDs) to execute the query and store the retrieved unique values into the publisherIDs slice.

You can also retrieve distinct values from multiple columns with the Distinct() method, as it accepts a variadic number of column names. For instance, if you wanted to retrieve distinct title and publisher_id values from the books table, you could do it like this:

var books []Book
result = db.Distinct("title", "publisher_id").Find(&books)
if result.Error != nil {
    log.Fatalf("cannot retrieve distinct Title and Publisher ID values: %v\n", result.Error)
}

Retrieving records in a specific order

Records often need to be displayed in a specific order. Going back to the list of Book records for the library's website front page example, you might want to display the books sorted by their date_published so the latest published books are on top.

To achieve this, you can use the db.Order() method, specifying the column you want to sort by and the sorting order ("ASC" or "DESC") as a string:

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

    var books []Book
    result := db.Order("date_published DESC").Find(&books)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Books in specified order: %v\n", result.Error)
    }

    for _, book := range books {
        fmt.Printf("%-2d | %-41s | %s\n", book.ID, book.Title, book.DatePublished)
    }
}

// Output:
// 6  | Beren and Lúthien                         | 2017-06-01 00:00:00 +0000 UTC
// 37 | Mockingjay                                | 2010-08-24 00:00:00 +0000 UTC
// ..............................................................................
// 1  | The Hobbit                                | 1937-01-21 00:00:00 +0000 UTC

And if you wanted to sort by multiple columns, such as alphabetically by title and then by date_published, you can pass multiple arguments to db.Order():

result = db.Order("title ASC, date_published DESC").Find(&books)
if result.Error != nil {
    log.Fatalf("cannot retrieve Books in specified order: %v\n", result.Error)
}

Retrieving records with a limit and offset

Once again, let's return to the list of Book records for the library's website front page example. Imagine you only want to display the 10 most recently published books on the front page this time.

In such a scenario, GORM provides the db.Limit() method, which allows you to limit the number of records retrieved from a table:

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

    var books []Book
    result := db.Order("date_published DESC").Limit(10).Find(&books)
    if result.Error != nil {
        log.Fatalf("cannot retrieve Books: %v\n", result.Error)
    }

    for _, book := range books {
        fmt.Printf("%-2d | %-41s | %s\n", book.ID, book.Title, book.DatePublished)
    }
}

// Output:
// 6  | Beren and Lúthien                         | 2017-06-01 00:00:00 +0000 UTC
// 37 | Mockingjay                                | 2010-08-24 00:00:00 +0000 UTC
// ..............................................................................
// 29 | Going Postal                              | 2004-09-25 00:00:00 +0000 UTC

And to specify the number of records to skip before returning the records, you can use the db.Offset() method. Note that you can chain db.Limit() with db.Offset().

But what if the library database grows and has thousands of Book records? Displaying them all at once would not be a good idea. Instead, you could implement a pagination feature to display a limited number of books per page and allow users to navigate to other pages to see more books.

Enter GORM's db.Offset() method, it allows you to specify the number of records to skip before starting to retrieve records. For instance, if you wanted to retrieve the second set of 10 books (books 11-20), you could do so like this:

result = db.Order("date_published DESC").Limit(10).Offset(10).Find(&books)
if result.Error != nil {
    log.Fatalf("cannot retrieve Books: %v\n", result.Error)
}

In this way, db.Limit() and db.Offset() can be chained together to implement robust data retrieval methods such as pagination.

Conclusion

Congrats on making it here! You have learned to use GORM's CRUD interface to perform advanced read operations and queries.

You also learned that GORM allows you to use plain SQL string conditions with the db.Where() method to retrieve records.

Finally, you learned how to select specific columns to retrieve, order and limit retrieved records, and retrieve distinct (unique) values using the db.Distinct() method.

Now it's time to test your knowledge about advanced read operations and complex queries using GORM's CRUD interface with a few theory and coding tasks; let's go!