goldoni/php7.2-query-builder

This PHP library is responsible for building SQL query strings, Paginate and Hydration via an object oriented PHP interface.

1.3.0 2018-11-06 16:16 UTC

Requires

• php: >=7.2.0
• pagerfanta/pagerfanta: ^2.0

Requires (Dev)

• friendsofphp/php-cs-fixer: ^2.13
• php-coveralls/php-coveralls: ^2.1
• phpunit/phpunit: ^7.4
• squizlabs/php_codesniffer: ^3.3

MIT

• Goldoni Fouotsa

README

PHP Query builder, Paginate and Hydratation using PDO

Features

• Allows you to perform complex queries with little code

  • select
  • insert
  • update
  • delete
  • where
  • params
  • count
  • orderBy
  • groupBy
  • joins
  • limit
  • fetchAll
  • fetch
  • fetchOrFail
  • execute

• Pagination

  • paginate

• Hydration Ability to return a collection of objects:

  • into(Demo::class)

Getting Started

Create a new PDO instance, and pass the instance to Query:

use Goldoni\Builder\Query;
....
$pdo = new \PDO('mysql:dbname=goldoni;host=localhost;charset=utf8', 'root', 'root');
$query = (new Query($pdo))

Prerequisites

What things you need to install the software and how to install them

"require": {
    "php": ">=7.2.0"
}

Installing

composer require goldoni/php7.2-query-builder

Examples

select

$query = (new Query())->from('users')->select('first_name');

build the query below

SELECT first_name FROM users

insert

$query = (new Query($this->pdo))
            ->insert(
                'users',
                [
                    'first_name' => ':first_name',
                    'last_name'  => ':last_name',
                    'email'      => ':email',
                    'mobile'     => ':mobile',
                    'phone'      => ':phone',
                ]
            );
            
$query1 = (new Query($this->pdo))
            ->insert('users')
            ->value([
                'first_name' => ':first_name',
                'last_name'  => ':last_name',
                'email'      => ':email',
                'phone'      => ':phone'
            ]);

build the query below

INSERT INTO users (first_name, last_name, email, mobile, phone) VALUES (:first_name, :last_name, :email, :mobile, :phone)

insert with ->execute()

$data = [
    'id' => 12,
    'first_name' => 'Joe3',
    'last_name'  => 'Doe3',
    'email'      => '[e-mail]',
    'phone'      => '+0172222222'
];

$query = (new Query($this->pdo))
    ->insert(
        'users',
        [
            'id' => ':id',
            'first_name' => ':first_name',
            'last_name'  => ':last_name',
            'email'      => ':email',
            'phone'      => ':phone'
        ]
    )->params($data)
    ->execute();

update

$data = [
    'first_name' => 'Joe3',
    'last_name'  => 'Doe3',
    'email'      => '[e-mail]'
];
$query = (new Query($this->pdo))
        ->update(
            'users',
            [
                'first_name' => ':first_name',
                'last_name'  => ':last_name',
                'email'      => ':email'
            ],
            2
        )
        ->params($data);
            
$query1 = (new Query($this->pdo))
            ->update('users')
            ->set([
                'first_name' => ':first_name',
                'last_name'  => ':last_name',
                'email'      => ':email'
            ])
            ->where('id = :id')
            ->params($data);

build the query below

UPDATE users SET first_name = :first_name, last_name = :last_name, email = :email WHERE (id = :id)

update with ->execute()

$data = [
    'first_name' => 'Joe',
    'last_name'  => 'Doe',
    'email'      => '[e-mail]',
    'phone'      => '+0172222222'
];
$query = (new Query($this->pdo))
    ->update(
        'users',
        [
            'first_name' => ':first_name',
            'last_name'  => ':last_name',
            'email'      => ':email',
            'phone'      => ':phone'
        ],
        4
    )
    ->params($data)
    ->execute();

delete

$query = (new Query($this->pdo))->delete('users', 2);
            
$query1 = (new Query($this->pdo))->delete('users')->where('id = :id')->params(['id' => 12]);

build the query below

DELETE FROM users WHERE (id = :id)

delete with ->execute()

(new Query($this->pdo))->delete('users', 2)->execute();

where

$query = (new Query())
            ->from('users', 'u')
            ->where('first_name = :first_name OR email = :email', 'phone = :phone');
            
$query2 = (new Query())
            ->from('users', 'u')
            ->where('first_name = :first_name OR email = :email')
            ->where('mobile = :mobile');

build the query below

SELECT * FROM users as u WHERE (first_name = :first_name OR email = :email) AND (phone = :phone)

SELECT * FROM users as u WHERE (first_name = :first_name OR email = :email) AND (mobile = :mobile)

params

$user = (new Query($this->pdo))
            ->from('users', 'u')
            ->where('id = :id')
            ->params(['id' => 1]);

count

$usersCount = (new Query($this->pdo))
            ->from('users', 'u')
            ->where('u.id < :number')
            ->params([
                'number' => 5
            ])
            ->count();

orderBy

$query = (new Query($this->pdo))
            ->from('users', 'u')
            ->orderBy('u.id', 'DESC');

build the query below

SELECT * FROM users as u ORDER BY u.id DESC

groupBy

$query = (new Query())
            ->select('u.first_name', 'COUNT(id)')
            ->from('users', 'u')
            ->groupBy('u.updated_at');

build the query below

SELECT u.first_name, COUNT(id) FROM users as u GROUP BY u.updated_at

joins

 $query = (new Query())
            ->from('users', 'u')
            ->select('first_name')
            ->join('posts as p', 'u.id = p.user_id');
            
 $query = (new Query())
            ->from('users', 'u')
            ->select('first_name')
            ->join('posts as p2', 'u.id = p2.user_id', 'inner');

build the query below

SELECT first_name FROM users as u LEFT JOIN posts as p ON u.id = p.user_id

SELECT first_name FROM users as u INNER JOIN posts as p2 ON u.id = p2.user_id

limit

$query = (new Query($this->pdo))
            ->from('users', 'u')
            ->where('first_name = :first_name OR email = :email')
            ->limit(5);

build the query below

SELECT * FROM users as u WHERE (first_name = :first_name OR email = :email) LIMIT 0, 5

fetchAll

$query = (new Query($this->pdo))->from('users')->select('first_name')->fetchAll();

fetch

$user = (new Query($this->pdo))
            ->from('users', 'u')
            ->where('id = :id')
            ->params(['id' => 1])
            ->fetch();

fetchOrFail

$user = (new Query($this->pdo))
            ->from('users', 'u')
            ->where('id = :id')
            ->params(['id' => 100])
            ->fetchOrFail();

execute

$query = (new Query($this->pdo))
            ->insert(
                'users',
                [
                    'first_name' => ':first_name',
                    'last_name'  => ':last_name',
                    'email'      => ':email',
                    'mobile'     => ':mobile',
                    'phone'      => ':phone',
                ]
            )
            ->execute();

paginate

    $paginate = (new Query($this->pdo))
        ->from('users', 'u')
        ->into(Demo::class)
        ->paginate(5, 1);
            
    $paginate->getNbPages();
    $paginate->haveToPaginate();
    $paginate->hasPreviousPage();
    $paginate->getPreviousPage();
    $paginate->hasNextPage();
    $paginate->getNextPage();
    $paginate->getCurrentPageOffsetStart();
    $paginate->getCurrentPageOffsetEnd();
    $paginate->getIterator(); // return collections of objects

Hydration

use Goldoni\Builder\Entities\Demo;
...
$demos = (new Query($this->pdo))
            ->from('users', 'u')
            ->into(Demo::class)
            ->fetchAll();

$demo1 = $demos[0]; 

// get_class($demo1) === Demo::class  

echo $demo1->firstName; 

$demo2 = (new Query($this->pdo))
            ->from('users', 'u')
            ->where('id = :id')
            ->into(Demo::class)
            ->params(['id' => 2])
            ->fetch();
            
// get_class($demo2) === Demo::class      

echo $demo2->firstName;

var_dump($demo1 instanceof Demo::class); // TRUE
var_dump($demo2 instanceof Demo::class); // TRUE

Unit Test

./vendor/bin/phpunit

Versioning

We use 1.3.0

Authors

• Goldoni Fouotsa - Initial work

License

This project is licensed under the MIT License