> For the complete documentation index, see [llms.txt](https://espier.gitbook.io/espier/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://espier.gitbook.io/espier/kuang-jia/intro/shu-ju-ku/database-crud-repository.md).

# 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     |