> For the complete documentation index, see [llms.txt](https://goooqo.docs.doyto.win/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://goooqo.docs.doyto.win/api/crud.md).

# 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.md)
{% endcontent-ref %}

For the definition of `Query`, check:

{% content-ref url="/spaces/caxovyAjMzGHxhZgzo8F/pages/DbcpkkQ6xQmUBzjz98zT" %}
[Query Object](/query-mapping/query-object.md)
{% 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]"
```