PHP code example of rgalstyan / symfony-aggregated-queries

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

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

    

rgalstyan / symfony-aggregated-queries example snippets

$qb = $entityManager->createQueryBuilder();
$partners = $qb
    ->select('p, profile, country')
    ->from(Partner::class, 'p')
    ->leftJoin('p.profile', 'profile')
    ->leftJoin('p.country', 'country')
    ->getQuery()
    ->getResult();

$partners = $partnerRepository->aggregatedQuery()
    ->withJsonRelation('profile', ['id', 'name', 'email'])
    ->withJsonRelation('country', ['id', 'name', 'code'])
    ->withJsonCollection('promocodes', ['id', 'code', 'discount'])
    ->withCount('promocodes')
    ->getResult();

return [
    // ...
    Rgalstyan\SymfonyAggregatedQueries\Bundle\SymfonyAggregatedQueriesBundle::class => ['all' => true],
];

namespace App\Repository;

use App\Entity\Partner;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use Rgalstyan\SymfonyAggregatedQueries\Repository\AggregatedRepositoryTrait;

class PartnerRepository extends ServiceEntityRepository
{
    use AggregatedRepositoryTrait;
    
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, Partner::class);
    }
    
    public function findAllOptimized(): array
    {
        return $this->aggregatedQuery()
            ->withJsonRelation('profile', ['id', 'name', 'email'])
            ->withJsonRelation('country', ['id', 'name', 'code'])
            ->withJsonCollection('promocodes', ['id', 'code', 'discount'])
            ->withCount('promocodes')
            ->where('status', 'active')
            ->orderBy('createdAt', 'DESC')
            ->limit(50)
            ->getResult();
    }
}

namespace App\Entity;

use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: PartnerRepository::class)]
#[ORM\Table(name: 'partners')]
class Partner
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;
    
    #[ORM\Column(length: 255)]
    private ?string $name = null;
    
    #[ORM\Column(length: 50)]
    private ?string $status = null;
    
    #[ORM\ManyToOne(targetEntity: PartnerProfile::class)]
    #[ORM\JoinColumn(name: 'profile_id', referencedColumnName: 'id')]
    private ?PartnerProfile $profile = null;
    
    #[ORM\ManyToOne(targetEntity: Country::class)]
    #[ORM\JoinColumn(name: 'country_id', referencedColumnName: 'id')]
    private ?Country $country = null;
    
    #[ORM\OneToMany(targetEntity: PartnerPromocode::class, mappedBy: 'partner')]
    private Collection $promocodes;
    
    // Getters/setters...
}

namespace App\Service;

use App\Repository\PartnerRepository;

class PartnerService
{
    public function __construct(
        private readonly PartnerRepository $partnerRepository
    ) {}
    
    public function getAllPartnersForApi(): array
    {
        $partners = $this->partnerRepository->findAllOptimized();
        
        // Transform to API format if needed
        return array_map(fn($p) => [
            'id' => $p['id'],
            'name' => $p['name'],
            'profile' => $p['profile'],
            'country' => $p['country'],
            'promocode_count' => $p['promocodes_count'],
        ], $partners);
    }
}

namespace App\Controller;

use App\Service\PartnerService;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\Routing\Annotation\Route;

class PartnerController extends AbstractController
{
    public function __construct(
        private readonly PartnerService $partnerService
    ) {}
    
    #[Route('/api/partners', methods: ['GET'])]
    public function index(): JsonResponse
    {
        $partners = $this->partnerService->getAllPartnersForApi();
        
        return $this->json($partners);
    }
}

[
    [
        'id' => 1,
        'name' => 'Partner A',
        'status' => 'active',
        'created_at' => '2024-01-15 10:30:00',
        'profile' => [                         // array or null
            'id' => 10,
            'name' => 'John Doe',
            'email' => '[email protected]'
        ],
        'country' => [                         // array or null
            'id' => 1,
            'name' => 'USA',
            'code' => 'US'
        ],
        'promocodes' => [                      // always array, never null
            ['id' => 1, 'code' => 'SAVE10', 'discount' => 10],
            ['id' => 2, 'code' => 'SAVE20', 'discount' => 20],
        ],
        'promocodes_count' => 2
    ],
    // ...
]

namespace App\Service;

use App\Entity\Partner;
use Rgalstyan\SymfonyAggregatedQueries\AggregatedQueryBuilder;

class ReportService
{
    public function __construct(
        private readonly AggregatedQueryBuilder $queryBuilder
    ) {}
    
    public function generatePartnerReport(): array
    {
        return $this->queryBuilder
            ->from(Partner::class)
            ->withJsonRelation('profile', ['id', 'name'])
            ->withJsonRelation('country', ['id', 'name'])
            ->withCount('promocodes')
            ->where('status', 'active')
            ->orderBy('createdAt', 'DESC')
            ->getResult();
    }
    
    public function generateStatsReport(): array
    {
        return $this->queryBuilder
            ->from(Partner::class)
            ->withCount('orders')
            ->withCount('promocodes')
            ->withCount('discountRules')
            ->where('status', 'active')
            ->getResult();
    }
}

$partners = $partnerRepository->aggregatedQuery()
    ->withJsonRelation('profile')
    ->where('status', 'active')
    ->where('countryId', 5)
    ->whereIn('typeId', [1, 2, 3])
    ->orderBy('name', 'ASC')
    ->limit(100)
    ->offset(50)
    ->getResult();

$partners = $partnerRepository->aggregatedQuery()
    ->withJsonRelation('profile')
    ->withJsonCollection('promocodes', ['id', 'code', 'discount', 'expiresAt'])
    ->withJsonCollection('discountRules', ['id', 'type', 'value'])
    ->getResult();

// Result structure:
[
    'id' => 1,
    'profile' => [...],
    'promocodes' => [
        ['id' => 1, 'code' => 'SAVE10', 'discount' => 10, 'expires_at' => '2024-12-31'],
        ['id' => 2, 'code' => 'SAVE20', 'discount' => 20, 'expires_at' => '2025-01-31'],
    ],
    'discount_rules' => [
        ['id' => 1, 'type' => 'percentage', 'value' => 15],
    ]
]

$partners = $partnerRepository->aggregatedQuery()
    ->withJsonRelation('profile')
    ->withCount('promocodes')
    ->withCount('discountRules')
    ->withCount('orders')
    ->getResult();

// Result structure:
[
    'id' => 1,
    'profile' => [...],
    'promocodes_count' => 15,
    'discount_rules_count' => 3,
    'orders_count' => 127
]

// Load single relation (ManyToOne, OneToOne)
->withJsonRelation(string $relation, array $columns = [])

// Load collection (OneToMany)
->withJsonCollection(string $relation, array $columns = [])

// Count related records
->withCount(string $relation)

->where(string $field, mixed $value)
->where(string $field, mixed $value, string $operator = '=')
->whereIn(string $field, array $values)
->orderBy(string $field, string $direction = 'ASC')
->limit(int $limit)
->offset(int $offset)

->getResult()                    // array (default, fastest)
->getResult('array')             // Same as above
->getResult('entity')            // Hydrate into Doctrine entities (slower)
->getOneOrNullResult()           // Get first result or null

->toSql()                        // Get generated SQL
->getParameters()                // Get query parameters
->debug()                        // Enable debug logging

use Doctrine\ORM\EntityManagerInterface;

class DataExportService
{
    public function __construct(
        private readonly PartnerRepository $partnerRepository,
        private readonly EntityManagerInterface $em
    ) {}
    
    public function exportPartners(): void
    {
        $batchSize = 500;
        $offset = 0;
        $csvFile = fopen('partners_export.csv', 'w');
        
        do {
            $partners = $this->partnerRepository->aggregatedQuery()
                ->withJsonRelation('profile', ['id', 'name', 'email'])
                ->withJsonRelation('country', ['id', 'name'])
                ->withCount('orders')
                ->orderBy('id', 'ASC')
                ->limit($batchSize)
                ->offset($offset)
                ->getResult();
            
            if (empty($partners)) {
                break;
            }
            
            foreach ($partners as $partner) {
                fputcsv($csvFile, [
                    $partner['id'],
                    $partner['name'],
                    $partner['profile']['name'] ?? 'N/A',
                    $partner['country']['name'] ?? 'N/A',
                    $partner['orders_count'],
                ]);
            }
            
            $offset += $batchSize;
            
            // Free memory
            unset($partners);
            $this->em->clear();
            
        } while (true);
        
        fclose($csvFile);
    }
}

// Repository
public function findAllTraditional(): array
{
    return $this->createQueryBuilder('p')
        ->select('p, profile, country')
        ->leftJoin('p.profile', 'profile')
        ->leftJoin('p.country', 'country')
        ->getQuery()
        ->getResult();
}

// Result: 3+ queries
// Hydration: Full Doctrine entities
// Performance: Slower
// Memory: Higher

// Repository
public function findAllOptimized(): array
{
    return $this->aggregatedQuery()
        ->withJsonRelation('profile')
        ->withJsonRelation('country')
        ->getResult();
}

// Result: 1 query
// Hydration: None (direct arrays)
// Performance: 6-7x faster
// Memory: 90% less