Queries

For select data from storage, D3 provides Query structures.

Querying entities

To extract some type of entities from storage, you need to use a special query for this entity. To create such query use repository.Select method:

// create query for users
adultQuery := userRepository.Select().Where("age", ">", 18)
// extract adult users
adultUsers, err := userRepository.FindAll(ctx, userQuery)

Where clause

For use WHERE clause use methods Query.Where, Query.AndWhere, Query.OrWhere, Query.AndNestedWhere and Query.OrNestedWhere. AndWhere / OrWhere adds conditions with OR or AND operators. AndNestedWhere / OrNestedWhere add nested conditions (surrounded by parentheses) to WHERE clause with OR or AND operator.

Example:

userRepository.Select().Where("age", ">", 18).OrWhere("name", "=", "Sam")
    .AndNestedWhere(func(q *query.Query) {
        q.Where("id", "=", 1).OrWhere("id", "=", 2)
    })

Generate:

SELECT ... FROM users WHERE ((age > $1 OR name = $2) AND (id = $3 OR id = $4))

Fetch related entities

Using Query.With method you can tell D3 orm to fetch related entities in one query with the owner entity. D3 will use left join to fetch related data. This can be useful if you do not want containers to generate additional queries (as an example: when calling Get on lazy relation). A Query.With method calling with full entity name, or with short entity name if the owner and related entity in one package.

Example:

query := userRepository.Select().With("myModule/myPkg/Profile")
usersWithProfile, err := userRepository.FindAll(query)

Generate:

SELECT users .id as "users .id", ..., profile.id as "profile.id", ... FROM users LEFT JOIN profile ON profile.user_id = user.id

Union clause

Use query.Union method if you want to join several sets of entities in one query.

Example:

userRepository.Select().Where("age", "=", 10).Union(userRepository.Select().Where("age", "=", 15))

Generate:

SELECT ... FROM users WHERE age = $1 UNION SELECT ... FROM user WHERE age = $2

Offset and limit

Use query.Offset and query.Limit methods for limit entities in sample and offset. Example:

userRepository.Select().Offset(5).Limit(5)

Generate:

SELECT ... FROM users OFFSET 5 LIMIT 5

Order by clause

For order entities in sample use query.OrderBy method. Example:

userRepository.Select().OrderBy("age DESC", "name ASC")

Generate:

SELECT ... FROM users ORDER BY age DESC, name ASC

RAW Queries

For querying data as an array of rows (where each row is a map of field names and values) from a database, D3 provides special API:

// create query for scalar data
adultQuery := query.New().Select("*").From("users").Where("age", ">", 18)
// extract data as slice of rows
result, err := orm.Session(ctx).Execute(adultQuery)

Besides that you can use more clauses:

Join

q := query.New().Select("*").From("users").Join(query.JoinInner, "profile", "users.id=profile.user_id")
result, err := orm.Session(ctx).Execute(q)

Generate:

SELECT * FROM users INNER JOIN profile ON users.id=profile.user_id

Group By

q := query.New().Select("age", "count(*)").From("users").GroupBy("age")
result, err := orm.Session(ctx).Execute(q)

Generate:

SELECT age, count(*) FROM users GROUP BY age

Having

q := query.New().Select("age").From("q_user").GroupBy("age").Having("count(*)", ">", 1)
result, err := orm.Session(ctx).Execute(q)

Generate:

SELECT age FROM users GROUP BY age HAVING count(*) > 1