Query Builder

Oro has three query entries:

db.Use[Product]()
db.Table("products")
db.Raw("select * from products where id = ?", id)

Model queries use Go field names. Table and Raw queries use database column names or SQL expressions.

Model query

product, err := db.Use[Product]().Where("Code", "P001").First(ctx)

Common methods:

Table query

rows, err := db.Table("products").Where("price", ">=", 100).Get(ctx)

Table returns oro.Map by default. Use MapTo[T]() for DTOs:

views, err := db.Table("products").
    Select("id", "code", "price").
    MapTo[ProductView]().
    Get(ctx)

Raw query

rows, err := db.Raw("select * from "+db.TableName("products")+" where code = ?", code).Get(ctx)

Raw supports First, Get, MapTo[T], Stream, Exec, Cache, CacheKey, CacheTags, and Timeout.

Raw SQL does not automatically apply table prefixes. Use db.TableName.

Select

rows, err := db.Table("orders").
    Select(
        "status",
        oro.Count("*").As("total"),
        oro.Sum("amount").As("amount_sum"),
        oro.Raw("max(created_at) as last_created_at"),
    ).
    GroupBy("status").
    Get(ctx)

Model Select uses Go fields:

products, err := db.Use[Product]().Select("ID", "Code", "Price").Get(ctx)

Use oro.As(field, alias) for aliases.

Group and Having

rows, err := db.Table("orders").
    Select("status", oro.Count("*").As("total")).
    GroupBy("status").
    HavingRaw("count(*) > ?", 10).
    Get(ctx)

Having methods include Having, HavingColumn, HavingIn, HavingExists, and HavingRaw.

Join

Join conditions are expressed with a callback:

rows, err := db.Table("orders").As("o").
    LeftJoin("users", func(j *oro.Join) {
        j.As("u").OnColumn("u.id", "o.user_id")
    }).
    Where("o.status", "paid").
    Get(ctx)

Join source can be a table name or oro.Query(...).As(alias).

latestOrders := db.Table("orders").
    Select("user_id", oro.Raw("max(id) as last_order_id")).
    GroupBy("user_id")

rows, err := db.Table("users").As("u").
    LeftJoin(oro.Query(latestOrders).As("lo"), func(j *oro.Join) {
        j.OnColumn("lo.user_id", "u.id")
    }).
    Get(ctx)

Subqueries

paidOrders := db.Table("orders").Select("user_id").Where("status", "paid")

users, err := db.Table("users").WhereIn("id", oro.Query(paidOrders)).Get(ctx)
rows, err := db.From(oro.Query(paidOrders).As("po")).Get(ctx)

Terminal methods