# CRUD Repository

为提高开发速度，降低代码重复率，新增 2 个 CURD Trait ：`DBALCrudRepository` 和 `ORMCrudRepository`。

在 Doctrine ORM 中 ORM 依赖于 DBAL 层。ORM 调用 DBAL 并将 DBAL 返回的数组结构转化为对象。由于多了这一层转化，开发人员在使用时，可以根据使用场景和项目规模决定使用哪个Trait。

两者都是基于 Doctrine 提供的 `QueryBuilder`实现，不同的是：

* `DBALCrudRepository` 基于 SQL Query Builder （Doctrine\DBAL\Query\QueryBuilder） 
* `ORMCrudRepository` 基于 DQL Query Builder（Doctrine\ORM\QueryBuilder）

## DBALCrudRepository

`DBALCrudRepository` 基于 SQL Query Builder （Doctrine\DBAL\Query\QueryBuilder），其结果返回为数组，性能比ORMCrudRepository 略高。

```php
trait DBALCrudRepository{
    public function save(array &$data)
    public function create(array &$data) {
    public function batchUpdate(array $filter, array $data)
    public function batchDelete(array $filter)
    public function count(array $filter=[])
    public function getList($cols='*', array $filter=[], $page = 1, $pageSize = 100, $orderBy = [])
}
```

使用方法如下：

```php
<?php
namespace ItemBundle\Repositories;

use ItemBundle\Entities\ItemType;
use Doctrine\ORM\EntityRepository;
use EspierBundle\Traits\DBALCrudRepository;

class ItemTypeRepository extends EntityRepository
{
    use DBALCrudRepository;
}
```

## ORMCrudRepository

`ORMCrudRepository` 基于 DQL Query Builder（Doctrine\ORM\QueryBuilder）

```php
public function save($entity)
public function delete($entity)
public function create(array $data) {
public function batchUpdate(array $filter, array $data)
public function batchDelete(array $filter)
public function count(array $filter=[])
public function getList(array $filter=[], $page = 1, $pageSize = 100, $orderBy = [])
```

ORMCrudRepository 与 DBALCrudRepository 不同是 save 和 delete 方法，其输入为实体类，而不是数组。

使用方法如下：

```php
<?php
namespace ItemBundle\Repositories;

use ItemBundle\Entities\ItemType;
use Doctrine\ORM\EntityRepository;
use EspierBundle\Traits\ORMCrudRepository;
class ItemTypeRepository extends EntityRepository
{
    use ORMCrudRepository;
}
```

## DoctrineArrayFilter

为了兼容现有代码和大家在 ECOS 中的使用习惯，在batchUpdate、batchDelete、count和getList中的 filter数组统一由 DoctrineArrayFilter 来解析。除支持 `字段|操作符` 外，还支持多层嵌套的 OR 和 AND 结构。

### 支持结构

#### and

```php
$filter = [
    'name|eq'=>'100',
    'name|neq'=>'100',
    'name|lt'=>'100',
    'name|lte'=>'100',
    'name|gt'=>'100',
    'name|gte'=>'100',
    'name|isNull'=>'',
    'name|isNotNull'=>'',
    'name|like'=>'%bar%',
    'name|notLike'=>'%foo',
    'name|in'=>[100,1000],
    'name|notIn'=>[1100,200],
];
```

生成where 条件如下：

```
(name = '100') AND (name <> '100') AND (name < '100') AND (name <= '100') AND (name > '100') AND (name >= '100') AND (name IS NULL) AND (name IS NOT NULL) AND (name LIKE '%bar%') AND (name NOT LIKE '%foo') AND (name IN ('100', '1000')) AND (name NOT IN ('1100', '200'))
```

#### OR

```php
    $filter = [
        'OR'=>[
            'name_or_or'=>1000,
            'name_or_or2'=>1000,
        ],
    ];
```

生成where 条件如下：

```
(name_or_or = '1000') OR (name_or_or2 = '1000')
```

#### 多层嵌套

```php
$filter1 = [
    'AND'=>[
        'name_and1|like'=>'zhang',
        'name_and2|isNull'=>'',
        'OR'=>[
            'name_or1|in'=>[100,2020],
            'name_or2|notIn'=>[100,2020],
            'AND'=>[
                'name_or_or'=>1000,
                'name_or_or2'=>1000,
            ],
        ],
    ],
    'OR'=>[
        'name_or1|in'=>[100,2020],
        'name_or2|notIn'=>[100,2020],
        'AND'=>[
            'name_or_or'=>1000,
            'name_or_or2'=>1000,
        ],
    ],        
];
```

生成where 条件如下：

```sql
 ((name_and1 LIKE 'zhang') AND (name_and2 IS NULL) AND ((name_or1 IN ('100', '2020')) OR (name_or2 NOT IN ('100', '2020')) OR ((name_or_or = '1000') AND (name_or_or2 = '1000')))) AND ((name_or1 IN ('100', '2020')) OR (name_or2 NOT IN ('100', '2020')) OR ((name_or_or = '1000') AND (name_or_or2 = '1000')))
```

### 支持操作符

|    操作符    |       数据库含义      |
| :-------: | :--------------: |
|     eq    |         =        |
|    neq    |        <>        |
|     lt    |         <        |
|    lte    |        <=        |
|     gt    |         >        |
|    gte    |        >=        |
|     in    |        in        |
|   notIn   |      not in      |
|   isNull  |   name IS NULL   |
| isNotNull | name IS NOT NULL |
|    like   |       LIKE       |
|  notLike  |     NOT LIKE     |


---

# 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://espier.gitbook.io/espier/kuang-jia/intro/shu-ju-ku/database-crud-repository.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.