PHP code example of cryonighter / formula-doctrine-bundle

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

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

    

cryonighter / formula-doctrine-bundle example snippets

#[ORM\Entity]
class Customer
{
    #[Formula('(SELECT COUNT(*) FROM orders o WHERE o.customer_id = {this}.id)')]
    public int $orderCount = 0;
}

#[ORM\Entity]
class Customer
{
    #[Formula('SELECT COUNT(o) FROM App\Entity\Order o WHERE o.customer = {this}')]
    public int $orderCount = 0;
}

return [
    // ...
    Cryonighter\FormulaDoctrine\FormulaDoctrineBundle::class => ['all' => true],
];

use Cryonighter\FormulaDoctrine\Attribute\Formula;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
#[ORM\Table(name: 'customers')]
class Customer
{
    #[ORM\Id, ORM\Column, ORM\GeneratedValue]
    public int $id;

    #[ORM\Column]
    public string $name;

    // DQL — must NOT be enclosed in parentheses
    #[Formula('SELECT COUNT(o) FROM App\Entity\Order o WHERE o.customer = {this}')]
    public int $orderCount = 0;

    // Native SQL — must be enclosed in parentheses
    #[Formula('(SELECT COALESCE(SUM(oi.price), 0) FROM order_items oi JOIN orders o ON oi.order_id = o.id WHERE o.customer_id = {this}.id)')]
    public float $totalRevenue = 0.0;

    // Nullable formula
    #[Formula('(SELECT MAX(o.created_at) FROM orders o WHERE o.customer_id = {this}.id)')]
    public ?string $lastOrderDate = null;
}

$customers = $entityManager
    ->createQuery('SELECT c FROM App\Entity\Customer c')
    ->getResult();

foreach ($customers as $customer) {
    echo $customer->orderCount;    // populated from subquery
    echo $customer->totalRevenue;  // populated from subquery
}

$customers = $entityManager
    ->createQueryBuilder()
    ->select('c')
    ->from(Customer::class, 'c')
    ->where('c.name LIKE :name')
    ->setParameter('name', '%Acme%')
    ->getQuery()
    ->getResult();

class CustomerRepository extends ServiceEntityRepository
{
    public function findTopCustomers(int $limit): array
    {
        return $this->createQueryBuilder('c')
            ->orderBy('c.id', 'ASC')
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
        // $result[0]->totalRevenue is populated automatically
    }
}

$customerRepository = $this->em->getRepository(Customer::class);

$customers = $customerRepository->findAll();

echo $customer[0]->orderCount;    // populated from subquery
echo $customer[0]->totalRevenue;  // populated from subquery

// DQL
$customers = $entityManager
    ->createQuery('SELECT c FROM App\Entity\Customer c WHERE c.orderCount > :minOrders')
    ->setParameter('minOrders', 5)
    ->getResult();

// QueryBuilder
$customers = $entityManager
    ->createQueryBuilder()
    ->select('c')
    ->from(Customer::class, 'c')
    ->where('c.totalRevenue >= :minRevenue')
    ->setParameter('minRevenue', 1000.0)
    ->getQuery()
    ->getResult();

// Repository findBy()
$customers = $customerRepository->findBy(['orderCount' => 10]);

// DQL
$customers = $entityManager
    ->createQuery('SELECT c FROM App\Entity\Customer c ORDER BY c.totalRevenue DESC')
    ->getResult();

// QueryBuilder
$customers = $entityManager
    ->createQueryBuilder()
    ->select('c')
    ->from(Customer::class, 'c')
    ->orderBy('c.orderCount', 'DESC')
    ->getQuery()
    ->getResult();

// Repository findBy() with ordering
$customers = $customerRepository->findBy(
    [],
    ['totalRevenue' => 'DESC']
);

// Group customers by order count and filter groups
$result = $entityManager
    ->createQuery('
        SELECT c.orderCount, COUNT(c.id) as customerCount, AVG(c.totalRevenue) as avgRevenue
        FROM App\Entity\Customer c
        GROUP BY c.orderCount
        HAVING c.orderCount >= :minOrders AND COUNT(c.id) > :minCustomers
        ORDER BY c.orderCount DESC
    ')
    ->setParameter('minOrders', 3)
    ->setParameter('minCustomers', 1)
    ->getResult();

// Result example:
// [
//   ['orderCount' => 10, 'customerCount' => 5, 'avgRevenue' => 15000.50],
//   ['orderCount' => 7,  'customerCount' => 3, 'avgRevenue' => 8500.25],
//   ...
// ]

$result = $entityManager
    ->createQuery('
        SELECT c.orderCount, COUNT(c.id) as total
        FROM App\Entity\Customer c
        WHERE c.totalRevenue > :minRevenue
        GROUP BY c.orderCount
        HAVING c.orderCount BETWEEN :minOrders AND :maxOrders
        ORDER BY c.orderCount DESC
    ')
    ->setParameter('minRevenue', 500.0)
    ->setParameter('minOrders', 2)
    ->setParameter('maxOrders', 10)
    ->getResult();

$result = $entityManager
    ->createQueryBuilder()
    ->select(
        'SUM(c.orderCount) as totalOrders',
        'AVG(c.totalRevenue) as avgRevenue',
        'MAX(c.totalRevenue) as maxRevenue',
        'MIN(c.totalRevenue) as minRevenue',
    )
    ->from(Customer::class, 'c')
    ->getQuery()
    ->getSingleResult();

// Result example:
// [
//   'totalOrders' => 42,
//   'avgRevenue'  => 1500.50,
//   'maxRevenue'  => 9800.00,
//   'minRevenue'  => 0.0,
// ]

// Categorise customers by revenue tier
$result = $entityManager
   ->createQuery('
        SELECT c.name, c.totalRevenue,
            CASE
                WHEN c.totalRevenue = 0    THEN \'none\'
                WHEN c.totalRevenue < 500  THEN \'low\'
                WHEN c.totalRevenue < 5000 THEN \'medium\'
                ELSE                            \'high\'
            END as revenueCategory
        FROM App\Entity\Customer c
        ORDER BY c.totalRevenue ASC
  ')
  ->getResult();

// Result example:
// [
//   ['name' => 'Alice', 'totalRevenue' => 0.0,    'revenueCategory' => 'none'],
//   ['name' => 'Bob',   'totalRevenue' => 320.0,  'revenueCategory' => 'low'],
//   ['name' => 'Carol', 'totalRevenue' => 1500.0, 'revenueCategory' => 'medium'],
//   ['name' => 'Dave',  'totalRevenue' => 9800.0, 'revenueCategory' => 'high'],
// ]

// CASE WHEN in ORDER BY — push inactive customers to the end
$result = $entityManager
    ->createQuery('
        SELECT c.name, c.orderCount
        FROM App\Entity\Customer c
        ORDER BY CASE WHEN c.orderCount = 0 THEN 1 ELSE 0 END ASC, c.orderCount DESC
    ')
    ->getResult();

#[Formula('(SELECT MAX(o.total) FROM orders o WHERE o.customer_id = {this}.id)')]
public ?float $maxOrderTotal = null;

// {this} → c0_ (SQL table alias)
#[Formula('(SELECT COUNT(*) FROM orders o WHERE o.customer_id = {this}.id)')]
public int $orderCount = 0;

// {this} → the root entity alias
#[Formula('SELECT COUNT(o) FROM App\Entity\Order o WHERE o.customer = {this}')]
public int $orderCount = 0;

#[Formula(
    sql: '(SELECT COUNT(*) FROM orders o WHERE o.customer_id = {this}.id)',
    alias: 'total_orders',
)]
public int $orderCount = 0;

#[ORM\Entity]
class Customer
{
    #[ORM\ManyToOne(targetEntity: Country::class)]
    public Country $country;

    // DQL formula — exposed under alias 'orders'
    #[Formula('SELECT COUNT(o) FROM App\Entity\Order o WHERE o.customer = {this}', alias: 'orders')]
    public int $orderCount = 0;

    // SQL formula — exposed by property name 'totalRevenue'
    #[Formula('(SELECT COALESCE(SUM(oi.price), 0) FROM order_items oi JOIN orders o ON oi.order_id = o.id WHERE o.customer_id = {this}.id)')]
    public float $totalRevenue = 0.0;
}

#[ORM\Entity]
class Country
{
    // SQL formula — references Customer.orderCount by its alias 'orders'
    #[Formula('(SELECT COALESCE(SUM(c.orders), 0) FROM customers c WHERE c.country_id = {this}.id)')]
    public int $customerOrderCount = 0;

    // DQL formula — references Customer.totalRevenue by property name
    #[Formula('SELECT COALESCE(SUM(c.totalRevenue), 0) FROM App\Entity\Customer c WHERE c.country = {this}')]
    public float $customerRevenue = 0.0;
}

// Update all customers who placed 10 or more orders
$affected = $entityManager
    ->createQuery('UPDATE App\Entity\Customer c SET c.name = :newName WHERE c.orderCount >= :min')
    ->setParameter('newName', 'VIP')
    ->setParameter('min', 10)
    ->execute();

// Delete customers who have never placed an order
$affected = $entityManager
    ->createQuery('DELETE App\Entity\Customer c WHERE c.orderCount = :count')
    ->setParameter('count', 0)
    ->execute();

php
return [
    // ... other bundles ...
    Stof\DoctrineExtensionsBundle\StofDoctrineExtensionsBundle::class => ['all' => true],
    Cryonighter\FormulaDoctrine\FormulaDoctrineBundle::class => ['all' => true], // ← last
];