# 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]"
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://goooqo.docs.doyto.win/api/crud.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.