PHP code example of andanteproject / shared-query-builder

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

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

    

andanteproject / shared-query-builder example snippets

use Andante\Doctrine\ORM\SharedQueryBuilder;

// $qb instanceof Doctrine\ORM\QueryBuilder
// $userRepository instanceof Doctrine\ORM\EntityRepository
$qb = $userRepository->createQueryBuilder('u');
// Let's wrap query builder inside our decorator.
// We use $sqb as acronym of "Shared Query Builder"
$sqb = SharedQueryBuilder::wrap($qb);

// $sqb instanceof Andante\Doctrine\ORM\SharedQueryBuilder
// $qb instanceof Doctrine\ORM\QueryBuilder
$qb = $sqb->unwrap();

if($sqb->hasEntity(User::class)) // bool returned 
{ 
    // Apply some query criteria only if this query builder is handling the User entity
}

$userAlias = $sqb->getAliasForEntity(User::class); // string 'u' returned 

if($sqb->hasEntity(User::class)) // bool returned 
{ 
    $sqb
        ->andWhere(
            $sqb->expr()->eq(
                $sqb->withAlias(User::class, 'email'), // string 'u.email'
                ':email_value'
            )
        )
        ->setParameter('email_value', '[email protected]')
    ;    
} 

$entityClass = $sqb->getEntityForAlias('u'); // string 'App\Entity\User' returned

$allAliases = $sqb->getAllAliases(true);

// Common join methods
$sqb->join(/* args */);
$sqb->innerJoin(/* args */);
$sqb->leftJoin(/* args */);

// Lazy join methods
$sqb->lazyJoin(/* args */);
$sqb->lazyInnerJoin(/* args */);
$sqb->lazyLeftJoin(/* args */);

// They work with all the ways you know you can perform joins in Doctrine
// A: $sqb->lazyJoin('u.address', 'a') 
// or B: $sqb->lazyJoin('Address::class', 'a', Expr\Join::WITH, $sqb->expr()->eq('u.address','a')) 

// Named proposal
$proposal = $sqb->createEmptyProposal('building_filter');

// Anonymous proposal (unique name generated automatically)
$proposal = $sqb->createEmptyProposal();

$proposal = $sqb->createEmptyProposal('status_filter');

// Conditions: use withAlias() so the SQB resolves the entity alias (e.g. User → 'u')
$proposal->andWhere(
    $proposal->expr()->eq(
        $proposal->withAlias(User::class, 'status'),
        $proposal->withUniqueImmutableParameter(':status', 'active')
    )
);
$proposal->orWhere(
    $proposal->expr()->eq(
        $proposal->withAlias(User::class, 'role'),
        $proposal->withUniqueImmutableParameter(':role', 'admin')
    )
);

// Joins: use withAlias() for the association path
$proposal->innerJoin($proposal->withAlias(User::class, 'profile'), 'p');

// Optional: select, groupBy, orderBy, having (withAlias for each path)
$proposal->addSelect($proposal->withAlias(User::class, 'id'))
    ->addGroupBy($proposal->withAlias(User::class, 'id'))
    ->addOrderBy($proposal->withAlias(User::class, 'createdAt'), 'DESC');

$sqb->select('u')->from(User::class, 'u');

$statusProposal = $sqb->createEmptyProposal('status');
$statusProposal->andWhere(
    $statusProposal->expr()->eq(
        $statusProposal->withAlias(User::class, 'status'),
        $statusProposal->withUniqueImmutableParameter(':status', 'active')
    )
);

$sqb->andWhere($statusProposal);
// Now the main query has the proposal’s condition and parameter; its joins/select/etc. would be applied too if we had added any.

$proposal1 = $sqb->createEmptyProposal('p1');
$proposal1->andWhere(
    $proposal1->expr()->eq(
        $proposal1->withAlias(User::class, 'role'),
        $proposal1->withUniqueImmutableParameter(':role', 'admin')
    )
);

$proposal2 = $sqb->createEmptyProposal('p2');
$proposal2->andWhere(
    $proposal2->expr()->eq(
        $proposal2->withAlias(User::class, 'role'),
        $proposal2->withUniqueImmutableParameter(':role', 'editor')
    )
);

$sqb->andWhere($sqb->expr()->orX($proposal1, $proposal2));
// Main query has (condition1 OR condition2) and both parameters.

// StatusFilter.php
use Andante\Doctrine\ORM\SharedQueryBuilder\Proposal;
use Symfony\Component\HttpFoundation\Request;

class StatusFilter implements FilterInterface
{
    public function buildProposal(Request $request, Proposal $proposal): Proposal
    {
        $status = $request->query->get('status');
        if ($status === null) {
            return $proposal;
        }
        $proposal->andWhere(
            $proposal->expr()->eq(
                $proposal->withAlias(User::class, 'status'),
                $proposal->withUniqueImmutableParameter(':status', $status)
            )
        );
        return $proposal;
    }
}

// UserController.php (upper level: create proposal, pass to filter, merge here)
$statusFilter = new StatusFilter();
$proposal = $sqb->createEmptyProposal('status_filter');
$statusFilter->buildProposal($request, $proposal);
if ($proposal->hasConditions()) {
    $sqb->andWhere($proposal);
}

$sqb = SharedQueryBuilder::wrap($userRepository->createQueryBuilder('u'));
$sqb
    ->lazyJoin($sqb->withAlias(User::class, 'address'), 'a')
    ->lazyJoin($sqb->withAlias(Address::class, 'building'), 'b')
    // Let's add a WHERE condition that do not need our lazy joins
    ->andWhere(
        $sqb->expr()->eq($sqb->withAlias(User::class, 'verifiedEmail'), ':verified_email')
    )
    ->setParameter('verified_email', true)
;

$users = $sqb->getQuery()->getResult();
// DQL executed:
//     SELECT u
//     FROM App\Entity\User
//     WHERE u.verifiedEmail = true

// BUT if we use the same Query Builder to filter by building.name:
$buildingNameFilter = 'Building A';
$sqb
    ->andWhere(
        $sqb->expr()->eq($sqb->withAlias(Building::class, 'name'), ':name_value')
    )
    ->setParameter('name_value', $buildingNameFilter)
;
$users = $sqb->getQuery()->getResult();
// DQL executed:
//     SELECT u
//     FROM App\Entity\User
//       JOIN u.address a
//       JOIN a.building b
//     WHERE u.verifiedEmail = true
//       AND b.name = 'Building A'

// How you could achieve this without SharedQueryBuilder
$buildingNameFilter = 'Building A';
$qb = $userRepository->createQueryBuilder('u');
$qb
    ->andWhere(
        $qb->expr()->eq('u.verifiedEmail', ':verified_email')
    )
    ->setParameter('verified_email', true);
    
if(!empty($buildingNameFilter)){
    $qb
        ->lazyJoin('u.address', 'a')
        ->lazyJoin('a.building', 'b')
        ->andWhere(
            $qb->expr()->eq('b.name', ':building_name_value')
        )
        ->setParameter('building_name_value', $buildingNameFilter)
    ;
}

$users = $qb->getQuery()->getResult(); // Same result as example shown before
// But this has some down sides further explained

// UserController.php
class UserController extends Controller
{
    public function index(Request $request, UserRepository $userRepository) : Response
    {
        $qb = $userRepository->createQueryBuilder('u');
        $qb
            ->andWhere(
                $qb->expr()->eq('u.verifiedEmail', ':verified_email')
            )
            ->setParameter('verified_email', true);
        
        // Now Apply some optional filters from Request
        // Let's suppose we have an "applyFilters" method which is giving QueryBuilder and Request
        // to an array of classes responsible for filtering query results.  
        $this->applyFilters($qb, $request);
        
        // Maybe have some pagination logic here too. Check KnpLabs/knp-components which is perfect for this.
        
        $users = $qb->getQuery()->getResults();
        // Build our response with User entities list.
    }
}

// BuildingNameFilter.php
class BuildingNameFilter implements FilterInterface
{
    public function filter(QueryBuilder $qb, Request $request): void
    {
        $buildingNameFilter = $request->query->get('building-name');
        if(!empty($buildingNameFilter)){
            $qb
                ->join('u.address', 'a')
                ->join('a.building', 'b')
                ->andWhere(
                    $qb->expr()->eq('b.name', ':building_name_value')
                )
                ->setParameter('building_name_value', $buildingNameFilter)
            ;
        }
    }
}

// UserController.php
use Andante\Doctrine\ORM\SharedQueryBuilder;

class UserController extends Controller
{
    public function index(Request $request, UserRepository $userRepository) : Response
    {
        $sqb = SharedQueryBuilder::wrap($userRepository->createQueryBuilder('u'));
        $sqb
            // Please note: Sure, you can mix "normal" join methods and "lazy" join methods
            ->lazyJoin($sqb->withAlias(User::class, 'address'), 'a')
            ->lazyJoin($sqb->withAlias(Address::class, 'building'), 'b')
            ->andWhere($sqb->expr()->eq($sqb->withAlias(User::class, 'verifiedEmail'), ':verified_email'))
            ->setImmutableParameter('verified_email', true);
        
        // Now Apply some optional filters from Request
        // Let's suppose we have an "applyFilters" method which is giving QueryBuilder and Request
        // to an array of classes responsible for filtering query results.  
        $this->applyFilters($sqb, $request);
        
        // Maybe have some pagination logic here too.
        // You probably need to unwrap the Query Builder now for this
        $qb = $sqb->unwrap();
        
        $users = $qb->getQuery()->getResult();
        // Build our response with User entities list.
    }
}

// BuildingNameFilter.php
use Andante\Doctrine\ORM\SharedQueryBuilder;

class BuildingNameFilter implements FilterInterface
{
    public function filter(SharedQueryBuilder $sqb, Request $request): void
    {
        $buildingNameFilter = $request->query->get('building-name');
        // Let's check if Query has a Building entity in from or join DQL parts 🙌
        if($sqb->hasEntity(Building::class) && !empty($buildingNameFilter)){
            $sqb
                ->andWhere(
                    // We can ask Query builder for the "Building" alias instead of guessing it/retrieve somewhere else 💋
                    $sqb->expr()->eq($sqb->withAlias(Building::class, 'name'), ':building_name_value')
                    // You can also use $sqb->getAliasForEntity(Building::class) to discover alias is 'b';
                )
                ->setImmutableParameter('building_name_value', $buildingNameFilter)
            ;
        }
    }
}

// BuildingNameFilter.php
use Andante\Doctrine\ORM\SharedQueryBuilder\Proposal;
use Symfony\Component\HttpFoundation\Request;

class BuildingNameFilter implements FilterInterface
{
    public function buildProposal(Request $request, Proposal $proposal): Proposal
    {
        $buildingName = $request->query->get('building-name');
        if ($buildingName === null || $buildingName === '' || !$proposal->hasEntity(Building::class)) {
            return $proposal;
        }
        $proposal->andWhere(
            $proposal->expr()->eq(
                $proposal->withAlias(Building::class, 'name'),
                $proposal->withUniqueImmutableParameter(':building_name', $buildingName)
            )
        );
        return $proposal;
    }
}

// UserController.php
use Andante\Doctrine\ORM\SharedQueryBuilder;
use Andante\Doctrine\ORM\SharedQueryBuilder\Proposal;

$sqb = SharedQueryBuilder::wrap($userRepository->createQueryBuilder('u'));
$sqb
    ->lazyJoin($sqb->withAlias(User::class, 'address'), 'a')
    ->lazyJoin($sqb->withAlias(Address::class, 'building'), 'b')
    ->andWhere($sqb->expr()->eq($sqb->withAlias(User::class, 'verifiedEmail'), ':verified_email'))
    ->setImmutableParameter('verified_email', true);

// Each filter receives an empty proposal, fills it, returns it; we merge here
$statusProposal = $sqb->createEmptyProposal('status_filter');
(new StatusFilter())->buildProposal($request, $statusProposal);
if ($statusProposal->hasConditions()) {
    $sqb->andWhere($statusProposal);
}

$buildingNameProposal = $sqb->createEmptyProposal('building_name_filter');
(new BuildingNameFilter())->buildProposal($request, $buildingNameProposal);
if ($buildingNameProposal->hasConditions()) {
    $sqb->andWhere($buildingNameProposal);
}

$users = $sqb->unwrap()->getQuery()->getResult();

// $sqb instanceof Andante\Doctrine\ORM\SharedQueryBuilder

// set a common Query Builder parameter, as you are used to 
$sqb->setParameter('parameter_name', 'parameterValue');

// set an immutable Query Builder parameter. It cannot be changed; otherwise an exception will be raised.
$sqb->setImmutableParameter('immutable_parameter_name', 'parameterValue');

// get a collection of all query parameters (commons + immutables!)
$sqb->getParameters();

// get a collection of all immutable query parameters (exclude commons)
$sqb->getImmutableParameters();

// Sets a parameter and return parameter name as string instead of $sqb.
$sqb->withParameter(':parameter_name', 'parameterValue');
$sqb->withImmutableParameter(':immutable_parameter_name', 'parameterValue');
// This allows you to write something like this:
$sqb->expr()->eq('building.name', $sqb->withParameter(':building_name_value', $buildingNameFilter));

// The two following methods sets "unique" parameters. See "Unique parameters" doc section for more...
$sqb->withUniqueParameter(':parameter_name', 'parameterValue');
$sqb->withUniqueImmutableParameter(':parameter_name', 'parameterValue');

$sqb
    ->andWhere(
        $sqb->expr()->eq(
            $sqb->withAlias(Building::class, 'name'), 
            ':building_name_value'
        )
    )
    ->setImmutableParameter('building_name_value', $buildingNameFilter)
;

$sqb
    ->andWhere(
        $sqb->expr()->eq(
            $sqb->withAlias(Building::class, 'name'), 
            $sqb->withImmutableParameter(':building_name_value', $buildingNameFilter) // return ":building_name_value" but also sets immutable parameter
        )
    )
;

$sqb
    ->andWhere(
        $sqb->expr()->eq(
           'building.name', 
            $sqb->withUniqueParameter(':name', $buildingNameFilter) // return ":param_name_4b3403665fea6" making sure parameter name is not already in use and sets parameter value.
        )
    )
    ->andWhere(
        $sqb->expr()->gte(
           'building.createdAt', 
            $sqb->withUniqueImmutableParameter(':created_at', new \DateTime('-5 days ago'))  // return ":param_created_at_5819f3ad1c0ce" making sure parameter name is not already in use and sets immutable parameter value.
        )
    )
    ->andWhere(
        $sqb->expr()->lte(
           'building.createdAt',
            $sqb->withUniqueImmutableParameter(':created_at', new \DateTime('today midnight'))  // return ":param_created_at_604a8362bf00c" making sure parameter name is not already in use and sets immutable parameter value.
        )
    )
;

/* 
 * Query Builder has now 3 parameters:
 *  - param_name_4b3403665fea6 (common)
 *  - param_created_at_5819f3ad1c0ce (immutable)
 *  - param_created_at_604a8362bf00c (immutable)
 */