Skip to content

Queries

Retrieving Data

REL provides two basic finders method, Find for retrieving single record, and FindAll for retrieving multiple record.

Note

  • Find only accepts struct as the first argument, and always return the first result from the query.
  • FindAll only accepts slice as the first argument, and always return all result from the query.

Retrieve a book where id=1:

var book Book
err := repo.Find(ctx, &book, where.Eq("id", 1))
book := Book{ID: 1, Title: "REL for dummies"}
repo.ExpectFind(where.Eq("id", 1)).Result(book)

Retrieve all books:

var books []Book
err := repo.FindAll(ctx, &books)
books := []Book{
    {ID: 1, Title: "REL for dummies"},
}
repo.ExpectFindAll().Result(books)

Conditions

To retrieve filtered recods from database, you can use filter api to specify condition. For example, to filter all books that available, you can use rel.Eq in the query builder.

Retrieve all available books using filter query:

var books []Book
err := repo.FindAll(ctx, &books, rel.Eq("available", true))
books := []Book{
    {ID: 1, Title: "REL for dummies"},
}
repo.ExpectFindAll(rel.Eq("available", true)).Result(books)

Alias can be used to boost readability when dealing with short query:

var books []Book
err := repo.FindAll(ctx, &books, where.Eq("available", true))
books := []Book{
    {ID: 1, Title: "REL for dummies"},
}
repo.ExpectFindAll(where.Eq("available", true)).Result(books)

Use fragment to specify custom SQL query:

var books []Book
err := repo.FindAll(ctx, &books, where.Fragment("available=?", true))
books := []Book{
    {ID: 1, Title: "REL for dummies"},
}
repo.ExpectFindAll(where.Fragment("available=?", true)).Result(books)

You can use rel.And or rel.Or to specify more conditions.

Retrieve all available books where price is at least 100 or in discount using filter query:

var books []Book
err := repo.FindAll(ctx, &books, rel.And(rel.Eq("available", true), rel.Or(rel.Gte("price", 100), rel.Eq("discount", true))))
books := []Book{
    {ID: 1, Title: "REL for dummies", Price: 100},
    {ID: 2, Title: "REL for dummies", Price: 50, Discount: true},
}
repo.ExpectFindAll(rel.And(rel.Eq("available", true), rel.Or(rel.Gte("price", 100), rel.Eq("discount", true)))).Result(books)

Retrieve all available books where price is at least 100 or in discount using chained filter query:

var books []Book
err := repo.FindAll(ctx, &books, rel.Eq("available", true).And(rel.Gte("price", 100).OrEq("discount", true)))
books := []Book{
    {ID: 1, Title: "REL for dummies", Price: 100},
    {ID: 2, Title: "REL for dummies", Price: 50, Discount: true},
}
repo.ExpectFindAll(rel.Eq("available", true).And(rel.Gte("price", 100).OrEq("discount", true))).Result(books)

Retrieve all available books where price is at least 100 or in discount using alias (github.com/go-rel/rel/where):

var books []Book
err := repo.FindAll(ctx, &books, where.Eq("available", true).And(where.Gte("price", 100).OrEq("discount", true)))
books := []Book{
    {ID: 1, Title: "REL for dummies", Price: 100},
    {ID: 2, Title: "REL for dummies", Price: 50, Discount: true},
}
repo.ExpectFindAll(where.Eq("available", true).And(where.Gte("price", 100).OrEq("discount", true))).Result(books)

Subquery

Subquery is supported in condition clause, you can pass any query to the where/having parameter.

Retrieve all books that is cheaper than average book price:

var books []Book
err := repo.FindAll(ctx, &books, where.Lt("price", rel.Select("AVG(price)").From("books")))
books := []Book{
    {ID: 1, Title: "REL for dummies", Price: 100},
    {ID: 2, Title: "REL for dummies", Price: 50, Discount: true},
}
repo.ExpectFindAll(where.Lt("price", rel.Select("AVG(price)").From("books"))).Result(books)

Sorting

To retrieve records from database in a specific order, you can use the sort api.

Sort books ascending by updated_at field:

var books []Book
err := repo.FindAll(ctx, &books, rel.NewSortAsc("updated_at"))
books := []Book{
    {ID: 1, Title: "REL for dummies", UpdatedAt: time.Now()},
}
repo.ExpectFindAll(rel.NewSortAsc("updated_at")).Result(books)

Using alias if you need more syntactic sugar:

var books []Book
err := repo.FindAll(ctx, &books, sort.Asc("updated_at"))
books := []Book{
    {ID: 1, Title: "REL for dummies", UpdatedAt: time.Now()},
}
repo.ExpectFindAll(sort.Asc("updated_at")).Result(books)

Combining with other query is fairly easy.

Chain where and sort using query builder:

var books []Book
err := repo.FindAll(ctx, &books, rel.Where(where.Eq("available", true)).SortAsc("updated_at"))
books := []Book{
    {ID: 1, Title: "REL for dummies", UpdatedAt: time.Now()},
}
repo.ExpectFindAll(rel.Where(where.Eq("available", true)).SortAsc("updated_at")).Result(books)

It's also possible to use variadic arguments to combine multiple queries:

var books []Book
err := repo.FindAll(ctx, &books, where.Eq("available", true), sort.Asc("updated_at"))
books := []Book{
    {ID: 1, Title: "REL for dummies", UpdatedAt: time.Now()},
}
repo.ExpectFindAll(where.Eq("available", true), sort.Asc("updated_at")).Result(books)

Selecting Specific Fields

To select specific fields, you can use Select method, this way only specificied field will be mapped to books.

Note

Specifying select without argument (rel.Select()) will automatically load all fields. This is helpful when used as query builder entry point (compared to using rel.From), because you can let REL to infer the table name.

Load only id and title:

var books []Book
err := repo.FindAll(ctx, &books, rel.Select("id", "title"))
books := []Book{
    {ID: 1, Title: "REL for dummies"},
}
repo.ExpectFindAll(rel.Select("id", "title")).Result(books)

Using Specific Table

By default, REL will use pluralized-snakecase struct name as the table name. To select from specific table, you can use From method.

Load from ebooks table:

var books []Book
err := repo.FindAll(ctx, &books, rel.From("ebooks"))
books := []Book{
    {ID: 1, Title: "REL for dummies"},
}
repo.ExpectFindAll(rel.From("ebooks")).Result(books)

Chain the query with select:

var books []Book
err := repo.FindAll(ctx, &books, rel.Select("id", "title").From("ebooks"))
books := []Book{
    {ID: 1, Title: "REL for dummies"},
}
repo.ExpectFindAll(rel.Select("id", "title").From("ebooks")).Result(books)

Limit and Offset

To set the limit and offset of query, use Limit and Offset api. Offset will be ignored if Limit is not specified.

Specify limit and offset:

var books []Book
err := repo.FindAll(ctx, &books, rel.Limit(10), rel.Offset(20))
books := []Book{
    {ID: 1, Title: "REL for dummies"},
}
repo.ExpectFindAll(rel.Limit(10), rel.Offset(20)).Result(books)

As a chainable api:

var books []Book
err := repo.FindAll(ctx, &books, rel.Select().Limit(10).Offset(20))
books := []Book{
    {ID: 1, Title: "REL for dummies"},
}
repo.ExpectFindAll(rel.Select().Limit(10).Offset(20)).Result(books)

Group

To use group by query, you can use Group method.

Retrieve count of books for every category:

// custom struct to store the result.
var results []struct {
    Category string
    Total    int
}

// we need to explicitly specify table name since we are using an anonymous struct.
err := repo.FindAll(ctx, &results, rel.Select("category", "COUNT(id) as total").From("books").Group("category"))
results := []struct {
    Category string
    Total    int
}{
    {Category: "education", Total: 100},
}
repo.ExpectFindAll(rel.Select("category", "COUNT(id) as total").From("books").Group("category")).Result(results)

Joining Tables

To join tables, you can use join api.

Note

Joining table won't load the association to struct. If you want to load association on a struct, use preload instead.

Join transaction and book table, then filter only transaction that have specified book name. This methods assumes belongs to relation, which means it'll try to join using transactions.book_id=books.id:

var transactions []Transaction
err := repo.FindAll(ctx, &transactions, rel.Join("books").Where(where.Eq("books.name", "REL for Dummies")))
transactions := []Transaction{
    {ID: 1, Status: "paid"},
}
repo.ExpectFindAll(rel.Join("books").Where(where.Eq("books.name", "REL for Dummies"))).Result(transactions)

Specifying which column to join using JoinOn:

var transactions []Transaction
err := repo.FindAll(ctx, &transactions, rel.JoinOn("books", "transactions.book_id", "books.id"))
transactions := []Transaction{
    {ID: 1, Status: "paid"},
}
repo.ExpectFindAll(rel.JoinOn("books", "transactions.book_id", "books.id")).Result(transactions)

Syntactic sugar also available for join:

var transactions []Transaction
err := repo.FindAll(ctx, &transactions, join.On("books", "transactions.book_id", "books.id"))
transactions := []Transaction{
    {ID: 1, Status: "paid"},
}
repo.ExpectFindAll(join.On("books", "transactions.book_id", "books.id")).Result(transactions)

Joining table with custom join mode:

var transactions []Transaction
err := repo.FindAll(ctx, &transactions, rel.JoinWith("LEFT JOIN", "books", "transactions.book_id", "books.id"))
transactions := []Transaction{
    {ID: 1, Status: "paid"},
}
repo.ExpectFindAll(rel.JoinWith("LEFT JOIN", "books", "transactions.book_id", "books.id")).Result(transactions)

Use fragment for more complex join query:

var transactions []Transaction
err := repo.FindAll(ctx, &transactions, rel.Joinf("JOIN `books` ON `transactions`.`book_id`=`books`.`id`"))
transactions := []Transaction{
    {ID: 1, Status: "paid"},
}
repo.ExpectFindAll(rel.Joinf("JOIN `books` ON `transactions`.`book_id`=`books`.`id`")).Result(transactions)

Pessimistic Locking

REL supports pessimistic locking by using mechanism provided by the underlying database. Lock can be only used only inside transaction.

Retrieve and lock a row for update:

var book Book
err := repo.Find(ctx, &book, where.Eq("id", 1), rel.Lock("FOR UPDATE"))
var book Book
repo.ExpectFind(where.Eq("id", 1), rel.Lock("FOR UPDATE")).Result(book)

Retrieve and lock a row using predefined lock alias:

var book Book
err := repo.Find(ctx, &book, where.Eq("id", 1), rel.ForUpdate())
var book Book
repo.ExpectFind(where.Eq("id", 1), rel.ForUpdate()).Result(book)

Retrieve and lock a row using chained query:

var book Book
err := repo.Find(ctx, &book, rel.Where(where.Eq("id", 1)).Lock("FOR UPDATE"))
var book Book
repo.ExpectFind(rel.Where(where.Eq("id", 1)).Lock("FOR UPDATE")).Result(book)

Aggregation

REL provides a very basic Aggregate method which can be used to count, sum, max etc.

Count all available books using aggregate:

count, err := repo.Aggregate(ctx, rel.From("books").Where(where.Eq("available", true)), "count", "id")
repo.ExpectAggregate(rel.From("books").Where(where.Eq("available", true)), "count", "id").Result(5)

Count all available books using count:

count, err := repo.Count(ctx, "books")
repo.ExpectCount("books").Result(7)

Count all available books using count:

count, err := repo.Count(ctx, "books", where.Eq("available", true))
repo.ExpectCount("books", where.Eq("available", true)).Result(5)

Pagination

REL provides a convenient FindAndCountAll methods that is useful for pagination, It's a combination of FindAll and Count method. FindAndCountAll returns count of records (ignoring limit and offset query) and an error.

Retrieve all books within limit and offset and also count of all books:

var books []Book
count, err := repo.FindAndCountAll(ctx, &books, rel.Where(where.Like("title", "%dummies%")).Limit(10).Offset(10))
books := []Book{
    {ID: 1, Title: "REL for dummies"},
}
repo.ExpectFindAndCountAll(rel.Where(where.Like("title", "%dummies%")).Limit(10).Offset(10)).Result(books, 12)

Batch Iteration

REL provides records iterator that can be use for perform batch processing of large amounts of records.

Options:

  • BatchSize - The size of batches (default 1000).
  • Start - The primary value (ID) to start from (inclusive).
  • Finish - The primary value (ID) to finish at (inclusive).
var (
    user User
    iter = repo.Iterate(ctx, rel.From("users"), rel.BatchSize(500))
)

// make sure iterator is closed after process is finish.
defer iter.Close()
for {
    // retrieve next user.
    if err := iter.Next(&user); err != nil {
        if err == io.EOF {
            break
        }

        // handle error
        return err
    }

    // process user
    SendPromotionEmail(&user)
}
users := make([]User, 5)
repo.ExpectIterate(rel.From("users"), rel.BatchSize(500)).Result(users)
repo.ExpectIterate(rel.From("users"), rel.BatchSize(500)).ConnectionClosed()

Native SQL Query

REL allows querying using native SQL query, this is especially useful when using complex query that cannot be covered with the query builder.

Retrieve a book using native sql query:

var book Book
sql := rel.SQL("SELECT id, title, price, orders = (SELECT COUNT(t.id) FROM [transactions] t WHERE t.book_id = b.id) FROM books b where b.id=?", 1)
err := repo.Find(ctx, &book, sql)
var book Book
sql := rel.SQL("SELECT id, title, price, orders = (SELECT COUNT(t.id) FROM [transactions] t WHERE t.book_id = b.id) FROM books b where b.id=?", 1)
repo.ExpectFind(sql).Result(book)

Last update: 2022-06-25