exegeseit/doctrinequerysearch-helper

此包旨在使用 Doctrine\ORM\Querybuilder 或 Doctrine\DBAL\Querybuilder 时简化动态 WHERE 子句的创建

维护者

详细信息

github.com/exeGeseIT/DoctrineQuerySearchHelper

来源

问题

安装: 74

依赖项: 0

建议者: 0

安全: 0

星星: 1

观察者: 0

分支: 0

开放问题: 0

v3.1.1 2024-04-29 23:56 UTC

Requires

Requires (Dev)

MIT 6ad8e609131af75450b11e455c82b54a04f98e32

  • Jean-Claude GLOMBARD <jc.glombard.woop@gmail.com>

This package is auto-updated.

Last update: 2024-09-30 00:54:03 UTC

README

此包旨在使用 Doctrine\ORM\Querybuilder 或 Doctrine\DBAL\Querybuilder 时简化动态 WHERE 子句的创建

它依赖于

  • QueryClauseBuilder，一个负责根据 $search 条件数组创建最终 WHERE 子句的 Querybuilder 辅助工具
  • SearchFilter，一组用于定义 $search 条件数组的静态辅助工具

安装

DoctrineQuerySearchHelper 至少需要 PHP 8.1

运行以下命令将此包安装到您的应用程序中

$ composer require exegeseit/doctrinequerysearch-helper

工作原理/基本用法

此包的基本用法是在实体仓库中创建一个 "fetchQb" 方法。此方法将接收我们的 $search 条件数组作为参数，并返回一个完全定义的 Querybuilder 实例（SELECT 语句 + WHERE 语句）。

内部，使用 QueryClauseBuilder 实例来定义允许的搜索键及其映射到定义 SELECT 语句部分的返回 Querybuilder 实例的实体属性。

以下示例展示了如何实现这一点。

另一方面，$search 参数是一个关联数组，其中每行定义最终 WHERE 子句中的一个条件，其形式为

searchKey_filter => searchKey_value

searchKey_filter 键使用后面在 "SearchFilter 工具" 部分中描述的适当 SearchFilter 辅助工具生成

用法

查看 fetchMarketQb 方法，该方法创建用于获取 "Market" 对象的 QueryBuilder。特别是查看如何声明不同的 "搜索键"，这将允许您过滤结果。它还定义了一个 默认 的 ORDER BY 子句

// src/Repository/MarketRepository.php
use App\Entity\Market;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
// ..
use ExeGeseIT\DoctrineQuerySearchHelper\QueryClauseBuilder;
// ...

class MarketRepository extends ServiceEntityRepository
{
    public function __construct(CacheInterface $cache, ManagerRegistry $registry)
    {
        parent::__construct($registry, Market::class);
    }

    public function fetchMarketQb(array $search = [], string $paginatorSort = '')
    {
		    
        /**
         * Get a QueryBuilder instance and define his SELECT statement
         */ 
        $qb = $this->createQueryBuilder('m')
                ->innerJoin('m.organization', 'o')
                    ->addSelect('o')
                ->leftJoin('m.funder', 'fu')
                    ->addSelect('fu')
                ->leftJoin('m.userofmarkets', 'uof', Join::WITH, 'uof.isaccountable = 1')
                    ->addSelect('uof')
                ->leftJoin('uof.user', 'u')
                    ->addSelect('u')
                ;

        $qb->addOrderBy('m.name'); 

        /** 
         * Now, use $qb to  get an intance of QueryClauseBuilder 
         */
        $clauseBuilder = QueryClauseBuilder::getInstance($qb);
        $clauseBuilder
            /** 
             * First, we define valid searchKeys and their Entity property mapping
             */
            ->setSearchFields([
                'idmarket' => 'm.id',
                'keymarket' => 'm.key',                
                'idorganization' => 'o.id',
                'keyorganization' => 'o.key',
                'idfunder' => 'fu.id',
                'keyfunder' => 'fu.key',
                'idmanager' => 'u.id',
                'isprivate' => 'm.isprivate',
                'amount' => 'm.amount',
            ])
            /** 
             * We can also define "special" searchKeys.
             * If they appear in the $search array without any filter,
             * a LIKE filter is implicitly applied
             * In other words (in this example) these two definitions are equivalent:
             *    $search[ SearchFilter::filter('manager') ] = 'Peter';
             *    $search[ SearchFilter::like('manager') ] = 'Peter';
             */
            ->setDefaultLikeFields([
                'funder' => 'fu.name',
                'organization' => 'o.name',
                'market' => 'm.name',
                'manager' => "CONCAT(u.firstname, ' ', u.lastname)",
            ])
            ;

        /** 
         * Finally, the WHERE clause of our QueryBuilder is calculated
         * and our "fully defined" QueryBuilder instance is returned. 
         */
        return $clauseBuilder->getQueryBuilder($search, $paginatorSort);
    }
}

现在，我们可以使用我们的仓库方法获取 Market 的过滤列表。它还定义了一个 默认 的 ORDER BY 子句

// src/Controller/SomeController.php
use App\Entity\Market;
// ...
use ExeGeseIT\DoctrineQuerySearchHelper\SearchFilter;
// ...

class SomeController
{
    public function index(EntityManagerInterface $em)
    {
	// Markets filtering conditions
        $search = [
            SearchFilter::filter('idorganization') => $idorganization,
            SearchFilter::filter('funder') => $funder,
            SearchFilter::equal('manager') => $manager,
            SearchFilter::equal('isprivate') => false,
            SearchFilter::or() => [
                SearchFilter::equal('isprivate') => true,
                SearchFilter::greaterOrEqual('amount') => 5000,
            ],
        ];

        $markets = $em->getRepository(Market::class)->fetchMarketQb($search)
                     ->getQuery()->useQueryCache(true)
                     ->getResult()
                     ;

        // ...
    }
}

SearchFilter 工具

/**
 * isset($value)  ? => ...WHERE {{ searchKey = $value }}
 * !isset($value) ? => ...WHERE {{ 1 }}
 * 
 * @param string $searchKey
 * @param bool $tokenize  if TRUE "~<random_hash>" is added to ensure uniqueness
 * @return string
 */
SearchFilter::filter(string $searchKey, bool $tokenize = true)
/**
 * ...WHERE 1
 *    {{ AND searchKey = $value }}
 * 
 * @param string $searchKey
 * @param bool $tokenize  if TRUE "~<random_hash>" is added to ensure uniqueness
 * @return string
 */
SearchFilter::equal(string $searchKey, bool $tokenize = true): string
/**
 * ...WHERE 1
 *    {{ AND searchKey <> $value }}
 * 
 * @param string $searchKey
 * @param bool $tokenize  if TRUE "~<random_hash>" is added to ensure uniqueness
 * @return string
 */
SearchFilter::notEqual(string $searchKey, bool $tokenize = true): string
/**
 * ...WHERE 1
 *    {{ AND searchKey LIKE $value }}
 * 
 * @param string $searchKey
 * @param bool $tokenize  if TRUE "~<random_hash>" is added to ensure uniqueness
 * @return string
 */
SearchFilter::like(string $searchKey, bool $tokenize = true): string
/**
 * ...WHERE 1
 *    {{ AND searchKey NOT LIKE $value
 * 
 * @param string $searchKey
 * @param bool $tokenize  if TRUE "~<random_hash>" is added to ensure uniqueness
 * @return string
 */
SearchFilter::notLike(string $searchKey, bool $tokenize = true): string
/**
 * Differs from SearchFilter::like() in that "$searchKey" is taken as is. 
 *   i.e.: the characters '%' and '_' are neither appended nor escaped
 * 
 * ...WHERE 1
 *    {{ AND searchKey LIKE $value }}
 * 
 * @param string $searchKey
 * @param bool $tokenize  if TRUE "~<random_hash>" is added to ensure uniqueness
 * @return string
 */
SearchFilter::likeStrict(string $searchKey, bool $tokenize = true): string
/**
 * Differs from SearchFilter::notLike() in that "$searchKey" is taken as is. 
 *   i.e.: the characters '%' and '_' are neither appended nor escaped
 * 
 * ...WHERE 1
 *    {{ AND searchKey NOT LIKE $value
 * 
 * @param string $searchKey
 * @param bool $tokenize  if TRUE "~<random_hash>" is added to ensure uniqueness
 * @return string
 */
SearchFilter::notLikeStrict(string $searchKey, bool $tokenize = true): string
/**
 * ...WHERE 1
 *    {{ AND searchKey IS NULL
 * 
 * @param string $searchKey
 * @param bool $tokenize  if TRUE "~<random_hash>" is added to ensure uniqueness
 * @return string
 */
SearchFilter::null(string $searchKey, bool $tokenize = true): string
/**
 * ...WHERE 1
 *    {{ AND searchKey IS NOT NULL
 * 
 * @param string $searchKey
 * @param bool $tokenize  if TRUE "~<random_hash>" is added to ensure uniqueness
 * @return string
 */
SearchFilter::notNull(string $searchKey, bool $tokenize = true): string
/**
 * ...WHERE 1
 *    {{ AND searchKey > $value
 * 
 * @param string $searchKey
 * @param bool $tokenize  if TRUE "~<random_hash>" is added to ensure uniqueness
 * @return string
 */
SearchFilter::greater(string $searchKey, bool $tokenize = true): string
/**
 * ...WHERE 1
 *    {{ AND searchKey >= $value
 * 
 * @param string $searchKey
 * @param bool $tokenize  if TRUE "~<random_hash>" is added to ensure uniqueness
 * @return string
 */
SearchFilter::greaterOrEqual(string $searchKey, bool $tokenize = true): string
/**
 * ...WHERE 1
 *    {{ AND searchKey < $value
 * 
 * @param string $searchKey
 * @param bool $tokenize  if TRUE "~<random_hash>" is added to ensure uniqueness
 * @return string
 */
SearchFilter::lower(string $searchKey, bool $tokenize = true): string
/**
 * ...WHERE 1
 *    {{ AND searchKey <= $value }}
 * 
 * @param string $searchKey
 * @param bool $tokenize  if TRUE "~<random_hash>" is added to ensure uniqueness
 * @return string
 */
SearchFilter::lowerOrEqual(string $searchKey, bool $tokenize = true): string

SearchFilter 还提供了两个组合辅助工具

/**
 * ...WHERE 1
 *    {{ AND ( .. OR .. OR ..) }}
 * 
 * @return string
 */
SearchFilter::andOr(): string
/**
 * ...WHERE 1
 *    {{ OR ( .. AND .. AND ..) }}
 * 
 * @return string
 */
SearchFilter::or(): string