README

Enables PHP developers to build SQL queries similar to native language syntax.

Requirements

PHP >= 8.1
PDO PHP Extension

Installation

composer require dilovanmatini/query-builder

Usage

Using PDO Instance:

<?php

use Database\QueryBuilder\QB;

require_once __DIR__ . '/vendor/autoload.php';

QB::config([
    'connection' => $conn // PDO instance
]);

$user = QB::select('id, name, email')->from('users')->where('id', 1)->and('status', 1)->fetch();

echo $user->name;

In Laravel:

<?php

use Database\QueryBuilder\QB;

$user = QB::select('id, name, email')->from(User::class)->where('id', 1)->and('status', 1)->fetch();

echo $user->name;

In Laravel, the connection detects automatically. You don't need to set it.

Using Database Credentials:

<?php

use Database\QueryBuilder\QB;

require_once __DIR__ . '/vendor/autoload.php';

QB::config([
    'host' => 'localhost',
    'database' => 'test',
    'username' => 'root',
    'password' => '',
]);

$user = QB::select('id, name, email')->from('users')->where('id', 1)->and('status', 1)->fetch();

echo $user->name;

Configuration

If you set a valid connection you don't need to set host, port, database, username, password, and charset options.

Documentation

Select
- Select
- From
- Joins
- Where
- Group By
- Order By
- Having
- Limit
- Offset
- Fetch, Fetch ALL, and Statement
- Helpers
  - Raw
  - Param
  - Now
Insert
- Columns
- Values
Update
- Set
- Where
Delete
- Where

Select

The select() method is used to add SELECT clause to the query to specify which columns you would like to retrieve from the database. The select() method accepts list of arguments as columns, so you can pass the columns as a comma-separated list or as an array.

To retrieve all columns from a table, you may use the select() method without passing any arguments.

$user = QB::select()->from('users');

SELECT * FROM users

Note: The second code is SQL code that will be generated by the first code.

To retrieve a single column, pass the name of the column as the first argument to the select() method.

$name = QB::select('name')->from('users');

SELECT name FROM users

To retrieve multiple columns, pass the names of the columns as an array to the select() method.

$user = QB::select(['id', 'name', 'email'])->from('users');

SELECT id, name, email FROM users

Or you can pass the columns as a comma-separated list to the select() method.

$user = QB::select('id, name, email')->from('users');

SELECT id, name, email FROM users

Multiple usage of select() in one query:

$user = QB::select([
        'u.id'
        QB::alias('u', [
            'name',
            'email',
        ]),
        QB::if('u.status = 1', 'active', 'inactive')->as('status'),
        QB::count('p.*')->as('count'),
    ], 'u.join_date')
    ->from('users AS u')
    ->leftJoin('posts')->as('p')->on('p.user_id', 'u.id');

SELECT
    u.id,
    u.name,
    u.email,
    IF(u.status = 1, 'active', 'inactive') AS status,
    COUNT(p.*) AS count,
    u.join_date
FROM users AS u
LEFT JOIN posts AS p ON p.user_id = u.id

select() has some helpers to make it easier to write queries:

Alias

The alias() method is used to add alias for the columns. The alias() method accepts two arguments. The first argument is alias name and the second argument is the list of columns.

$user = QB::select([
        QB::alias('u', [
            'name',
            'email',
        ]),
    ])
    ->from('users AS u');

SELECT u.name, u.email FROM users AS u

Count

The count() method is used to add COUNT() function to the query. The count() method accepts two arguments. The first argument is the column name and the second argument is optional and is used to specify the alias for the column.

You can also use as() method instead of passing the alias as the second argument.

$user = QB::select([
        QB::count('p.*')->as('count'),
    ])
    ->from('users AS u')
    ->leftJoin('posts')->as('p')->on('p.user_id', 'u.id');

SELECT COUNT(p.*) AS count FROM users AS u LEFT JOIN posts AS p ON p.user_id = u.id

The same way for the sum min max avg methods.

From

The from() method is used to add FROM clause to the query to specify the table from which you would like to retrieve data. The from() method accepts a string variable or a model class name as its first argument. The second argument is optional and is used to specify the alias for the table.

$user = QB::select()->from('users');

SELECT * FROM users

Or using Model class name For developers who use MVC framework like Laravel

$user = QB::select()->from(User::class);

SELECT * FROM users

You can also specify the alias for the table as the second argument to the from() method.

$user = QB::select()->from('users', 'u');

SELECT * FROM users AS u

Or using the as() method

$user = QB::select()->from('users')->as('u');

SELECT * FROM users AS u

Joins

The joins methods are used to join tables in a query.

The leftJoin() rightJoin() crossJoin() innerJoin fullJoin() methods accept the table name as the first argument and the alias for the table as the second argument. You can use as() method instead of passing the alias as the second argument.

If you don't provide alias for the tables, the table name will be used as the alias when you have more than one table in the query.

$user = QB::select()
        ->from('users')->as('u')
        ->leftJoin('posts')->as('p');

SELECT u.* FROM users AS u LEFT JOIN posts AS p

You can also use the on() method to specify the join condition.

$user = QB::select()
        ->from('users')->as('u')
        ->leftJoin('posts')->as('p')->on('u.id', 'p.user_id');

SELECT u.* FROM users AS u LEFT JOIN posts AS p ON u.id = p.user_id

Using more than one joins in a query.

$user = QB::select()
        ->from('users')->as('u')
        ->leftJoin('posts')->as('p')->on('u.id', 'p.user_id')
        ->leftJoin('comments')->as('c')->on('c.post_id', 'p.id');

SELECT u.*
FROM users AS u
LEFT JOIN posts AS p ON u.id = p.user_id
LEFT JOIN comments AS c ON c.post_id = p.id

Where

The where() method is used to add a WHERE clause to the query. The where() method accepts three arguments. The first argument is required and others are optional.

If you pass only one argument to the where() method, it will be considered as the full condition consists of column name, operator, and value.

If you pass two arguments to the where() method, the first argument will be considered as the column name and the second argument will be considered as the value. The = operator will be used as the default operator.

If you pass three arguments to the where() method, the first argument will be considered as the column name, the second argument will be considered as the operator, and the third argument will be considered as the value.

Example using only one argument:

$user = QB::select()->from('users')->where('id = 1');

SELECT * FROM users WHERE id = 1

Example using two arguments:

$user = QB::select()->from('users')->where('id', 1);

SELECT * FROM users WHERE id = 1

Example using three arguments:

$user = QB::select()->from('users')->where('status', '!=', 0);

SELECT * FROM users WHERE status != 0

Note: if you want to pass the RAW value as the second and third arguments, you should use the QB::raw() method.

You can also use the `and()` and `or()` methods to add more conditions to the `WHERE` clause.

Using the and() method:

$user = QB::select()
        ->from('users')
        ->where('id', 1)
        ->and('status', 1);

SELECT * FROM users WHERE id = 1 AND status = 1

Using the or() method:

$user = QB::select()
        ->from('users')
        ->where('skill', 'php')
        ->or('skill', 'javascript');

SELECT * FROM users WHERE skill = 'php' OR skill = 'javascript'

Using the and() and or() methods together:

$users = QB::select()
        ->from('users')
        ->where('hobie', 'tines')
        ->or('hobie', 'coding')
        ->and('skill', 'php');

SELECT * FROM users WHERE (hobie = 'tines' OR hobie = 'coding') AND skill = 'php'

You can use group conditions using the where() and() or() on() having methods. Especially when you use the and() and or() methods together.

$user = QB::select()
        ->from('users')
        ->where('id', 1)
        ->and('status', 1)
        ->and(
            QB::where('skill', 'php')->or('skill', 'javascript')
        );

SELECT * FROM users WHERE id = 1 AND status = 1 AND (skill = 'php' OR skill = 'javascript')

Where helpers

The where() method also accepts the group of method helpers to provide more flexibility to the query.

List of where helpers:

QB::equal( $value )
QB::notEqual( $value )
QB::lessThan( $value )
QB::lessThanOrEqual( $value )
QB::greaterThan( $value )
QB::greaterThanOrEqual( $value )
QB::like( $value )
QB::notLike( $value )
QB::between( $value1, $value2 )
QB::notBetween( $value1, $value2 )
QB::in( $values )
QB::notIn( $values )
QB::isNull()
QB::isNotNull()
QB::isEmpty()
QB::isNotEmpty()

All the above helpers can be used as the second or third argument of the where() and() or() on() having methods.

Some examples:

$user = QB::select()
        ->from('users')
        ->where('id', QB::equal(1))
        ->and('status', QB::notEqual(0))
        ->and('skills', QB::in(['php', 'javascript']));

SELECT * FROM users WHERE id = 1 AND status != 0 AND skills IN ('php', 'javascript')

$users = QB::select()
        ->from('users')
        ->where('id', QB::between(1, 10))
        ->and('status', QB::notBetween(0, 5));

SELECT * FROM users WHERE id BETWEEN 1 AND 10 AND status NOT BETWEEN 0 AND 5

$users = QB::select()
        ->from('users')
        ->where('name', QB::like('%john%'))
        ->and('skill', QB::notLike('%script'))
        ->and('birthday', QB::isNotNull())
        ->and('hobie', QB::isNotEmpty());

SELECT *
FROM users
WHERE
    name LIKE '%john%' AND
    skill NOT LIKE '%script' AND
    birthday IS NOT NULL AND
    hobie IS NOT NULL

Note: like() and notLike() don't make the value as placeholder, so you can pass the value directly. But if you want to make the value as placeholder, you can use the QB::param() method for the value.

Group by

The groupBy() method is used to add a GROUP BY clause to the query. The groupBy() method accepts list of columns as arguments.

$users = QB::select()
        ->from('users')
        ->groupBy('skill');

SELECT * FROM users GROUP BY skill

$users = QB::select()
        ->from('users')
        ->groupBy('skill', 'hobie');

SELECT * FROM users GROUP BY skill, hobie

Order by

The orderBy() method is used to add a ORDER BY clause to the query. The orderBy() method accepts multiple arguments.

If you don't pass ASC or DESC, the ASC will be used as the default order.

$users = QB::select()
        ->from('users')
        ->orderBy('name');

SELECT * FROM users ORDER BY name ASC

$users = QB::select()
        ->from('users')
        ->orderBy('name ASC', 'join_date DESC');

SELECT * FROM users ORDER BY name ASC, join_date DESC

Having

The having() method is used to add a HAVING clause to the query. The having() method is similar to the where() method.

$users = QB::select()
        ->from('users')
        ->groupBy('skill')
        ->having('COUNT(id)', '>', 10);

SELECT * FROM users GROUP BY skill HAVING COUNT(id) > 10

$users = QB::select()
        ->from('users')
        ->groupBy('skill')
        ->having('COUNT(id)', '>', 10)
        ->and('COUNT(id)', '<', 20);

SELECT * FROM users GROUP BY skill HAVING COUNT(id) > 10 AND COUNT(id) < 20

Limit

The limit() method is used to add a LIMIT clause to the query. The limit() method accepts one argument.

$users = QB::select()
        ->from('users')
        ->limit(10);

SELECT * FROM users LIMIT 10

You can also pass the offset as the second argument.

$users = QB::select()
        ->from('users')
        ->limit(10, 100);

SELECT * FROM users LIMIT 10, 100

Offset

The offset() method is used to add a OFFSET clause to the query. The offset() method accepts one argument.

The offset() method must be used with the limit() method.

$users = QB::select()
        ->from('users')
        ->limit(10)
        ->offset(100);

SELECT * FROM users LIMIT 10, 100

Fetch, FetchAll, and Statement

The fetch() method is used to fetch data from the database. The fetch() method accepts one argument as the fetch mode.

The default fetch mode is PDO::FETCH_OBJ. You can pass any fetch mode from the PDO class.

To fetch data as an object as stdClass instance:

$user = QB::select()
        ->from('users')
        ->where('id', 1)
        ->fetch();

echo $user->name; // John Doe

You can use fetch when you want to fetch only one row.

To fetch data as an associative array:

$user = QB::select()
        ->from('users')
        ->where('id', 1)
        ->fetch(\PDO::FETCH_ASSOC);

echo $user['name']; // John Doe

The fetchAll() method is used to fetch all data from the database. The fetchAll() method accepts one argument as the fetch mode.

$users = QB::select()
        ->from('users')
        ->fetchAll();

foreach ($users as $user) {
    $user->name; // John Doe
}

You can use fetchAll when you want to fetch all rows.

The statement() method is used to get the PDOStatement object.

$stmt = QB::select()
        ->from('users')
        ->statement();

while ($user = $stmt->fetch()) {
    echo $user->name; // John Doe
}

You can use statement when you want to use the PDOStatement methods.

Raw

The raw() method is used to return the raw query string. The raw() method accepts one argument to indicate whether you want the query as a string or an stdClass object including parameters used as placeholders.

$query = QB::select()
        ->from('users')
        ->where('id', 1)
        ->raw();

SELECT * FROM users WHERE id = 1

Helpers

Raw

The raw() method is used to add a raw string to the query. The raw() method accepts one argument.

$users = QB::select()
        ->from('users')
        ->where('id', QB::raw('COUNT(id)'))

SELECT * FROM users WHERE id = COUNT(id)

Param

The param() method is used to add the value as placeholder to the query. The param() method accepts two arguments. The first one is required as the value and the second one is optional as the name of the placeholder.

$users = QB::select()
        ->from('users')
        ->leftJoin('skills' 's')->on('s.user_id', 'u.id')->and('s.name', QB::param('php', 'skill'))
        ->where('id', 1);

SELECT * FROM users LEFT JOIN skills s ON s.user_id = u.id AND s.name = :skill WHERE id = 1

You don't need to use the param() method for the where() clause. The where() automatically adds the value as placeholder.

Now

The now() method is used to add the current date and time to the query.

$users = QB::select()
        ->from('users')
        ->where('created_at', QB::now());

SELECT * FROM users WHERE created_at = NOW()

Some examples from simple to complex

$users = QB::select()
        ->from('users')
        ->where('id', 1)
        ->and('status', 1)
        ->and(
            QB::where('skill', 'php')->or('skill', 'javascript')
        )
        ->groupBy('skill')
        ->having('COUNT(id)', '>', 10)

SELECT *
FROM users
WHERE
    id = 1 AND
    status = 1 AND
    (skill = 'php' OR skill = 'javascript')
GROUP BY skill
HAVING COUNT(id) > 10

$users = QB::select()
        ->from('users')
        ->where('id', 1)
        ->and('status', 1)
        ->and(
            QB::where('skill', 'php')->or('skill', 'javascript')
        )
        ->groupBy('skill')
        ->orderBy('name ASC', 'join_date DESC')
        ->having('COUNT(id)', '>', 10)
        ->limit(10)
        ->offset(100);

SELECT *
FROM users
WHERE
    id = 1 AND
    status = 1 AND
    (skill = 'php' OR skill = 'javascript')
GROUP BY skill
HAVING COUNT(id) > 10
ORDER BY name ASC, join_date DESC
LIMIT 10, 100

$users = QB::select(
        'u.id, u.name, u.email, s.name as skill',
        QB::if('u.status = 1', 'active', 'inactive')->as('status'),
        QB::select('COUNT(id)')->from('skills')->where('user_id', QB::raw('u.id'))->as('skill_count')
    )
    ->from('users')->as('u')
    ->leftJoin('posts', 'p')->on('p.user_id', 'u.id')
    ->leftJoin('comments')->as('c')->on('c.post_id', 'p.id')
    ->where('u.status', 1)
    ->and(
        QB::where('u.skill', 'php')->or('u.skill', 'javascript')
    )
    ->and('u.join_date', QB::greaterThan('2019-01-01'))
    ->and('u.role',
        QB::if(
            QB::where('u.role', QB::in('admin', 'super_admin')),
            'admin',
            'user'
        )
    )
    ->groupBy('u.skill')
    ->having('COUNT(p.id)', '>', 10)
    ->orderBy('u.name ASC', 'u.join_date DESC')
    ->limit(10)
    ->having('COUNT(c.id)', '<', 100)
    ->offset(100);

SELECT
    u.id, u.name, u.email, s.name as skill,
    IF(u.status = 1, 'active', 'inactive') as status,
    (SELECT COUNT(id) FROM skills WHERE user_id = u.id) as skill_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
LEFT JOIN comments c ON c.post_id = p.id
WHERE
    u.status = 1 AND
    (u.skill = 'php' OR u.skill = 'javascript') AND
    u.join_date > '2019-01-01' AND
    u.role = IF(u.role IN ('admin', 'super_admin'), 'admin', 'user')
GROUP BY u.skill
HAVING COUNT(p.id) > 10 AND COUNT(c.id) < 100
ORDER BY u.name ASC, u.join_date DESC
LIMIT 10, 100

Insert

The QB::insert() method is used to create an INSERT query. The insert() method accepts one argument as the table name.

QB::insert('users')->values([
    'name' => 'John Doe'
]);

INSERT INTO users (name) VALUES ('John Doe')

You can also use insertInto() as well. It is an alias of insert() method.

You can use Model classes instead of table names.

Columns

The columns() method is used to add COLUMNS to the INSERT query. The columns() method is optional and accepts the below arguments:

A string as RAW SQL.
An array as column names.

If you pass a string to the columns(), the values() method must be string too.

If you pass an array to the values() method, you don't need to use the columns() method.

QB::insert('users')->columns('name')->values('John Doe');

INSERT INTO users (name) VALUES ('John Doe')

Values

The values() method is used to add VALUES to the INSERT query. The values() method accepts the below arguments:

A string as RAW SQL.
An array as column names and values.

When you pass an array to the values() method, the keys of the array are the column names and the values of the array are the values of the columns.

QB::insert('users')->values([
    'name' => 'John Doe'
]);

INSERT INTO users (name) VALUES ('John Doe')

The values of the array will be placeholders automatically.

To execute the query, you can use the run() or execute() methods.

QB::insert('users')->values([
    'name' => 'John Doe'
])->run();

Update

The QB::update() method is used to create an UPDATE query. The update() method accepts two arguments as the table name and the alias of the table.

You can also use as() method to set the alias of the table.

QB::update('users')->set([
    'name' => 'John Doe'
])->where('id', 1);

UPDATE users SET name = 'John Doe' WHERE id = 1

You can use Model classes instead of table names.

Set

The set() method is used to add SET to the UPDATE query. The set() method accepts the below arguments:

A string as RAW SQL.
An array as column names and values.

When you pass an array to the set() method, the keys of the array are the column names and the values of the array are the values of the columns.

QB::update('users')->set([
    'name' => 'John Doe'
])->where('id', 1);

UPDATE users SET name = 'John Doe' WHERE id = 1

The values of the array will be placeholders automatically.

To use where() method with update() method, please see the Where section.

Note: you cannot use update() method without using where() method.

To execute the query, you can use the run() or execute() methods.

QB::update('users')->set([
    'name' => 'John Doe'
])->where('id', 1)->run();

UPDATE users SET name = 'John Doe' WHERE id = 1

Delete

The QB::delete() method is used to create a DELETE query. The delete() method accepts two arguments as the table name and the alias of the table.

You can also use as() method to set the alias of the table.

QB::delete('users')->where('id', 1);

DELETE FROM users WHERE id = 1

You can also use deleteFrom() as well. It is an alias of delete() method.

You can use Model classes instead of table names.

To use where() method with delete() method, please see the Where section.

Note: you cannot use delete() method without using where() method.

To execute the query, you can use the run() or execute() methods.

QB::delete('users')->where('id', 1)->run();

DELETE FROM users WHERE id = 1

You can get SQL query as the string by ending the query with raw() method in SELECT, INSERT, UPDATE and DELETE queries.

License

This project is open-sourced software licensed under the MIT License - see the LICENSE file for details.

dilovanmatini / query-builder

Maintainers

Details

README

Requirements

Installation

Usage

Configuration

Documentation

Select

Alias

Count

From

Joins

Where

Where helpers

Group by

Order by

Having

Limit

Offset

Fetch, FetchAll, and Statement

Raw

Helpers

Raw

Param

Now

Some examples from simple to complex

Insert

Columns

Values

Update

Set

Delete

License