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:

Example

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

Mock

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

Retrieve all books:

Example

var books []Book
err := repo.FindAll(ctx, &books)

Mock

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:

Example

var books []Book
err := repo.FindAll(ctx, &books, rel.Eq("available", true))

Mock

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:

Example

var books []Book
err := repo.FindAll(ctx, &books, where.Eq("available", true))

Mock

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

Use fragment to specify custom SQL query:

Example

var books []Book
err := repo.FindAll(ctx, &books, where.Fragment("available=?", true))

Mock

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:

Example

var books []Book
err := repo.FindAll(ctx, &books, rel.And(rel.Eq("available", true), rel.Or(rel.Gte("price", 100), rel.Eq("discount", true))))

Mock

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:

Example

var books []Book
err := repo.FindAll(ctx, &books, rel.Eq("available", true).And(rel.Gte("price", 100).OrEq("discount", true)))

Mock

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):

Example

var books []Book
err := repo.FindAll(ctx, &books, where.Eq("available", true).And(where.Gte("price", 100).OrEq("discount", true)))

Mock

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:

Example

var books []Book
err := repo.FindAll(ctx, &books, where.Lt("price", rel.Select("AVG(price)").From("books")))

Mock

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:

Example

var books []Book
err := repo.FindAll(ctx, &books, rel.NewSortAsc("updated_at"))

Mock

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:

Example

var books []Book
err := repo.FindAll(ctx, &books, sort.Asc("updated_at"))

Mock

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:

Example

var books []Book
err := repo.FindAll(ctx, &books, rel.Where(where.Eq("available", true)).SortAsc("updated_at"))

Mock

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:

Example

var books []Book
err := repo.FindAll(ctx, &books, where.Eq("available", true), sort.Asc("updated_at"))

Mock

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:

Example

var books []Book
err := repo.FindAll(ctx, &books, rel.Select("id", "title"))

Mock

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:

Example

var books []Book
err := repo.FindAll(ctx, &books, rel.From("ebooks"))

Mock

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

Chain the query with select:

Example

var books []Book
err := repo.FindAll(ctx, &books, rel.Select("id", "title").From("ebooks"))

Mock

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:

Example

var books []Book
err := repo.FindAll(ctx, &books, rel.Limit(10), rel.Offset(20))

Mock

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

As a chainable api:

Example

var books []Book
err := repo.FindAll(ctx, &books, rel.Select().Limit(10).Offset(20))

Mock

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:

Example

// 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"))

Mock

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 automatically load the association to struct. If you want to load association on a struct, try JoinAssoc or better 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:

Example

var transactions []Transaction
err := repo.FindAll(ctx, &transactions, rel.Join("books").Where(where.Eq("books.name", "REL for Dummies")))

Mock

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:

Example

var transactions []Transaction
err := repo.FindAll(ctx, &transactions, rel.JoinOn("books", "transactions.book_id", "books.id"))

Mock

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

Syntactic sugar also available for join:

Example

var transactions []Transaction
err := repo.FindAll(ctx, &transactions, join.On("books", "transactions.book_id", "books.id"))

Mock

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

Joining table with custom join mode:

Example

var transactions []Transaction
err := repo.FindAll(ctx, &transactions, rel.JoinWith("LEFT JOIN", "books", "transactions.book_id", "books.id"))

Mock

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:

Example

var transactions []Transaction
err := repo.FindAll(ctx, &transactions, rel.Joinf("JOIN `books` ON `transactions`.`book_id`=`books`.`id`"))

Mock

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:

Example

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

Mock

var book Book
repo.ExpectFind(where.Eq("id", 1), rel.Lock("FOR UPDATE")).Result(book)

Retrieve and lock a row using predefined lock alias:

Example

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

Mock

var book Book
repo.ExpectFind(where.Eq("id", 1), rel.ForUpdate()).Result(book)

Retrieve and lock a row using chained query:

Example

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

Mock

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:

Example

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

Mock

repo.ExpectAggregate(rel.From("books").Where(where.Eq("available", true)), "count", "id").Result(5)

Count all available books using count:

Example

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

Mock

repo.ExpectCount("books").Result(7)

Count all available books using count:

Example

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

Mock

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:

Example

var books []Book
count, err := repo.FindAndCountAll(ctx, &books, rel.Where(where.Like("title", "%dummies%")).Limit(10).Offset(10))

Mock

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).

Example

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)
}

Mock

users := make([]User, 5)
repo.ExpectIterate(rel.From("users"), rel.BatchSize(500)).Result(users)

Mock Error

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:

Example

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)

Mock

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: 2024-03-28