# Introduction

GoooQo is a database access framework implemented in Go, based on OQM techniques. It relies entirely on objects to construct various database query statements, eliminating boilerplate code associated with traditional ORM frameworks, and assists developers in achieving automated database access operations.

The first three Os in the name GoooQo stands for the three major object concepts in OQM:

* `Entity Object` is used to map the static part in the SQL statements, such as the table name and column names;
* `Query Object` is used to map the dynamic part of the SQL statements, such as filter conditions, pagination, and sorting;
* `View Object` is used to map the static part in complex query statements, such as table names, column names, nested views, and the GROUP BY clause.

### The Dynamic Combination of Query Conditions ​ 

A query interface with *n* query conditions can generate 2^n unique query requests by combining different parameter values to construct corresponding query statements. 

Similarly, an object with *n* fields has 2^n different assignment combinations. 

The 2^n assignment combinations of object instances are mapped exactly to 2^n combinations of query conditions.

OQM technology leverages this relationship and introduces the query object mapping method, where each field of the query object corresponds to a query condition, and query clauses are constructed based on field assignments. 

In this way, the logic of building query clauses can be separated from the logic of constructing query parameters and encapsulated as common functions in a framework, allowing developers to focus solely on the definition and assignment of query objects.

### The Definition of the Query​ Object

A basic query condition consists of a column name, a comparison operator, and a value. The fields in the entity correspond to the column names. When we use aliases to represent comparison operators and combine them with the fields in an entity object as suffixes, we get the fields of the query object. Therefore, we have the following formula:

$$
Entity + Suffix = Query
$$

Here is an example:

<figure><picture><source srcset="/files/DYsfKMz2of8cHIxEzbLJ" media="(prefers-color-scheme: dark)"><img src="/files/Hffmhax9qiPz38e0vjHs" alt=""></picture><figcaption><p>Entity + Suffix = Query</p></figcaption></figure>

### Community

Gitter: <https://gitter.im/doytowin/goooqo>

Discussion: <https://github.com/doytowin/goooqo/discussions>


# Quickstart

### Init Project

First, use `go mod init` to initialize the project and add GoooQo by:

```
go get -u github.com/doytowin/goooqo/rdb
```

Then, initialize the database connection and transaction manager as follows:

```go
package main

import (
	"database/sql"
	"github.com/doytowin/goooqo/rdb"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db := rdb.Connect("app.properties")
	defer rdb.Disconnect(db)
	tm := rdb.NewTransactionManager(db)

	//...
}
```

### Build DataAccess

Suppose we have the following user table in `test.db`:

<table><thead><tr><th>id</th><th>name</th><th data-type="number">score</th><th>memo</th></tr></thead><tbody><tr><td>1</td><td>Alley</td><td>80</td><td>Good</td></tr><tr><td>2</td><td>Dave</td><td>75</td><td>Well</td></tr><tr><td>3</td><td>Bob</td><td>60</td><td></td></tr><tr><td>4</td><td>Tim</td><td>92</td><td>Great</td></tr><tr><td>5</td><td>Emy</td><td>100</td><td>Great</td></tr></tbody></table>

We define an entity object and a query object for the table:

{% code title="user.go" %}

```go
package main

import (
	. "github.com/doytowin/goooqo/core"
)

type UserEntity struct {
	Int64Id
	Name  *string `json:"name"`
	Score *int    `json:"score"`
	Memo  *string `json:"memo"`
}

func (u UserEntity) GetTableName() string {
	return "t_user"
}

type UserQuery struct {
	PageQuery
	ScoreLt   *int
	MemoStart *string
	// ...
}
```

{% endcode %}

The fields of the entity object correspond to the columns of the table, and the fields of the query object are based on the query conditions of the requirements.

Then we define a `userDataAccess` to access the table:

```go
userDataAccess := rdb.NewTxDataAccess[UserEntity](tm)

userQuery := UserQuery{PageQuery: PageQuery{Page: 5, Size: 10}, ScoreLt: P(80)}
userEntities, err := userDataAccess.Query(ctx, userQuery)
```

This will generate and execute the following SQL:

```sql
SELECT id, name, score, memo FROM t_user WHERE score < ? LIMIT 10 OFFSET 40
```

### Related Documents

<table data-view="cards"><thead><tr><th></th><th></th><th></th><th data-hidden data-card-target data-type="content-ref"></th></tr></thead><tbody><tr><td></td><td>Define an entity object</td><td></td><td><a href="/pages/OUsrtuabLBHBSIEnCOGx">/pages/OUsrtuabLBHBSIEnCOGx</a></td></tr><tr><td></td><td>Define a query object</td><td></td><td><a href="/pages/DbcpkkQ6xQmUBzjz98zT">/pages/DbcpkkQ6xQmUBzjz98zT</a></td></tr><tr><td></td><td>Create DataAccess</td><td></td><td><a href="/pages/81sqmRGj2KUyCouwhl3f">/pages/81sqmRGj2KUyCouwhl3f</a></td></tr><tr><td></td><td>Database Connection</td><td></td><td><a href="/pages/s9zsiS7aIsQ0s5nXzn76">/pages/s9zsiS7aIsQ0s5nXzn76</a></td></tr><tr><td></td><td>Transaction</td><td></td><td><a href="/pages/f0VRvrkPkifAppOc5FXp">/pages/f0VRvrkPkifAppOc5FXp</a></td></tr></tbody></table>


# Connection

## Usage

Developers can create a database connection on their own:

```go
package main

import (
	"database/sql"
	_ "github.com/mattn/go-sqlite3"
)

func main() {	
	db, err := sql.Open("sqlite3", "./test.db")
	if err == nil {
		defer db.Close()
	}
	//...
}
```

or use a property file:

```go
package main

import (
	"github.com/doytowin/goooqo/rdb"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db := rdb.Connect("app-sqlite3.properties")
	defer rdb.Disconnect(db)
	//...
}
```

## Property Format

The property file contains the parameters for different databases.

{% code title="app-sqlite3.properties" %}

```properties
driver=sqlite3
data_source=./test.db
```

{% endcode %}

{% code title="app-mysql.properties" %}

```properties
driver=mysql
mysql_url=tcp(localhost:3306)/demo?charset=utf8mb4&parseTime=true
mysql_username=root
mysql_password=root
```

{% endcode %}

More properties will be supported after the [dialect](/advanced/dialect) feature is ready.


# Transaction

## Definition

After creating the database connection, we use the database connection to create a transaction manager.

Transaction management in GoooQo is accomplished through the cooperation of TransactionManager and TransactionContext (TC for short).

```go
package core

import (
	"context"
	"database/sql/driver"
)

type TransactionManager interface {
	GetClient() any
	StartTransaction(ctx context.Context) (TransactionContext, error)
	SubmitTransaction(ctx context.Context, callback func(tc TransactionContext) error) error
}

type TransactionContext interface {
	context.Context
	driver.Tx
	Parent() context.Context
	SavePoint(name string) error
	RollbackTo(name string) error
}
```

The method `TransactionManager#StartTransaction` is responsible for starting a transaction and returning TC; TC combines `driver.Tx` and is responsible for transaction commit and rollback.

The `TxDataAccess` interface combines `TransactionManager` and `DataAccess`, which can conveniently manage transactions while providing database operations.

```go
type TxDataAccess[E Entity] struct {
	TransactionManager
	DataAccess[E]
}
```

## Usages

Use the database connection `db` to create a TransactionManager `tm`:

<pre class="language-go"><code class="lang-go"><strong>db := rdb.Connect("app.properties")
</strong>defer rdb.Disconnect(db)
tm := rdb.NewTransactionManager(db)
</code></pre>

Use `StartTransaction` to start a transaction and manually commit or rollback the transaction:

```go
tc, err := tm.StartTransaction(ctx)
userQuery := UserQuery{ScoreLt: P(80)}
cnt, err := userDataAccess.DeleteByQuery(tc, userQuery)
if err != nil {
	err = RollbackFor(tc, err)
	return 0
}
err = tc.Commit()
return cnt
```

Or use `SubmitTransaction` to submit the transaction via callback:

```go
err := tm.SubmitTransaction(ctx, func(tc TransactionContext) (err error) {
    // transaction body
    return
})
```


# CRUD

## Definition

After creating the transaction manager, we can create a `TxDataAccess` for each entity for CRUD operations:

```go
userDataAccess := rdb.NewTxDataAccess[UserEntity](tm)
```

The method `NewTxDataAccess` returns a `TxDataAccess` instance, which consists of the `DataAccess` interface for CRUD operations and the `TransactionManager` interface for transaction operations.

```go
package core

type DataAccess[E Entity] interface {
    Get(ctx context.Context, id any) (*E, error)
    Delete(ctx context.Context, id any) (int64, error)
    Query(ctx context.Context, query Query) ([]E, error)
    Count(ctx context.Context, query Query) (int64, error)
    DeleteByQuery(ctx context.Context, query Query) (int64, error)
    Page(ctx context.Context, query Query) (PageList[E], error)
    Create(ctx context.Context, entity *E) (int64, error)
    CreateMulti(ctx context.Context, entities []E) (int64, error)
    Update(ctx context.Context, entity E) (int64, error)
    Patch(ctx context.Context, entity E) (int64, error)
    PatchByQuery(ctx context.Context, entity E, query Query) (int64, error)
}

type TxDataAccess[E Entity] interface {
    TransactionManager
    DataAccess[E]
}

type PageList[D any] struct {
    List  []D   `json:"list"`
    Total int64 `json:"total"`
}
```

The parameter `ctx` can be a normal `context.Context` or a `TransactionContext` for transactions.

For the definition of `Entity`, check:

{% content-ref url="/spaces/caxovyAjMzGHxhZgzo8F/pages/OUsrtuabLBHBSIEnCOGx" %}
[Entity Object](/entity-mapping/entity-object)
{% endcontent-ref %}

For the definition of `Query`, check:

{% content-ref url="/spaces/caxovyAjMzGHxhZgzo8F/pages/DbcpkkQ6xQmUBzjz98zT" %}
[Query Object](/query-mapping/query-object)
{% endcontent-ref %}

## Usages

The following examples are based on `UserEntity` and `UserQuery`:

```go
type UserEntity struct {
    Int64Id
    Name    *string `json:"name,omitempty"`
    Score   *int    `json:"score,omitempty"`
    Memo    *string `json:"memo,omitempty"`
    Deleted *bool   `json:"deleted,omitempty"`
}

func (u UserEntity) GetTableName() string {
    return "t_user"
}

type UserQuery struct {
    PageQuery
    IdGt     *int64
    IdIn     *[]int64
    ScoreLt  *int
    MemoNull *bool
    MemoLike *string
    Deleted  *bool
    UserOr   *[]UserQuery

    Account    *string    `condition:"(username = ? OR email = ?)"`
    ScoreLtAvg *UserQuery `subquery:"select avg(score) from t_user"`
    ScoreLtAny *UserQuery `subquery:"SELECT score FROM t_user"`
    ScoreLtAll *UserQuery `subquery:"select score from UserEntity"`
    ScoreGtAvg *UserQuery `select:"avg(score)" from:"UserEntity"`

    ScoreInScoreOfUser    *UserQuery
    ScoreGtAvgScoreOfUser *UserQuery
}
```

### Get

```go
user, err := userDataAccess.Get(ctx, 3)
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE id = ?" args="[3]"
```

### Query

```go
userQuery := UserQuery{ScoreLt: P(80)}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE score < ?" args="[80]"

userQuery := UserQuery{PageQuery: PageQuery{PageSize: P(20), 
    Sort: P("id,desc;score")}, MemoLike: P("Great")}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE memo LIKE ? ORDER BY id DESC, score LIMIT 20 OFFSET 0" args="[Great]"

userQuery := UserQuery{IdIn: &[]int64{1, 4, 12}, Deleted: P(true)}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE id IN (?, ?, ?) AND deleted = ?" args="[1 4 12 true]"

userQuery := UserQuery{UserOr: &[]UserQuery{{IdGt: P(int64(10)), 
    MemoNull: P(true)}, {ScoreLt: P(80), MemoLike: P("Good")}}}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (id > ? AND memo IS NULL OR score < ? AND memo LIKE ?)" args="[10 80 Good]"

userQuery := UserQuery{ScoreGtAvg: &UserQuery{Deleted: P(true)},
     ScoreLtAny: &UserQuery{}}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE score > (SELECT avg(score) FROM t_user WHERE deleted = ?) 
// AND score < ANY(SELECT score FROM t_user)" args="[true]"

userQuery := UserQuery{Account: P("John")}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (username = ? OR email = ?)" args="[John John]"
```

### Count

```go
userQuery := UserQuery{ScoreLt: P(60)}
cnt, err := userDataAccess.Count(ctx, userQuery)
// SQL="SELECT count(0) FROM t_user WHERE score < ?" args="[60]"
```

### Page

```go
userQuery := UserQuery{PageQuery: PageQuery{PageSize: P(20)}, ScoreLt: P(80)}
page, err := userDataAccess.Page(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE score < ? LIMIT 20 OFFSET 0" args="[80]"
// SQL="SELECT count(0) FROM t_user WHERE score < ?" args="[80]"
```

### Delete

```go
tc, _ := tm.StartTransaction(tc)
cnt, err := userDataAccess.Delete(tc, 3)
// SQL="DELETE FROM t_user WHERE id = ?" args="[3]"
```

### DeleteByQuery

```go
userQuery := UserQuery{ScoreLt: P(80)}
cnt, err := userDataAccess.DeleteByQuery(tc, userQuery)
// SQL="DELETE FROM User WHERE score < ?" args="[80]"
```

### Create

```go
entity := UserEntity{Name: P("John"), Score: P(90), Deleted: P(false)}
id, err := userDataAccess.Create(tc, &entity)
// SQL="INSERT INTO t_user (name, score, memo, deleted) VALUES (?, ?, ?, ?)" args="[John 90 <nil> false]"
```

### CreateMulti

```go
entities := []UserEntity{{Name: P("John"), Score: P(90), Memo: P("Great"), Deleted: P(false)}, {Name: P("Alex"), Score: P(55)}}
cnt, err := userDataAccess.CreateMulti(tc, entities)
// SQL="INSERT INTO t_user (name, score, memo, deleted) VALUES (?, ?, ?, ?), (?, ?, ?, ?)" args="[John 90 Great false Alex 55 <nil> <nil>]"
```

### Update

Update all fields by id.

```go
entity := UserEntity{Int64Id: NewInt64Id(2), Score: P(90), Memo: P("Great")}
cnt, err := userDataAccess.Update(tc, entity)
// SQL="UPDATE t_user SET score = ?, memo = ? WHERE id = ?" args="[90 Great 2]"
```

### Patch

Update non-nil fields by id.

```go
entity := UserEntity{Int64Id: NewInt64Id(2), Score: P(90)}
cnt, err := userDataAccess.Patch(tc, entity)
// SQL="UPDATE t_user SET score = ? WHERE id = ?" args="[90 2]"
```

### PatchByQuery

Update non-nil fields by query conditions.

```go
entity := UserEntity{Memo: P("Add Memo")}
query := UserQuery{MemoNull: P(true)}
cnt, err := userDataAccess.PatchByQuery(tc, entity, query)
// SQL="UPDATE t_user SET memo = ? WHERE memo IS NULL" args="[Add Memo]"
```


# Association Service

Available in v0.2.3


# Entity Object

### Example

```go
import (
	. "github.com/doytowin/goooqo"
)

type UserEntity struct {
	Int64Id
	Name  *string `json:"name,omitempty"`
	Score *int    `json:"score,omitempty"`
	Memo  *string `json:"memo,omitempty"`
}

func (u UserEntity) GetTableName() string {
	return "t_user"
}
```

### Definition

An entity object is used to provide the table name and column names for CRUD statements construction in GoooQo.

The entity struct needs to implement the following interface:

```go
package core

type Entity interface {
	GetId() any

	// SetId set id to self.
	// self: the pointer points to the current entity.
	// id: type could be int, int64, or string so far.
	SetId(self any, id any) error
}


package rdb

import "github.com/doytowin/goooqo/core"

type RdbEntity interface {
	core.Entity
	GetTableName() string
}
```

* `GetId` is used to build a `Update` statement.
* `SetId` is used to set the generated ID to an entity.
* `GetTableName` is used to provide the table name corresponding to the entity.
* Each field in the entity needs to correspond to a column in the table.

GoooQo provides two `Entity` implementations, `IntId` and `Int64Id`, to simplify entity definition.

The CRUD statements of the `UserEntity` in the example are:

```sql
SELECT id, name, score, memo FROM t_user；
INSERT INTO t_user (name, score, memo) VALUES (?, ?, ?)
UPDATE t_user SET name = ?, score = ?, memo = ? WHERE id = ?;
DELETE FROM t_user WHERE id = ?;
```


# Related Entities

Available in v0.2.2


# Query Object

### Example

```go
type UserQuery struct {
    PageQuery
    IdGt     *int64
    IdIn     *[]int64
    ScoreLt  *int
    MemoNull *bool
    MemoLike *string
    Deleted  *bool
    UserOr   *[]UserQuery
    
    ScoreGtAvg *UserQuery `subquery:"select:avg(score),from:UserEntity"`
    ScoreLtAny *UserQuery `subquery:"select:score,from:UserEntity"`
    ScoreLtAll *UserQuery `subquery:"select:score,from:UserEntity"`
}
```

### Query Interface

The query object needs to implement the `Query` interface for the construction of paging clause and sorting clause:&#x20;

```go
package core

type Query interface {
    GetPageNumber() int
    GetPageSize() int
    CalcOffset() int
    GetSort() *string
    NeedPaging() bool
}
```

The `PageQuery` struct provides a standard implementation for the query structs.&#x20;

{% content-ref url="/spaces/caxovyAjMzGHxhZgzo8F/pages/WhXxQxwyMwh5iXBKypK7" %}
[Page Query](/query-mapping/page-query)
{% endcontent-ref %}

### Fields Definition

The query object is used to map the dynamic part of the SQL statements, such as filter conditions, pagination, and sorting.

Each field in the query object is used to map a query condition.

Check the following docs for how to define the fields in GoooQo:

{% content-ref url="/spaces/caxovyAjMzGHxhZgzo8F/pages/Ku4ZcaLqf5MB6ZMsvyp6" %}
[Predicate-Suffix Field](/query-mapping/query-object/predicate-suffix-field)
{% endcontent-ref %}

{% content-ref url="/spaces/caxovyAjMzGHxhZgzo8F/pages/ifm6kLL2owkfw9DC6rpx" %}
[Logic-Suffix Field](/query-mapping/query-object/logic-suffix-field)
{% endcontent-ref %}

{% content-ref url="/spaces/caxovyAjMzGHxhZgzo8F/pages/JDwZe7p4MFhiM23xD1Id" %}
[Subquery Field](/query-mapping/query-object/subquery-field)
{% endcontent-ref %}

{% content-ref url="/spaces/caxovyAjMzGHxhZgzo8F/pages/Xa80wuFVpJgGOm8kFJ6Y" %}
[E-R Query Field](/query-mapping/query-object/er-query-field)
{% endcontent-ref %}

{% content-ref url="/spaces/caxovyAjMzGHxhZgzo8F/pages/v1icJZJQQGyYRIvaZ2F8" %}
[Custom Condition Field](/query-mapping/query-object/custom-condition-field)
{% endcontent-ref %}


# Predicate-Suffix Field

## Mapping

GoooQo uses a predicate suffix mapping method to map fields in query objects to basic query conditions. Each basic query condition consists of a column name, a comparison operator, and a value. The format of the field name in the query object is a column name plus one of the comparison operator alias. All assigned fields in a query instance will be mapped to the corresponding query conditions and spliced ​​into a query clause.

### Examples

Here are two examples of the predicate-suffix field mapping:

```go
userQuery := UserQuery{Deleted: P(true)}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE deleted = ?" args="[true]"

userQuery := UserQuery{IdIn: &[]int64{1, 4, 12}, Deleted: P(true)}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE id IN (?, ?, ?) AND deleted = ?" args="[1 4 12 true]"
```

Check the [predicate suffix table](#predicate-suffix-table) for all supported suffixes by GoooQo.

## Predicate Suffix Table

<table><thead><tr><th>Suffix</th><th>Field Name</th><th>Value</th><th>SQL Condition</th><th data-hidden>MongoDB Condition</th></tr></thead><tbody><tr><td>(EMPTY)</td><td>id</td><td>5</td><td>id = 5</td><td>{"id":5}</td></tr><tr><td>Eq</td><td>idEq</td><td>5</td><td>id = 5</td><td>{"idEq":5}</td></tr><tr><td>Not</td><td>idNot</td><td>5</td><td>id != 5</td><td>{"idNot":{"$ne":5}}</td></tr><tr><td>Ne</td><td>idNe</td><td>5</td><td>id &#x3C;> 5</td><td>{"idNe":{"$ne":5}}</td></tr><tr><td>Gt</td><td>idGt</td><td>5</td><td>id > 5</td><td>{"idGt":{"$gt":5}}</td></tr><tr><td>Ge</td><td>idGe</td><td>5</td><td>id >= 5</td><td>{"idGe":{"$gte":5}}</td></tr><tr><td>Lt</td><td>idLt</td><td>5</td><td>id &#x3C; 5</td><td>{"idLt":{"$lt":5}}</td></tr><tr><td>Le</td><td>idLe</td><td>5</td><td>id &#x3C;= 5</td><td>{"idLe":{"$lte":5}}</td></tr><tr><td>NotIn</td><td>idNotIn</td><td>[1,2,3]</td><td>id NOT IN (1,2,3)</td><td>{"id":{"$nin":[1, 2, 3]}}</td></tr><tr><td>In</td><td>idIn</td><td>[1,2,3]</td><td>id IN (1,2,3)</td><td>{"id":{"$in":[1, 2, 3]}}</td></tr><tr><td>Null</td><td>memoNull</td><td>false</td><td>memo IS NOT NULL</td><td>{"memo":{"$not":{"$type", 10}}}</td></tr><tr><td>Null</td><td>memoNull</td><td>true</td><td>memo IS NULL</td><td>{"memo":{"$type", 10}}</td></tr><tr><td>NotLike</td><td>nameNotLike</td><td>"arg"</td><td>name NOT LIKE '%arg%'</td><td>{"name":{"$not":{"$regex":"arg"}}}</td></tr><tr><td>Like</td><td>nameLike</td><td>"arg"</td><td>name LIKE '%arg%'</td><td>{"name":{"$regex":"arg"}}</td></tr><tr><td>NotStart</td><td>nameNotStart</td><td>"arg"</td><td>name NOT LIKE 'arg%'</td><td>{"name":{"$not":{"$regex":"^arg"}}}</td></tr><tr><td>Start</td><td>nameStart</td><td>"arg"</td><td>name LIKE 'arg%'</td><td>{"name":{"$regex":"^arg"}}</td></tr><tr><td>NotEnd</td><td>nameNotEnd</td><td>"arg"</td><td>name NOT LIKE '%arg'</td><td>{"name":{"$not":{"$regex":"arg$"}}}</td></tr><tr><td>End</td><td>nameEnd</td><td>"arg"</td><td>name LIKE '%arg'</td><td>{"name":{"$regex":"arg$"}}</td></tr><tr><td>NotContain</td><td>nameNotContain</td><td>"arg"</td><td>name NOT LIKE '%arg%’</td><td>{"name":{"$not":{"$regex":"arg"}}}</td></tr><tr><td>Contain</td><td>nameContain</td><td>"arg"</td><td>name LIKE '%arg%’</td><td>{"name":{"$regex":"arg"}}</td></tr><tr><td>Rx</td><td>nameRx</td><td>"arg\d"</td><td>name REGEXP 'arg\d’</td><td>{"name":{"$regex":"arg\d"}}</td></tr></tbody></table>


# Logic-Suffix Field

## Mapping

By default, the query conditions corresponding to the fields of the query object are connected by AND in GoooQo.&#x20;

### Or Suffix

If we want to use the logical operator OR to connect the query conditions, we need to define a struct or array with the suffix Or in the query object.

GoooQo supports the following three definitions:

```go
type UserQuery struct {
	PageQuerygo
	//...
	NameStartOr *[]string
	UserOr      *UserQuery
	UsersOr     *[]UserQuery
}
```

#### NameStartOr \*\[]string

```go
userQuery := UserQuery{NameStartOr: &[]string{"Bob", "John", "Tim"}}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (name LIKE ? OR name LIKE ? OR name LIKE ?)" args="[Bob% John% Tim%]"
```

#### UserOr \*UserQuery

```go
userQuery := UserQuery{UserOr: &UserQuery{IdIn: &[]int64{1, 4, 12}, Deleted: P(true)}}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (id IN (?, ?, ?) OR deleted = ?)" args="[1 4 12 true]"
```

#### UsersOr \*\[]UserQuery

```go
userQuery := UserQuery{UsersOr: &[]UserQuery{
	{IdIn: &[]int64{1, 4, 12}, Deleted: P(true)},
	{IdGt: P(int64(10)), Deleted: P(false)},
}}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user
// WHERE (id IN (?, ?, ?) AND deleted = ? OR id > ? AND deleted = ?)"
// args="[1 4 12 true 10 false]"
```

### And Suffix

When the name of a field ends with And, the logical operator connecting multiple query conditions is AND.

```go
type UserQuery struct {
	PageQuerygo
	//...
	UserOr      *UserQuery
	UserAnd     *UserQuery
}
```

#### UserAnd \*UserQuery

```go
userQuery := UserQuery{ScoreLt: P(80), UserOr: &UserQuery{Deleted: P(true),
    UserAnd: &UserQuery{IdIn: &[]int64{1, 4, 12}, Deleted: P(false)}}}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user
// WHERE score < ? AND (deleted = ? OR id IN (?, ?, ?) AND deleted = ?)"
// args="[80 true 1 4 12 false]"
```

## Articles

<https://blog.doyto.win/post/goooqo-or-clause-en/>


# Subquery Field

## Mapping

For a general subquery,`score > (SELECT avg(score) FROM t_user WHERE deleted = ?)`, is divided into three parts in OQM for mapping:

* score >
* SELECT avg(score) FROM t\_user
* WHERE clause

The first part `score >`can be mapped using the field name `scoreGtXxx`, just like a normal predicate suffix field. The string defined after the predicate suffix is ​​only used to distinguish duplicate field names and will be ignored during mapping.

The second part contains a column and a table name, which are static and unchanging. GoooQo provides two tags to save this information: One is `subquery`, which is used to define the native subquery statement; The other is the combination of the tags `select` and `from`, which saves the column name and table name respectively.&#x20;

The third part is another WHERE clause, which can be mapped through a query object. Therefore, we define the field type as the corresponding query object and map the value to the WHERE clause in the subquery through the query object mapping method.

### Tags Examples

Here are some examples of subquery field definitions (since v0.2.0):

```go
ScoreLtAvg *UserQuery `subquery:"select avg(score) from t_user"`
ScoreLtAny *UserQuery `subquery:"SELECT score FROM t_user"`
ScoreLtAll *UserQuery `subquery:"select score from UserEntity"`
ScoreGtAvg *UserQuery `select:"avg(score)" from:"UserEntity"`
```

### Field Name Resolution

Since 0.2.0, GoooQo also supports resolving SELECT from the field name.&#x20;

#### Field name format

&#x20;`<column><predicate><[aggregate]column>Of<entity>`

#### Examples

```go
ScoreInScoreOfUser    *UserQuery //score IN (SELECT score FROM t_user WHERE ...)
ScoreGtAvgScoreOfUser *UserQuery //score > (SELECT AVG(score) FROM t_user WHERE ...)
```

In these examples, we need to register a mapping relationship to map `User` to `t_user`:

```go
rdb.RegisterEntity("User", "t_user")
```


# E-R Query Field

## Entity-Relationship and **Abstract Entity Path**

In an Entity-Relationship diagram(ERD), a many-to-many relationship is a type of cardinality that refers to the relationship between two entities, say, A and B, where A may contain a parent instance for which there are many children in B and vice versa. The many-to-many relationship is also transitive. For example, if entity A has a many-to-many relationship with entity B, and entity B has a many-to-many relationship with entity C, then entity A and entity C also have a many-to-many relationship, which is an indirect many-to-many relationship.

Based on the transitivity of many-to-many relationships, the concept of A**bstract Entity Path** is proposed to describe this direct or indirect many-to-many relationship between entities. The abstract entity path uses all entities from one entity to another as nodes to describe the many-to-many relationship between any two entities. For example, the abstract entity path of entity A and entity B is \[A, B], the abstract entity path of entity B and entity A is \[B, A], and the abstract entity path of entity C and entity A is \[C, B, A]. The abstract entity path contains all the information about the relationship between any two entities, so it is used to dynamically generate complex nested query statements.

GoooQo introduces the concept of abstract entity path and defines a label named `entitypath` to represent the relationship between entities. This label is used to query the fields in the query object for querying entity relationships.&#x20;

Example 1. The entity path `entitypath:"user,role"`, based on the predetermined table name format, can generate two entity table names t\_user and t\_role, one intermediate table name a\_user\_and\_role, and two foreign key names user\_id and role\_id, and then generate the query statement:

```sql
SELECT * FROM t_user WHERE id
 IN (SELECT user_id FROM a_user_and_role WHERE role_id 
 IN (SELECT id FROM t_role [WHERE])
)
```

Example 2. The entity path `entitypath:"user,role,perm"` will generate:

```sql
SELECT * FROM t_user WHERE id
 IN (SELECT user_id FROM a_user_and_role WHERE role_id
 IN (SELECT role_id FROM a_role_and_perm WHERE perm_id
 IN (SELECT id FROM t_perm [WHERE])
))
```

## Examples

The table `t_menu` has a column `parent_id` referring to the `id` column itself as a foreign key. The `parent_id` column is used to define the hierarchical parent-child relationship between menu items. The menus are assigned to the users as a system resource via a general RBAC model. Then the entity path from the menu to the user is: `menu,perm,role,user`, which is used to generate nested query statements.

<pre class="language-go"><code class="lang-go">import . "github.com/doytowin/goooqo/core"

type MenuEntity struct {
	IntId
	ParentId *int    `json:"parentId,omitempty"`
	Name     *string `json:"name,omitempty"`
}

<strong>type MenuQuery struct {
</strong>	PageQuery
	Id *int

	// many-to-one:
	// Query the submenus of a specific parent menu:
	// parent_id IN (SELECT id FROM t_menu [WHERE])
	Parent *MenuQuery `entitypath:"menu" localField:"ParentId"`

	// one-to-many:
	// Query the parent menu of a specific submenu:
	// id IN (SELECT parent_id FROM t_menu [WHERE])
	Children *MenuQuery `entitypath:"menu" foreignField:"ParentId"`

	/**
	many-to-many:
	Query the menus accessible to a specific user:
	id IN (SELECT menu_id FROM a_perm_and_menu WHERE perm_id
	   IN (SELECT perm_id FROM a_role_and_perm WHERE role_id
	   IN (SELECT role_id FROM a_user_and_role WHERE user_id 
	   IN (SELECT id FROM t_user [WHERE])
	））)*/
	User *UserQuery `entitypath:"menu,perm,role,user"`
}
</code></pre>


# Custom Condition Field

## Mapping

For types of query conditions that are not currently supported, GoooQo uses the tag `condition` to directly write native SQL conditions:&#x20;

### Example

```go
type UserQuery struct {
    PageQuery
    Account *string `condition:"(username = ? OR email = ?)"`
    //...
}
```

Output SQL:&#x20;

```go
userQuery := UserQuery{Account: P("John")}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user
// WHERE (username = ? OR email = ?)" args="[John John]"
```


# Page Query

## Definition

`PageQuery` is a ​**​generic pagination query parameter structure​**​ designed to standardize common list query requirements such as pagination and sorting. `PageQuery` has three fields, `PageNumber` and `PageSize` is used to build the paging clause, and `Sort` is used to build the sorting clause. `PageQuery` implements the `Query` interface.

```go
package core

type PageQuery struct {
	Page int    `json:"page,omitempty"`
	Size int    `json:"size,omitempty"`
	Sort string `json:"sort,omitempty"`
}
```

## Usage

When defining query objects, embed the `PageQuery` struct through composition to reuse standardized pagination and sorting functionality.

```go
type UserQuery struct {
    PageQuery
    ScoreLt  *int
    MemoNull *bool
    Deleted  *bool
}
```

## Paging

```go
userQuery := UserQuery{PageQuery: PageQuery{}}
users, err := userDataAccess.Query(ctx, userQuery)
//SELECT id, score, memo FROM User

userQuery := UserQuery{PageQuery: PageQuery{Size: 20}}
users, err := userDataAccess.Query(ctx, userQuery)
//SELECT id, score, memo FROM User LIMIT 20 OFFSET 0

// When only PageNumber is set, PageSize will be set to 10
userQuery := UserQuery{PageQuery: PageQuery{Page: 5}}
users, err := userDataAccess.Query(ctx, userQuery)
//SELECT id, score, memo FROM User LIMIT 10 OFFSET 40

userQuery := UserQuery{PageQuery: PageQuery{Page: 3, Size: 50}}
users, err := userDataAccess.Query(ctx, userQuery)
//SELECT id, score, memo FROM User LIMIT 50 OFFSET 100	
```

## Sorting

The `Sort` string should follow `regexp.MustCompile("(?i)(\w+)(,(asC|dEsc))?;?")`

```go
userQuery := UserQuery{PageQuery: PageQuery{Sort: "id,desc;score,asc;memo"}}
users, err := userDataAccess.Query(ctx, userQuery)
//SELECT id, score, memo FROM User ORDER BY id DESC, score ASC, memo
```


# View Object

Will support in v0.3.x.


# Having

Will support in v0.3.x.


# Natural Join

Will support in v0.3.x.


# Outer Join

Will support in v0.3.x.


# Nested View

Will support in v0.3.x.


# Dialect

Available in v0.4.0


# Locking

TBD


# Articles


# GoooQo介绍

GoooQo是一个基于OQM技术的Go语言版的增删查改框架。 ​&#x20;

OQM是一项通过对象构建数据库查询语句的数据库访问技术。

OQM技术采用一种新的方法解决了n个查询条件的动态组合问题，从而使得开发人员仅需要定义和构造查询对象即可实现动态查询语句的构造。

GoooQo名称中Qo即为查询对象，前三个o代表了OQM技术中的三个主要的对象概念：

* `Entity Object` 实体对象用于映射SQL语句中的静态部分，例如表名和列名；
* `Query Object` 查询对象用于映射SQL语句中的动态部分，例如过滤条件、分页子句和排序子句；
* `View Object` 视图对象用于映射复杂查询语句中的静态部分，例如表名、列名、嵌套视图和分组子句。

### 查询条件的动态组合问题 ​&#x20;

一个提供了n个查询条件的查询接口，一共可以有2^n种参数组合的查询请求来构造对应的查询语句。&#x20;

一个定义有n个字段的对象，其实例也有2^n种不同的赋值方式。

通过对象实例的2^n种赋值组合可以映射2^n种查询条件的组合。&#x20;

OQM技术发现并利用这一特性，提出一种将对象的每个字段分别映射为一个查询条件、进而根据字段赋值构造查询子句的方法，称为查询对象映射方法，所提到的对象被称为查询对象。

通过这种方法，我们可以把构造查询参数的逻辑和构造查询子句的逻辑进行分离，从而把构造查询子句的逻辑封装为通用的功能，开发人员只需要关注查询对象的定义和赋值即可。 ​

### 查询条件的定义

一个基本的查询条件由列名、比较运算符和值组成，实体中的字段与列名一一对应。当我们使用别名来表示比较运算符，并把它们作为后缀与实体对象中的字段组合起来，就得到了查询对象的字段。因此得到如下公式：

$$
Entity+Suffix=Query
$$

示例：

<figure><picture><source srcset="/files/VlrQRQKdZvEyeLw1xNuC" media="(prefers-color-scheme: dark)"><img src="/files/BCYCc34ZTeKbsVhsVHAl" alt="Entity+Suffix=Query"></picture><figcaption><p>Entity+Suffix=Query</p></figcaption></figure>

#### 欢迎交流

<div align="left"><figure><img src="/files/1ijHxEJqqndz9llZJEAx" alt="" width="173"><figcaption><p>QQ</p></figcaption></figure></div>


# 快速上手

### 初始化项目

首先，使用`go mod init`初始化项目并添加GoooQo依赖：

```
go get -u github.com/doytowin/goooqo
```

然后，初始化数据库连接和事务管理器：

```go
package main

import (
	"database/sql"
	"github.com/doytowin/goooqo/rdb"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db, _ := sql.Open("sqlite3", "./test.db")

	tm := rdb.NewTransactionManager(db)

	//...
}
```

### 创建数据访问接口

假设我们在`test.db`中有以下用户表：

<table><thead><tr><th>id</th><th>name</th><th data-type="number">score</th><th>memo</th></tr></thead><tbody><tr><td>1</td><td>Alley</td><td>80</td><td>Good</td></tr><tr><td>2</td><td>Dave</td><td>75</td><td>Well</td></tr><tr><td>3</td><td>Bob</td><td>60</td><td></td></tr><tr><td>4</td><td>Tim</td><td>92</td><td>Great</td></tr><tr><td>5</td><td>Emy</td><td>100</td><td>Great</td></tr></tbody></table>

我们为表定义一个实体对象和一个查询对象：

{% code title="user.go" %}

```go
package main

import (
	. "github.com/doytowin/goooqo"
)

type UserEntity struct {
	Int64Id
	Name  *string `json:"name"`
	Score *int    `json:"score"`
	Memo  *string `json:"memo"`
}

func (u UserEntity) GetTableName() string {
	return "t_user"
}

type UserQuery struct {
	PageQuery
	ScoreLt   *int
	MemoStart *string
	// ...
}
```

{% endcode %}

实体对象的字段与表的列相对应，查询对象的字段根据需要构建的查询条件来定义。

然后我们定义一个`userDataAccess`来执行增删查改操作：

```go
userDataAccess := rdb.NewTxDataAccess[UserEntity](tm)

userQuery := UserQuery{PageQuery: PageQuery{PageSize: P(10)}, ScoreLt: P(80)}
userEntities, err := userDataAccess.Query(ctx, userQuery)
```

这将生成并执行以下SQL语句：

```sql
SELECT id, name, score, memo FROM t_user WHERE score < ? LIMIT 10 OFFSET 0
```

### 相关文档

有关如何构建查询对象，请查看：

{% content-ref url="/pages/ejm0b4QU3D0D6HD0QTS7" %}
[查询对象定义](/zh/query-mapping/query-object)
{% endcontent-ref %}

有关`DataAccess`接口的更多详细信息，请查看：

{% content-ref url="/pages/eflhNzpspIhz7EYXt9ob" %}
[增删查改接口](/zh/api/crud)
{% endcontent-ref %}


# 数据库连接

## 示例

开发人员可以自行创建数据库连接：

```go
package main

import (
	"database/sql"
	_ "github.com/mattn/go-sqlite3"
)

func main() {	
	db, err := sql.Open("sqlite3", "./test.db")
	if err == nil {
		defer db.Close()
	}
	//...
}
```

或者使用属性文件创建：

```go
package main

import (
	"github.com/doytowin/goooqo/rdb"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
	db := rdb.Connect("app.properties")
	defer rdb.Disconnect(db)
	//...
}
```

## 属性格式

属性文件包含不同数据库的参数，模版如下：

```properties
driver=sqlite3
data_source=./test.db

#driver=mysql
#mysql_url=tcp(localhost:3306)/demo?charset=utf8mb4&parseTime=true
#mysql_database=demo
#mysql_username=root
#mysql_password=root
```

将会在`Dialect`功能上线后，提供更多属性配置。


# 事务

## 基本机制 <a href="#ji-ben-ji-zhi" id="ji-ben-ji-zhi"></a>

创建数据库连接之后，我们使用该数据库连接创建事务管理器。

```go
db := rdb.Connect("local.properties")
tm := rdb.NewTransactionManager(db)
```

GoooQo中的事务管理通过[TransactionManager](https://github.com/doytowin/goooqo/blob/main/core/core.go#L60)和[TransactionContext](https://github.com/doytowin/goooqo/blob/main/core/core.go#L66)（以下简称TC）配合完成。

```go
package core

import (
	"context"
	"database/sql/driver"
)

type TransactionManager interface {
	GetClient() any
	StartTransaction(ctx context.Context) (TransactionContext, error)
	SubmitTransaction(ctx context.Context, callback func(tc TransactionContext) error) error
}

type TransactionContext interface {
	context.Context
	driver.Tx
	Parent() context.Context
	SavePoint(name string) error
	RollbackTo(name string) error
}
```

`TransactionManager`中的方法`StartTransaction`负责开启事务并返回TC；TC组合了`driver.Tx`，负责事务的提交和回滚。

[TxDataAccess](https://github.com/doytowin/goooqo/blob/main/core/core.go#L74)接口组合了[DataAccess](https://github.com/doytowin/goooqo/blob/main/core/core.go#L46)和`TransactionManager`，可以在实现数据库访问接口的同时，更方便的进行事务管理。

```go
type TxDataAccess[E Entity] interface {
	TransactionManager
	DataAccess[E]
}
```

## 事务使用示例 <a href="#shi-wu-shi-yong-shi-li" id="shi-wu-shi-yong-shi-li"></a>

使用`TransactionManager#StartTransaction`开启事务，手动提交或者回滚事务：

```go
tc, err := userDataAccess.StartTransaction(ctx)
userQuery := UserQuery{ScoreLt: P(80)}
cnt, err := userDataAccess.DeleteByQuery(tc, userQuery)
if err != nil {
	err = RollbackFor(tc, err)
	return 0
}
err = tc.Commit()
return cnt
```

或者使用`TransactionManager#SubmitTransaction`通过回调的方式提交事务：

```go
err := tm.SubmitTransaction(ctx, func(tc TransactionContext) (err error) {
    // transaction body
    return
})
```

## 事务的传播管理 <a href="#shi-wu-de-chuan-bo-guan-li" id="shi-wu-de-chuan-bo-guan-li"></a>

在Spring的事务传播机制中定义了以下7个级别，`GoooQo`中的对应处理方式如下：

* REQUIRED

  使用任意context调用`TxDataAccess`的`StartTransaction`：

  如果context为TC，则将context强制转化为TC后返回；

  如果context不是TC，则调用`db#BeginTx`开启事务获取`sql.Tx`，再通过context和`sql.Tx`创建TC后返回。
* SUPPORTS

  使用任意`Context`调用`TxDataAccess`的数据库访问方法。
* REQUIRES\_NEW

  当`ctx`为TC时，使用`ctx.(TC).Context`开启事务；\
  当`ctx`不为TC时，使用`ctx`开启事务；
* NOT\_SUPPORTED

  当`ctx`为TC时，使用`ctx.(TC).Context`调用`TxDataAccess`的数据库访问方法；\
  当`ctx`不为TC时，使用`ctx`调用`TxDataAccess`的数据库访问方法；
* MANDATORY:

  对传入的`ctx`不是TC的情况进行处理。
* NEVER

  对传入的`ctx`是TC的情况进行处理。
* NESTED

  使用TC的`SavePoint/RollbackTo`方法。

这里整理了一个表格对前4个传播级别进行了对比：

| context参数\数据库操作         | 开启事务          | 调用数据库          |
| ----------------------- | ------------- | -------------- |
| 任意Context               | REQUIRED      | SUPPORTS       |
| ctx.(TC).Context \| ctx | REQUIRES\_NEW | NOT\_SUPPORTED |


# 增删查改接口

## 接口定义

创建事务管理器后，我们需要为每个实体创建一个数据访问接口：

```go
userDataAccess := rdb.NewTxDataAccess[UserEntity](tm)
```

方法`NewTxDataAccess`返回一个`TxDataAccess`实例，该实例由用于CRUD操作的`DataAccess`接口和用于事务操作的`TransactionManager`接口组成。

```go
package core

type DataAccess[E Entity] interface {
	Get(ctx context.Context, id any) (*E, error)
	Delete(ctx context.Context, id any) (int64, error)
	Query(ctx context.Context, query Query) ([]E, error)
	Count(ctx context.Context, query Query) (int64, error)
	DeleteByQuery(ctx context.Context, query Query) (int64, error)
	Page(ctx context.Context, query Query) (PageList[E], error)
	Create(ctx context.Context, entity *E) (int64, error)
	CreateMulti(ctx context.Context, entities []E) (int64, error)
	Update(ctx context.Context, entity E) (int64, error)
	Patch(ctx context.Context, entity E) (int64, error)
	PatchByQuery(ctx context.Context, entity E, query Query) (int64, error)
}

type TxDataAccess[E Entity] interface {
	TransactionManager
	DataAccess[E]
}

type PageList[D any] struct {
    List  []D   `json:"list"`
    Total int64 `json:"total"`
}
```

`DataAccess`接口中的所有方法一共只接收4类参数：

* `context.Context`可以是普通的Context，也可以是开启了事务的`TransactionContext`
* `id` 实体的主键
* `Entity` 实体对象，用于映射表名和列名，需要组合`IntId`或者`Int64Id`
* `Query` 查询对象，用于动态构造查询条件和分页语句，需要组合`PageQuery`

对于`Entity`的定义，请参考：

{% content-ref url="/pages/73lswPRVXyxfmfZm9Wse" %}
[实体对象](/zh/entity-mapping/entity-object)
{% endcontent-ref %}

对于`Query`的定义，请参考：

{% content-ref url="/pages/ejm0b4QU3D0D6HD0QTS7" %}
[查询对象定义](/zh/query-mapping/query-object)
{% endcontent-ref %}

## 示例

以下接口调用示例基于实体对象`UserEntity`和查询对象`UserQuery`：

```go
type UserEntity struct {
    Int64Id
    Name    *string `json:"name,omitempty"`
    Score   *int    `json:"score,omitempty"`
    Memo    *string `json:"memo,omitempty"`
    Deleted *bool   `json:"deleted,omitempty"`
}

func (u UserEntity) GetTableName() string {
    return "t_user"
}

type UserQuery struct {
    PageQuery
    IdGt     *int64
    IdIn     *[]int64
    ScoreLt  *int
    MemoNull *bool
    MemoLike *string
    Deleted  *bool
    UserOr   *[]UserQuery

    Account    *string    `condition:"(username = ? OR email = ?)"`
    ScoreLtAvg *UserQuery `subquery:"select avg(score) from t_user"`
    ScoreLtAny *UserQuery `subquery:"SELECT score FROM t_user"`
    ScoreLtAll *UserQuery `subquery:"select score from UserEntity"`
    ScoreGtAvg *UserQuery `select:"avg(score)" from:"UserEntity"`

    ScoreInScoreOfUser    *UserQuery //score IN (SELECT score FROM t_user WHERE ...)
    ScoreGtAvgScoreOfUser *UserQuery //score > (SELECT AVG(score) FROM t_user WHERE ...)
}
```

## 调用示例

### Get

根据id查询数据：

```go
user, err := userDataAccess.Get(ctx, 3)
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE id = ?" args="[3]"
```

### Query

根据查询条件查询数据：

```go
userQuery := UserQuery{ScoreLt: P(80)}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE score < ?" args="[80]"

userQuery := UserQuery{PageQuery: PageQuery{PageSize: P(20), 
    Sort: P("id,desc;score")}, MemoLike: P("Great")}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE memo LIKE ? ORDER BY id DESC, score LIMIT 20 OFFSET 0" args="[Great]"

userQuery := UserQuery{IdIn: &[]int64{1, 4, 12}, Deleted: P(true)}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE id IN (?, ?, ?) AND deleted = ?" args="[1 4 12 true]"

userQuery := UserQuery{UserOr: &[]UserQuery{{IdGt: P(int64(10)), 
    MemoNull: P(true)}, {ScoreLt: P(80), MemoLike: P("Good")}}}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (id > ? AND memo IS NULL OR score < ? AND memo LIKE ?)" args="[10 80 Good]"

userQuery := UserQuery{ScoreGtAvg: &UserQuery{Deleted: P(true)},
     ScoreLtAny: &UserQuery{}}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE score > (SELECT avg(score) FROM t_user WHERE deleted = ?) 
// AND score < ANY(SELECT score FROM t_user)" args="[true]"

userQuery := UserQuery{Account: P("John")}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (username = ? OR email = ?)" args="[John John]"
```

### Count

根据查询条件查询数据的总数：

```go
userQuery := UserQuery{ScoreLt: P(60)}
cnt, err := userDataAccess.Count(ctx, userQuery)
// SQL="SELECT count(0) FROM t_user WHERE score < ?" args="[60]"
```

### Page

根据查询条件查询数据和总数：

```go
userQuery := UserQuery{PageQuery: PageQuery{PageSize: P(20)}, ScoreLt: P(80)}
page, err := userDataAccess.Page(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE score < ? LIMIT 20 OFFSET 0" args="[80]"
// SQL="SELECT count(0) FROM t_user WHERE score < ?" args="[80]"
```

### Delete

根据id删除数据：

```go
tc, _ := tm.StartTransaction(tc)
cnt, err := userDataAccess.Delete(tc, 3)
// SQL="DELETE FROM t_user WHERE id = ?" args="[3]"
```

### DeleteByQuery

根据查询条件删除数据：

```go
userQuery := UserQuery{ScoreLt: P(80)}
cnt, err := userDataAccess.DeleteByQuery(tc, userQuery)
// SQL="DELETE FROM User WHERE score < ?" args="[80]"
```

### Create

创建单条数据：

```go
entity := UserEntity{Name: P("John"), Score: P(90), Deleted: P(false)}
id, err := userDataAccess.Create(tc, &entity)
// SQL="INSERT INTO t_user (name, score, memo, deleted) VALUES (?, ?, ?, ?)" args="[John 90 <nil> false]"
```

### CreateMulti

创建多条数据：

```go
entities := []UserEntity{{Name: P("John"), Score: P(90), Memo: P("Great"), Deleted: P(false)}, {Name: P("Alex"), Score: P(55)}}
cnt, err := userDataAccess.CreateMulti(tc, entities)
// SQL="INSERT INTO t_user (name, score, memo, deleted) VALUES (?, ?, ?, ?), (?, ?, ?, ?)" args="[John 90 Great false Alex 55 <nil> <nil>]"
```

### Update

根据id更新所有字段：

```go
entity := UserEntity{Int64Id: NewInt64Id(2), Score: P(90), Memo: P("Great")}
cnt, err := userDataAccess.Update(tc, entity)
// SQL="UPDATE t_user SET score = ?, memo = ? WHERE id = ?" args="[90 Great 2]"
```

### Patch

根据id更新所有非空字段：

```go
entity := UserEntity{Int64Id: NewInt64Id(2), Score: P(90)}
cnt, err := userDataAccess.Patch(tc, entity)
// SQL="UPDATE t_user SET score = ? WHERE id = ?" args="[90 2]"
```

### PatchByQuery

根据查询条件更新所有非空字段：

```go
entity := UserEntity{Memo: P("Add Memo")}
query := UserQuery{MemoNull: P(true)}
cnt, err := userDataAccess.PatchByQuery(tc, entity, query)
// SQL="UPDATE t_user SET memo = ? WHERE memo IS NULL" args="[Add Memo]"
```


# 实体对象

## 示例

```go
import (
	. "github.com/doytowin/goooqo"
)

type UserEntity struct {
	Int64Id
	Name  *string `json:"name,omitempty"`
	Score *int    `json:"score,omitempty"`
	Memo  *string `json:"memo,omitempty"`
}

func (u UserEntity) GetTableName() string {
	return "t_user"
}
```

## 定义

实体对象用于为GoooQo中的CRUD语句构建提供表名和列名。

实体对象需要实现以下接口：

```go
package core

type Entity interface {
	GetId() any

	// SetId set id to self.
	// self: the pointer points to the current entity.
	// id: type could be int, int64, or string so far.
	SetId(self any, id any) error
}


package rdb

import "github.com/doytowin/goooqo/core"

type RdbEntity interface {
	core.Entity
	GetTableName() string
}
```

* `GetId`用于构建`UPDATE`语句。
* `SetId`用于将生成的ID注入到实体。
* `GetTableName`用于提供实体对应的表名。
* 实体中的每个字段需要与表中的一列相对应。

GoooQo提供了两个`Entity`的实现以简化实体定义：`IntId`和`Int64Id`。

示例中`UserEntity`对应的增删查改语句为：

```sql
SELECT id, name, score, memo FROM t_user；
INSERT INTO t_user (name, score, memo) VALUES (?, ?, ?)
UPDATE t_user SET name = ?, score = ?, memo = ? WHERE id = ?;
DELETE FROM t_user WHERE id = ?;
```


# 关联实体

Will support in v0.2.x.


# 查询对象定义

## 示例

```go
type UserQuery struct {
	PageQuery
	ScoreLt   *int
	MemoStart *string
	// ...
}
```

## 查询接口

查询对象需要实现查询接口，以便构建分页子句和排序子句：

```go
package core

type Query interface {
    GetPageNumber() int
    GetPageSize() int
    CalcOffset() int
    GetSort() *string
    NeedPaging() bool
}
```

GoooQo为查询接口提供了一个标准实现`PageQuery`：

{% content-ref url="/spaces/LwvgPEM32oQWVvvI7ep3/pages/7wI5Tx3t5Jl0wPwutQyV" %}
[分页排序对象](/zh/query-mapping/page-query)
{% endcontent-ref %}

## 字段定义

查询对象用于映射 SQL 语句的动态部分，例如过滤条件、分页和排序。

查询对象中的每个字段用于映射一组查询条件。

查看以下文档以了解如何定义查询对象中的字段：

{% content-ref url="/spaces/LwvgPEM32oQWVvvI7ep3/pages/GfvGP08it7ljJxkyX6HQ" %}
[谓词后缀字段](/zh/query-mapping/query-object/predicate-suffix-field)
{% endcontent-ref %}

{% content-ref url="/spaces/LwvgPEM32oQWVvvI7ep3/pages/27O85XWdQG0qXL3c2v0L" %}
[逻辑后缀字段](/zh/query-mapping/query-object/logic-suffix-field)
{% endcontent-ref %}

{% content-ref url="/spaces/LwvgPEM32oQWVvvI7ep3/pages/IyQiTNnV0NzfM0DWfklE" %}
[子查询字段](/zh/query-mapping/query-object/subquery-field)
{% endcontent-ref %}

{% content-ref url="/spaces/LwvgPEM32oQWVvvI7ep3/pages/2G08mVRgUT4dLZPzjdiL" %}
[ER关系字段](/zh/query-mapping/query-object/er-query-field)
{% endcontent-ref %}

{% content-ref url="/spaces/LwvgPEM32oQWVvvI7ep3/pages/mZJ5Rmz3sN3uSRYfPXJH" %}
[自定义字段](/zh/query-mapping/query-object/custom-condition-field)
{% endcontent-ref %}


# 谓词后缀字段

## 谓词后缀

GoooQo采用谓词后缀映射方法，将查询对象中的字段中以预定义谓词结尾的字段映射为基本查询条件。每个基本查询条件由列名、比较运算符和比较值组成。

在查询对象中，用于映射基本查询条件的字段，命名格式为列名加谓词的别名，用于映射查询条件的列名和比较运算符，查询条件的比较值为字段的赋值。 一个查询对象实例中已赋值的字段会被映射为对应的查询条件，并由逻辑运算符AND拼接为查询子句。

以下为后缀映射的两个示例：

```go
userQuery := UserQuery{Deleted: P(true)}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE deleted = ?" args="[true]"

userQuery := UserQuery{IdIn: &[]int64{1, 4, 12}, Deleted: P(true)}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user WHERE id IN (?, ?, ?) AND deleted = ?" args="[1 4 12 true]"
```

预定义谓词谓词后缀请参考[谓词后缀表](#wei-ci-hou-zhui-biao)。

## 谓词后缀表

<table><thead><tr><th>谓词后缀</th><th>字段名称</th><th>赋值</th><th>SQL查询条件</th><th data-hidden>MongoDB Condition</th></tr></thead><tbody><tr><td>(EMPTY)</td><td>id</td><td>5</td><td>id = 5</td><td>{"id":5}</td></tr><tr><td>Eq</td><td>idEq</td><td>5</td><td>id = 5</td><td>{"idEq":5}</td></tr><tr><td>Not</td><td>idNot</td><td>5</td><td>id != 5</td><td>{"idNot":{"$ne":5}}</td></tr><tr><td>Ne</td><td>idNe</td><td>5</td><td>id &#x3C;> 5</td><td>{"idNe":{"$ne":5}}</td></tr><tr><td>Gt</td><td>idGt</td><td>5</td><td>id > 5</td><td>{"idGt":{"$gt":5}}</td></tr><tr><td>Ge</td><td>idGe</td><td>5</td><td>id >= 5</td><td>{"idGe":{"$gte":5}}</td></tr><tr><td>Lt</td><td>idLt</td><td>5</td><td>id &#x3C; 5</td><td>{"idLt":{"$lt":5}}</td></tr><tr><td>Le</td><td>idLe</td><td>5</td><td>id &#x3C;= 5</td><td>{"idLe":{"$lte":5}}</td></tr><tr><td>NotIn</td><td>idNotIn</td><td>[1,2,3]</td><td>id NOT IN (1,2,3)</td><td>{"id":{"$nin":[1, 2, 3]}}</td></tr><tr><td>In</td><td>idIn</td><td>[1,2,3]</td><td>id IN (1,2,3)</td><td>{"id":{"$in":[1, 2, 3]}}</td></tr><tr><td>Null</td><td>memoNull</td><td>false</td><td>memo IS NOT NULL</td><td>{"memo":{"$not":{"$type", 10}}}</td></tr><tr><td>Null</td><td>memoNull</td><td>true</td><td>memo IS NULL</td><td>{"memo":{"$type", 10}}</td></tr><tr><td>NotLike</td><td>nameNotLike</td><td>"arg"</td><td>name NOT LIKE '%arg%'</td><td>{"name":{"$not":{"$regex":"arg"}}}</td></tr><tr><td>Like</td><td>nameLike</td><td>"arg"</td><td>name LIKE '%arg%'</td><td>{"name":{"$regex":"arg"}}</td></tr><tr><td>NotStart</td><td>nameNotStart</td><td>"arg"</td><td>name NOT LIKE 'arg%'</td><td>{"name":{"$not":{"$regex":"^arg"}}}</td></tr><tr><td>Start</td><td>nameStart</td><td>"arg"</td><td>name LIKE 'arg%'</td><td>{"name":{"$regex":"^arg"}}</td></tr><tr><td>NotEnd</td><td>nameNotEnd</td><td>"arg"</td><td>name NOT LIKE '%arg'</td><td>{"name":{"$not":{"$regex":"arg$"}}}</td></tr><tr><td>End</td><td>nameEnd</td><td>"arg"</td><td>name LIKE '%arg'</td><td>{"name":{"$regex":"arg$"}}</td></tr><tr><td>NotContain</td><td>nameNotContain</td><td>"arg"</td><td>name NOT LIKE '%arg%’</td><td>{"name":{"$not":{"$regex":"arg"}}}</td></tr><tr><td>Contain</td><td>nameContain</td><td>"arg"</td><td>name LIKE '%arg%’</td><td>{"name":{"$regex":"arg"}}</td></tr><tr><td>Rx</td><td>nameRx</td><td>"arg\d"</td><td>name REGEXP 'arg\d’</td><td>{"name":{"$regex":"arg\d"}}</td></tr></tbody></table>


# 逻辑后缀字段

默认情况下，查询对象各个字段对应的查询条件之间是通过AND连接起来的。

## Or后缀

如果需要使用逻辑运算符OR连接查询条件，则需要在查询对象中定义一个后缀为Or的结构体或数组。

GoooQo支持以下三种定义方式：

```go
type UserQuery struct {
	PageQuerygo
	//...
	NameStartOr *[]string
	UserOr      *UserQuery
	UsersOr     *[]UserQuery
}
```

### NameStartOr \*\[]string

```go
userQuery := UserQuery{NameStartOr: &[]string{"Bob", "John", "Tim"}}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (name LIKE ? OR name LIKE ? OR name LIKE ?)" args="[Bob% John% Tim%]"
```

### UserOr \*UserQuery

```go
userQuery := UserQuery{UserOr: &UserQuery{IdIn: &[]int64{1, 4, 12}, Deleted: P(true)}}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user 
// WHERE (id IN (?, ?, ?) OR deleted = ?)" args="[1 4 12 true]"
```

### UsersOr \*\[]UserQuery

```go
userQuery := UserQuery{UsersOr: &[]UserQuery{
	{IdIn: &[]int64{1, 4, 12}, Deleted: P(true)},
	{IdGt: P(int64(10)), Deleted: P(false)},
}}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user
// WHERE (id IN (?, ?, ?) AND deleted = ? OR id > ? AND deleted = ?)"
// args="[1 4 12 true 10 false]"
```

## And后缀

当字段的名称以`And`结尾时，连接多个查询条件的逻辑运算符为AND。

```go
type UserQuery struct {
	PageQuerygo
	//...
	UserOr      *UserQuery
	UserAnd     *UserQuery
}
```

### UserAnd \*UserQuery

```go
userQuery := UserQuery{ScoreLt: P(80), UserOr: &UserQuery{Deleted: P(true),
    UserAnd: &UserQuery{IdIn: &[]int64{1, 4, 12}, Deleted: P(false)}}}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user
// WHERE score < ? AND (deleted = ? OR id IN (?, ?, ?) AND deleted = ?)"
// args="[80 true 1 4 12 false]"
```

### 相关文章

[在GoooQo中怎么表达select \* from user where id = ? or (name = ? and age = ?)](https://blog.doyto.win/post/goooqo-or-clause/)


# 子查询字段

对于一般的子查询条件，例如`score > (SELECT avg(score) FROM t_user WHERE removed = ?)`，在OQM中被分为三个部分分别进行映射：

* score >
* SELECT avg(score) FROM t\_user
* WHERE clause

第一部分`score >`， 可以使用字段名`scoreGtXxx`来映射得到。 谓词后缀之后定义的字符串仅用于区分重复的字段名，在映射时会被忽略。

第二部分包含一个列名和一个表名，这些属于不变的静态，GoooQo提供了两种标签来保存这些信息： 一种是标签`subquery`，用于定义该子查询语句； 一种是标签`select`和`from`的组合，分别保存列名和表名。

第三部分是另一个WHERE子句，可以通过查询对象来映射。因此，我们将字段类型定义为对应的查询对象，并通过查询对象映射方法将字段的值映射为子查询中WHERE子句。

### 示例（v0.2.0+）

```go
ScoreLtAvg *UserQuery `subquery:"select avg(score) from User"`
ScoreLtAny *UserQuery `subquery:"SELECT score FROM User"`
ScoreLtAll *UserQuery `subquery:"select score from User"`
ScoreGtAvg *UserQuery `select:"avg(score)" from:"User"`
```


# ER关系字段

## **抽象实体路径**

在实体关系图中，多对多关系用于表示两个实体之间的关系。多对多关系是具有传递性的。例如，如果实体A与实体B具有多对多关系，而实体B与实体C具有多对多关系，则实体A和实体C也具有多对多关系，这是一种间接的多对多关系。

基于多对多关系的传递性，**抽象实体路径**的概念被提出用于描述实体之间这种直接或间接的多对多关系。抽象实体路径将从一个实体到另一个实体的所有实体作为节点来描述任意两个实体之间所具有的多对多关系。例如，实体A和实体B的抽象实体路径为\[A,B]，实体B和实体A的抽象实体路径为\[B,A]，实体C和实体A的抽象实体路径为\[C,B,A]。抽象实体路径包含了任意两个实体之间关系的全部信息，从而用于动态生成复杂的嵌套查询语句。

GoooQo 引入**抽象实体路径**概念，定义名为`entitypath`的标签来表示实体之间的关系。该标签用于查询对象中的用于查询实体关系的字段。例如，实体路径`` `entitypath:"user,role"` ``，基于预定的表名格式，可以得到两个实体表名t\_user和t\_role，中间表表名a\_user\_and\_role，以及两个外键名称user\_id和role\_id，进而生成查询语句：

```sql
SELECT * FROM t_user WHERE
id IN (
    SELECT user_id FROM a_user_and_role WHERE role_id IN (
       SELECT id FROM t_role WHERE ...
    )
)
```

## 示例

表 `t_menu` 有一个列 `parent_id`，它将 `id` 列本身引用为外键。`parent_id` 列用于定义菜单项之间的层次父子关系。菜单通过通用 RBAC 模型作为系统资源分配给用户。那么菜单到用户的实体路径即为：`menu,perm,role,user`，用于生成嵌套查询语句。

```go
import . "github.com/doytowin/goooqo/core"

type MenuEntity struct {
	IntId
	ParentId *int    `json:"parentId,omitempty"`
	Name     *string `json:"name,omitempty"`
}

type MenuQuery struct {
	PageQuery
	Id *int

	// many-to-one:
	// 查询特定父菜单的子菜单：
	// parent_id IN (SELECT id FROM t_menu WHERE [conditions])
	Parent *MenuQuery `entitypath:"menu" localField:"ParentId"`

	// one-to-many:
	// 查询特定子菜单的父菜单：
	// id IN (SELECT parent_id FROM t_menu WHERE [conditions])
	Children *MenuQuery `entitypath:"menu" foreignField:"ParentId"`

	/**
	many-to-many:
	查询特定用户可以访问的菜单：
	id IN (
		SELECT menu_id FROM a_perm_and_menu WHERE perm_id IN (
			SELECT perm_id FROM a_role_and_perm WHERE role_id IN (
				SELECT role_id FROM a_user_and_role WHERE user_id IN (
					SELECT id FROM t_user WHERE [conditions]
				)
			)
		)
	)*/
	User *UserQuery `entitypath:"menu,perm,role,user"`
}
```


# 自定义字段

对于目前不支持的查询条件类型，GoooQo支持使用`condition`标签直接编写原生SQL条件：

```go
type UserQuery struct {
    PageQuery
    Account    *string `condition:"(username = ? OR email = ?)"`
    //...
}
```

### 示例

```go
userQuery := UserQuery{Account: P("John")}
users, err := userDataAccess.Query(ctx, userQuery)
// SQL="SELECT id, name, score, memo, deleted FROM t_user
//   WHERE (username = ? OR email = ?)" args="[John John]"
```


# 分页排序对象

## 定义

`PageQuery`实现了`Query`接口，定义了三个字段， 其中，`PageNumber`和`PageSize`用于构建分页子句，`Sort` 用于构建排序子句。

```go
package core

type PageQuery struct {
	PageNumber *int    `json:"page,omitempty"`
	PageSize   *int    `json:"size,omitempty"`
	Sort       *string `json:"sort,omitempty"`
}
```

## 分页示例

```go
userQuery := UserQuery{PageQuery: PageQuery{}}
users, err := userDataAccess.Query(ctx, userQuery)
//SELECT id, score, memo FROM User

userQuery := UserQuery{PageQuery: PageQuery{PageSize: P(20)}}
users, err := userDataAccess.Query(ctx, userQuery)
//SELECT id, score, memo FROM User LIMIT 20 OFFSET 0

// When only PageNumber is set, PageSize will be set to 10
userQuery := UserQuery{PageQuery: PageQuery{PageNumber: P(5)}}
users, err := userDataAccess.Query(ctx, userQuery)
//SELECT id, score, memo FROM User LIMIT 10 OFFSET 40

userQuery := UserQuery{PageQuery: PageQuery{PageSize: P(50), PageNumber: P(3)}}
users, err := userDataAccess.Query(ctx, userQuery)
//SELECT id, score, memo FROM User LIMIT 10 OFFSET 100	
```

## 排序示例

`Sort`赋值的字符串需要符合正则表达式：`regexp.MustCompile("(?i)(\w+)(,(asC|dEsc))?;?")`

```go
userQuery := UserQuery{PageQuery: PageQuery{Sort: P("id,desc;score,asc;memo")}}
users, err := userDataAccess.Query(ctx, userQuery)
//SELECT id, score, memo FROM User ORDER BY id DESC, score ASC, memo
```


# 视图对象

Will support in v0.3.x.


# 聚合查询对象

Will support in v0.3.x.


# 自然连接

Will support in v0.3.x.


# 外连接

Will support in v0.3.x.


# Nested View

Will support in v0.3.x.


# 数据库方言

Will support in v0.4.x.


# 锁


# 文章