wyzen-packages/doctrine-simple-query-builder

Easy query builder for doctrine

v3.2.0 2024-03-19 11:49 UTC

Requires

• php: >=8.1
• doctrine/dbal: ^3.4
• doctrine/orm: ^2.13

Requires (Dev)

• ext-sqlite3: *
• phpunit/phpunit: ^9.5
• squizlabs/php_codesniffer: ^3.7
• symfony/var-dumper: ^6.1

MIT

• WYZEN

README

Librairie pour construire des requêtes plus simplement, en utilisant Doctrine.

Version utilisant PHP 8.1 minimum et Doctrine 3.4 minimum

Releases

• use PHP 8.1 minimum and Doctrine 3.4 minimum
• Ajout de debugSql() pour afficher la requete et ses paramètres
• Fixe la fonction addCustomFilter

Simple déclaration

use Wyzen\Doctrine\SimpleQueryBuilder\SimpleQueryBuilder;

$conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams, $config);
$sqb = new SimpleQueryBuilder($conn);

Les opérateurs

Liste des opérateurs fonctionnels

use Wyzen\Doctrine\SimpleQueryBuilder\SimpleQueryBuilderOperator

• EQ
• LT
• LE
• GT
• GE
• NE
• IN
• NOTIN
• ISNULL
• ISNOTNULL
• LIKE
• NOTLIKE
• BETWEEN
• NOTBETWEEN

setTable

$sqb->setTable('usersProfil');
// select sur 'usersProfil'

$sqb->setTable('usersProfilDto');
// select sur 'usersProfilDto'

$sqb->setTable('v_users');
// select sur 'v_users'

$sqb->setTable('usersProfilRepository');
// select sur 'users_profil'

$sqb->setTable('usersProfilDtoRepository');
// select sur 'v_dto_users_profil'

Filters

$sqb = new SimpleQueryBuilder($conn);
$sqb->setTable('users');

$sqb->addFilter('name', 'admin', SimpleQueryBuilderOperator::EQ); // par défaut EQ
$sqb->addGroup('name');
$sqb->addGroups(['name', 'email']);
$sqb->addOrder('name', 'desc');

// same
$sqb->addFilter('name', ['admin', 'guest', 'other'], SimpleQueryBuilderOperator::IN);
$sqb->addFilter('name', ['admin', 'guest', 'other'], SimpleQueryBuilderOperator::EQ);
$sqb->addFilter('name', ['admin', 'guest', 'other']);

$sqb->addFilter('name', ['admin', 'guest', 'other'], SimpleQueryBuilderOperator::NOTIN);
$sqb->addFilter('name', ['admin', 'guest', 'other'], SimpleQueryBuilderOperator::NE);

$sqb->addFilter('name', SimpleQueryBuilderOperator::ISNULL);

$sqb->addFilter('id', [1,10], SimpleQueryBuilderOperator::BETWEEN);

$sqb->addFilter('name', '%admin%', SimpleQueryBuilderOperator::LIKE);

Custom Filters

$sqb = new SimpleQueryBuilder($conn);
$sqb->setTable('users');

$sqb->addCustomFilter('users.user_id=30 or users.user_id=34');
// OR
$sqb->addCustomFilter('users.user_id', 33, SimpleQueryBuilderOperator::GT);

Query

$count = $sqb->executeRowCount('pk_table'); // count(distinct pk_table)
$count = $sqb->executeRowCount(); // count(*)


$rows = $sqb->fetchAll(); // return array

$stmt = $sqb->execute(); // return ResultStatement|int
$rows = $stmt->fetchAll();

Pagination

$limit = 10;
$pagination = new Pagination($limit);

$pagination->clear();
$pagination->isActive(); // Test pagination active

// Getters
$pagination->getLimit(); // Get the limit
$pagination->getPage(); // Get the current page
$pagination->getPages(); // Get the total of pages
$pagination->getResults(); // Get the total of rows (without limit)

// Setters
$pagination->setLimit(10); // Set the limit
$pagination->setPage(1); // Set the current page
$pagination->setPages(4); // Set the total of pages
$pagination->setResults(132); // Set the total of rows (without limit)

$sqb
    ->setPagination($pagination);
    ->setTable('users')
    ->addFilter('email', '%.com%', SimpleQueryBuilderOperator::LIKE)
    ->addOrder('name');
$rows = $sqb->fetchAll();
$sqb->getWhere();
$sqb->getOrderby();
$sqb->getGroupby();

$pagination = $sqb->getPagination();

// To change page
$pagination->setPage(2);
$sqb->setPagination($pagination);
$rows = $sqb->fetchAll();

Custom class

<?php
declare(strict_types=1);

use Doctrine\DBAL\Connection;
use Wyzen\Doctrine\SimpleQueryBuilder\SimpleQueryBuilderAbstract;

/**
* Déclaration de la classe
*/
class SimpleQueryBuilderCustom extends SimpleQueryBuilderAbstract
{
    // Vos propriétés
    const LIMIT = 10;

    /** @inheritDoc */
    function __construct(Connection $conn, ?String $className = null)
    {
        parent::__construct($conn, $className);
    }

    /**
    * Vos fonctions
    */
}