PHP code example of exegeseit / doctrinequerysearch-helper

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

exegeseit / doctrinequerysearch-helper example snippets

use ExeGeseIT\DoctrineQuerySearchHelper\QueryClauseBuilder; 
use ExeGeseIT\DoctrineQuerySearchHelper\SearchFilter;
use App\Entity\User;
use App\Repository\UserRepository;

$qb = $entityManager->getRepository(User::class)->createQueryBuilder('u');

$clauseBuilder = QueryClauseBuilder::getInstance(qb);
$clauseBuilder->setSearchFields[
    'id' => 'u.id',
    'email' => 'u.email',
    'createdAt' => 'u.createdAt',
];

$search = [ 
    SearchFilter::equal('email') => '[email protected]', 
];

$users = $clauseBuilder->getQueryBuilder($search)->getQuery()->getResult();

$search = [ 
    SearchFilter::equal('email') => '[email protected]', 
];

$clauseBuilder->setSearchFields[
    'id' => 'u.id',
    'email' => 'u.email',
    'createdAt' => 'u.createdAt',
];

$search = [ 
    SearchFilter::like('email') => 'john', 
];

$clauseBuilder->setSearchFields[
    'id' => 'u.id',
    'email' => 'u.email',
    'createdAt' => 'u.createdAt',
];

$clauseBuilder->setDefaultLikeFields([
    'firstName' => 'u.firstName',
    'lastName' => 'u.lastName',
]);

$search = [ 
    SearchFilter::filter('firstName') => 'john', 
];

$search = [ 
    SearchFilter::like('firstName') => 'john', 
];

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


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

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

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

        /*
         * Create a QueryClauseBuilder instance from the QueryBuilder.
         */
        $clauseBuilder = QueryClauseBuilder::getInstance($qb);
        
        $clauseBuilder
            /*
             * Define valid search keys and their entity field mapping.
             */
            ->setSearchFields([
                'idmarket' => 'm.id',
                'keymarket' => 'm.key',                
                'idorganization' => 'o.id',
                'keyorganization' => 'o.key',
                'idmanager' => 'u.id',
                'isprivate' => 'm.isprivate',
                'amount' => 'm.amount',
                'createdAt' => 'm.createdAt',
                'deletedAt' => 'm.deletedAt',
            ])
            /*
             * Define default LIKE fields.
             *
             * If one of these search keys appears in the $search array through
             * SearchFilter::filter(), a LIKE filter is implicitly applied.
             */
            ->setDefaultLikeFields([
                'organization' => 'o.name',
                'market' => 'm.name',
                'name' => 'm.name',
                'manager' => "CONCAT(u.firstname, ' ', u.lastname)",
            ])
            ;

        /*
         * Apply the WHERE clause and return the configured QueryBuilder.
         */
        return $clauseBuilder->getQueryBuilder($search, $paginatorSort);
    }
}

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

class SomeController
{
    public function index(EntityManagerInterface $em) : void
    {
        $search = [
            SearchFilter::filter('idorganization') => $idorganization,
            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::filter(string $searchKey, bool $tokenize = true): string

function isFalsyValue(mixed $value): bool
{
    return null === $value || '' === $value || [] === $value || 0 === $value || false === $value;
}

$search = [ 
    SearchFilter::filter('status') => $status,
];

SearchFilter::equal(string $searchKey, bool $tokenize = true): string

$search = [ 
    SearchFilter::equal('status') => $status,
];

SearchFilter::notEqual(string $searchKey, bool $tokenize = true): string

$search = [ 
    SearchFilter::notEqual('status') => $status,
];

SearchFilter::like(string $searchKey, bool $tokenize = true): string

$search = [ 
    SearchFilter::like('name') => $name,
];

SearchFilter::notLike(string $searchKey, bool $tokenize = true): string

$search = [ 
    SearchFilter::notLike('name') => $name,
];

SearchFilter::likeStrict(string $searchKey, bool $tokenize = true): string

$search = [ 
    SearchFilter::likeStrict('name') => $name,
];

SearchFilter::notLikeStrict(string $searchKey, bool $tokenize = true): string

$search = [ 
    SearchFilter::notLikeStrict('name') => $name,
];

SearchFilter::null(string $searchKey, bool $tokenize = true): string

$search = [ 
    SearchFilter::isNull('deletedAt') => true,
];

SearchFilter::notNull(string $searchKey, bool $tokenize = true): string

$search = [ 
    SearchFilter::isNotNull('deletedAt') => true,
];

SearchFilter::greater(string $searchKey, bool $tokenize = true): string

$search = [ 
    SearchFilter::greater('amount') => $amount,
];

SearchFilter::greaterOrEqual(string $searchKey, bool $tokenize = true): string

$search = [ 
    SearchFilter::greaterOrEqual('amount') => $amount,
];

SearchFilter::lower(string $searchKey, bool $tokenize = true): string

$search = [ 
    SearchFilter::lower('amount') => $amount,
];

SearchFilter::lowerOrEqual(string $searchKey, bool $tokenize = true): string

$search = [ 
    SearchFilter::lowerOrEqual('amount') => $amount,
];

$search = [ 
    SearchFilter::greaterOrEqual('createdAt') => $startDate,
    SearchFilter::lowerOrEqual('createdAt') => $endDate,
 ];

$search = [ 
    SearchFilter::greaterOrEqual('amount') => $minAmount,
    SearchFilter::lower('amount') => $maxAmount,
 ];

SearchFilter::andOr(): string

$search = [ 
    SearchFilter::andOr() => [
        SearchFilter::equal('status') => $statusDraf,
        SearchFilter::equal('status') => $statusPending,
];

SearchFilter::and(): string

$search = [ 
    SearchFilter::and() => [
         SearchFilter::greaterOrEqual('amount') => $amountMin,
         SearchFilter::lowerOrEqual('amount') => $amountMax,   ,
];

SearchFilter::or(): string

$search = [ 
    SearchFilter::or() => [
         SearchFilter::equal('isprivate') => true,
         SearchFilter::greaterOrEqual('amount') => $amount,   ,
];

SearchFilter::equal(string $searchKey, bool $tokenize = true): string

$search = [ 
    SearchFilter::andOr() => [
        SearchFilter::equal('status') => $statusDraf,
        SearchFilter::equal('status') => $statusPending,
];

sql
WHERE 1 = 1 AND (amount >= :amountMin AND amount <= :amountMax)