README

Doctrine查询过滤

一个提供Doctrine ORM查询过滤组件的包。通过将查询过滤条件建模为可重用的对象，它提供了一种一致且可扩展的方式来构建复杂的DQL语句。

该项目受到了Laminas Doctrine QueryBuilder的启发；提供了类似的功能，而不依赖于Laminas框架。

安装

通过composer进行安装。

require alex-patterson-webdev/doctrine-query-filter ^0.9

使用方法

使用QueryFilterManager，我们可以从array格式创建DQL字符串。例如，考虑以下DQL字符串。

SELECT c FROM Customer c WHERE c.forename = 'Fred' AND (c.age BETWEEN 18 AND 30)

我们可以使用一组称为查询条件的过滤器来表示此DQL查询。

$criteria = [
    'filters' => [
        [
            'name' => 'eq',
            'field' => 'forename',
            'value' => 'Fred',
        ],
        [
            'name' => 'between',
            'field' => 'age',
            'from' => 18,
            'to' => 30
        ],
    ],
];

通过将此$criteria传递给我们的QueryFilterManager，我们可以以下方式生成（并执行）查询。

// Get our Doctrine query builder instance
$queryBuilder = $entityManager->getRepository('Customer')->createQueryBuilder('c');

// Create a new QueryFilterManager (and supply it with a desired FilterFactory instance)
$queryFilterManager = new QueryFilterManager(new FilterFactory());

// Apply the filters to the $queryBuilder
$queryBuilder = $queryFilterManager->filter($queryBuilder, 'Customer', $criteria);

// SELECT c FROM Customer c WHERE c.forename = 'Fred' AND (c.age BETWEEN 18 AND 30)
echo $queryBuilder->getDQL();

// Fetch the results
$results = $queryBuilder->getQuery()->execute();

使用OR条件组合过滤器

当定义多个过滤器时，条件将明确使用and复合查询过滤器以"AND"方式组合在一起。要创建"OR"条件，我们必须定义一个or过滤器并为其提供所需的conditions数组。

// SELECT c FROM Customer c WHERE c.enabled = :enabled AND (c.username = :username1 OR c.username = :username2)
$criteria = [
    'filters' => [
        [
            'name' => 'eq',
            'field' => 'enabled',
            'value' => true,
        ],
        [
            'name' => 'or',
            'conditions' => [
                [
                    'name' => 'eq',
                    'field' => 'username',
                    'value' => 'Fred',
                ],
                [
                    'name' => 'eq',
                    'field' => 'username',
                    'value' => 'bob',
                ],
            ]
        ],
    ],
];

嵌套过滤器

您还可以嵌套and和or的组合，生成的DQL将包括正确的分组。

// WHERE x.surname = 'Smith' OR (x.age > 18 AND x.gender = 'Male')
$criteria = [
    'filters' => [
        [
            'name' => 'or',
            'conditions' => [
                [
                    'name' => 'eq',
                    'field' => 'surname',
                    'value' => 'Smith',
                ],
                [
                    'name' => 'and',
                    'conditions' => [
                        [
                            'name' => 'gt',
                            'field' => 'age',
                            'value' => 18,
                        ],
                        [
                            'name' => 'eq'
                            'field' => 'gender',
                            'value' => 'Male',
                        ],
                    ]
                ],
            ]
        ]
    ],
];

自定义过滤器

上述示例演示了内置过滤器的使用。然而，这些过滤器非常冗长，难以管理。真正的力量在于能够创建和使用自定义过滤器；通过扩展AbstractFilter类。自定义过滤器是自包含的，可以在多个查询中重用。这允许采用更模块化和可维护的方法来构建复杂的查询。

以下示例演示了如何利用提供的过滤器创建自己的CustomerSearch过滤器，该过滤器接受可选的$criteria参数。

use Arp\DoctrineQueryFilter\Filter\AbstractFilter;
use Arp\DoctrineQueryFilter\Filter\Exception\FilterException;
use Arp\DoctrineQueryFilter\Metadata\MetadataInterface;
use Arp\DoctrineQueryFilter\QueryBuilderInterface;

final class CustomerSearch extends AbstractFilter
{
    public function filter(QueryBuilderInterface $queryBuilder, MetadataInterface $metadata, array $criteria): void
    {
        if (empty($criteria['surname'])) {
            throw new FilterException('The surname criteria is required');
        }

        $filters = [
            [
                'name' => 'neq',
                'field' => 'status',
                'value' => 'inactive',
            ],
            [
                'name' => 'begins_with',
                'field' => 'surname',
                'value' => $criteria['surname'],
            ],
        ];

        if (isset($criteria['forename'])) {
            $filters[] = [
                'name' => 'eq',
                'field' => 'forename',
                'value' => $criteria['forename'],
            ];
        }

        if (isset($criteria['age'])) {
            $filters[] = [
                'name' => 'gte',
                'field' => 'age',
                'value' => (int) $criteria['age'],
            ];
        }

        $this->applyFilters($queryBuilder, $metadata, $filters);
    }
}

// We must register the custom filter with the FilterFactory
$filterFactory = new FilterFactory();
$filterFactory->addToClassMap('customer_search', CustomerSearch::class);

$queryFilterManager = new QueryFilterManager($filterFactory);
$criteria = [
    'filters' => [
        [
            'name' => 'customer_search',
            'surname' => 'Smith',
            'age' => 21,
        ],
    ],
];

$queryBuilder = $queryFilterManager->filter($queryBuilder, 'Entity\Customer', $criteria);

// Executes DQL: SELECT c FROM Customer c WHERE c.status != 'inactive' AND c.surname LIKE 'Smith%' AND c.age >= 21
$queryBuilder->getQuery()->execute();

排序结果

除了过滤集合外，我们还可以通过使用sort条件键添加排序填充来排序。

// SELECT c FROM Customer c WHERE c.id = 123 ORDER BY c.id DESC, c.createdDate ASC
$critiera = [
    'filters' => [
        [
            'name' => 'eq',
            'field' => 'id',
            'value' => 123
        ],
        'sort' => [
            [
                'name' => Field::class, 
                'field' => 'id',
                'direction' => OrderByDirection::DESC->value
            ],
            [
                'field' => 'createdDate'
            ],
        ]
    ]
];

每个排序过滤器都需要field键，一个可选的direction为ASC或DESC。省略排序过滤器中的name键将默认应用Arp\DoctrineQueryFilter\Sort\Field排序过滤器。此外，省略direction将默认使排序方向为ASC。

过滤器参考

已经包含了许多类型的查询过滤器。下表定义了过滤器别名及其可用选项。

过滤器工厂

如果您需要在构建查询过滤器时获得更多控制权，可以在$criteria['filters']数组中直接提供QueryFilter实例，而不是使用数组格式。

$queryFilterManager = new QueryFilterManager(new FilterFactory());
$criteria = [
    'filters' => [
        $queryFilterManager->createFilter('eq', ['field' => 'surname', 'value => 'Smith']),
        $queryFilterManager->createFilter('between', ['field' => 'age', 'from => 18, 'to' => 65]),
    ],
],

单元测试

可以使用PHPUnit从应用程序根目录执行单元测试。

php vendor/bin/phpunit

alex-patterson-webdev / doctrine-query-filter

维护者

详细信息