Skip to content

Query builder

What is QueryBuilder ?

QueryBuilder is a helper classes system that help you build complex queries to database.

At final, it will generate a sql query that can be executed which result can be interpreted by manager to build entities and collections as response.

By convention, we will put all our queries in your managers class to keep them in one place as a repository.

Basic operations

Instanciate query

To build a query, just call createQueryBuilder method of manager : 

<?php
$query = $customerManager->createQueryBuilder();

Get query result

To get result, use getResult method of manager : 

<?php
$allCustomers = $customerManager->getResult($query);

Put it in manager

As we say previously, we want to keep all our requests in managers. Here is our customer manager code : 

<?php
    /* File : EntityManager/UserManager.php */
    namespace MyApp\EntityManager;

    use Small\SwooleEntityManager\EntityManager\AbstractRelationnalManager;
    use Small\SwooleEntityManager\EntityManager\Attribute\Connection;
    use Small\SwooleEntityManager\EntityManager\Attribute\Entity;
    use MyApp\Entity\CustomerEntity
    use MyApp\Collection\CustomerCollection

    #[Connection(dbTableName: 'customer')]
    #[Entity(CustomerEntity::class)]
    class CustomerManager extends AbstractRelationnalManager
    {

        /**
         * Get all customers
         * @return CustomerCollection
         */
        public function getAllCustomers(): CustomerCollection
        {

            $query = $this->createQueryBuilder();

            return $this->getResult($query);

        }

    }

Where clause

The where clause allow you to filter your results. It comes with conditions instructions.

For example, we want to get all customers of type id 1 : 

<?php
$query->where()
    ->firstCondition(
        $query->getFieldForCondition('idType'), 
        ConditionOperatorType::equal,
        1
    )
;

The where clause come always with at least one condition using firstCondition method.

Condition take 3 parameters :

  • A field, a value or a binding
  • An operator
  • A field, a value or a binding

A field is given by getFieldForCondition method. Here we have taken idType field.

In result, we have the following sql result : 

SELECT DISTINCT `Customer`.`id` AS `Customer&id`, 
                `Customer`.`email` AS `Customer&email`, 
                `Customer`.`firstname` AS `Customer&firstname`, 
                `Customer`.`lastname` AS `Customer&lastname`, 
                `Customer`.`id_type` AS `Customer&idType` 
FROM `customer` AS `Customer` 
WHERE `Customer`.`id_type` = '1'

Now we want to build a getCustomerByType injecting a parameter to generalise the request. To do that, we use binding with the setParameter method : 

<?php
/**
 * Get customer by type
 * @param int $idType
 * @return CustomerCollection
 */
public function getCusomersByType(int $idType): array
{

    $query = $this->createQueryBuilder();

    $query->where()
        ->firstCondition(
            $query->getFieldForCondition('idType'), 
            ConditionOperatorType::equal,
             ':idType'
         )
    ;

    $query->setParameter('idType', $idType);

    return $this->getResult($query);

}

Operators

All Mysql operators are implemented :

  • =
  • <
  • >
  • <=
  • >=
  • !=
  • <>
  • %
  • like
  • not like
  • not regexpr
  • regexpr
  • is
  • is not
  • exists
  • not exists
  • in
  • not in

Join models

Now, we want to get customers with customer types and orders.

To do that, we use join methods on toOne and toMany fields. Here is now our getAllCustomers request : 

<?php
/**
 * Get all customers
 * @return CustomerCollection
 */
public function getAllCustomers(): array
{
    $query = $this->createQueryBuilder('customer')
        ->innerJoin('type')->endJoin()
        ->leftJoin('orders')->endJoin()
    ;

    return $this->getResult($query);
}

This request will load type and orders properties with corresponding models in one request to database : 

<?php
$customers = $customerManager->getAllCustomers();
echo $customers[0]->getType()->getLabel();
foreach ($customers[0]->getOrders() as $order) {
    echo $order->getTotal();
}

Here are the possible join methods : * join (innerJoin shortcut) * innerJoin * leftJoin * rigthJoin*

You can also use conditions in joins. For example, we want to filter orders by status 'prepared' of customers : 

<?php
/**
 * Get all customers with prepared orders
 * @return CustomerCollection
 */
public function getCustomersOrdersPrepared(): array
{

    $query = $this->createQueryBuilder('customer');
    $query->innerJoin('type')->endJoin()
        ->leftJoin('orders')
            ->joinCondition()
                ->andCondition(
                    $query->getFieldForCondition('status', 'orders'), 
                    ConditionOperatorType::equal, 
                    'prepared'
                )
            ->endJoinCondition()
        ->endJoin()
    ;

    return $this->getResult($query);

}

More complex conditions

Multiple conditions

Of course, you can use more than one condition in where clause of in joinCondition clause : 

<?php
$query = $customerManager->createQueryBuilder('customer');
$query->innerJoin('type')->endJoin()
    ->leftJoin('orders')
        ->joinCondition()
            ->andCondition(
                $query->getFieldForCondition('status', 'orders'),
                ConditionOperatorType::equal,
                'prepared'
            )
            ->andCondition(
                $query->getFieldForCondition('total', 'orders'), 
                ConditionOperatorType::superior,
                100
            )
        ->endJoinCondition()
    ->endJoin()
;
$query->where()
    ->firstCondition(
        $query->getFieldForCondition('idType'),
        ConditionOperatorType::equal, 
        '1'
    )
    ->andCondition(
        $query->getFieldForCondition('firstname'), 
        ConditionOperatorType::like, 
        'john%'
    )
;

In this request, we get all customers of type id 1, which firstname begin by 'john' and get only prepared orders of more than 100$.

Here are possible conditions : * firstCondition * andCondition * orCondition * xorCondition

Brackets

To continue in complex requests, you can use brackets on your where clause or joinCondition clause : 

<?php
$query = $orderManager->createQueryBuilder('order');
$query->where()
    ->firstCondition(
        $query->getFieldForCondition('total'),
        ConditionOperatorType::superior, 
        100
    )
    ->andCondition(
        $query->getFieldForCondition('total'), 
        ConditionOperatorType::inferior,
        1000
    )
    ->andBracket()
        ->firstCondition(
            $query->getFieldForCondition('status'), 
            ConditionOperatorType::equal, 
            'prepared'
        )
        ->orCondition(
            $query->getFieldForCondition('status'), 
            ConditionOperatorType::equal, 
            'in progress'
        )
    ->endBracket()
    ->andCondition(
        $query->getFieldForCondition('idTax'), 
        ConditionOperatorType::equal,
        5
    )
;

This request will return all orders between 100$ and 1000$, prepared or in progess which tax id is 5.

Note that the first condition of bracket must be firstCondition.

Sub queries

You can also create sub queries.

The getFieldForCondition method work even it the target query is not the from query.

Let see an example : You want to list customers that have at least an order prepared.

Let start making sub query to list prepared orders :

<?php
$subquery = $orderManager->createQueryBuilder('order');
$subquery->where()
    ->firstCondition(
        $query->getFieldForCondition('status'), 
        ConditionOperatorType::equal, 
        'prepared'
    )
;

Now we build the customer (main) query :

<?php
$query = $orderManager->createQueryBuilder('customer');
$query->where()
    ->firstCondition($subquery, ConditionOperatorType::exists)
;

And complete the sub query with cross query condition : 

<?php
$subquery->getWhere()
    ->andCondition(
        $subquery->getFieldForCondition('idCustomer'), 
        ConditionOperatorType::equal, 
        $query->getFieldForCondition('id')
    )
;

Pagination

If you want to limit results, you can use the paginate method.

<?php
/**
 * Get customers by page
 * @param $page
 * @param int $pageSize
 * @return CustomerCollection
 */
public function getCustomers($page, $pageSize = 10): array
{

    $query = $this->createQueryBuilder('customer');

    $query->paginate($page, $pageSize);

    return $this->getResult($query);

}

The first parameter is page number and the second parameter is the number of result in each page.

Order by

You can order the result by one or more fields : 

<?php
/**
 * Get all customers
 * @return CustomerCollection
 */
public function getAllCustomers(): array
{

    $query = $this->createQueryBuilder('customer')
        ->innerJoin('type')->endJoin()
        ->leftJoin('orders')->endJoin()
    ;

    $query->addOrderBy('email', null, OrderByDirectionType::asc);
    $query->addOrderBy('date', 'orders', OrderByDirectionType::desc);

    return $this->getResult($query);
}

  • The first parameter is the field.
  • The second parameter is the model of field
  • The third is an enum OrderByDirectionType corresponding to the order sens of sort (asc and desc)

By default, (if no order by is specified), the result is sorted by primary keys.

In this example, the result will be sorted by email (ascending) and in the orders dependence by date (descending).

Next chapter : Update builder