README

Connection with use QueryBuilder

use R1KO\QueryBuilder\ConnectionWithBuilderFactory;

$params = [
    'driver'    => 'mysql',
    'host'      => 'localhost',
    'port'      => '3306',
    'database'  => 'database',
    'username'  => 'root',
    'password'  => 'password',
    'charset'   => 'utf8mb4',
];

$db = ConnectionWithBuilderFactory::create($params);

QueryBuilder

Insert

public function insert(array $values): int;

Insert data into a table and returns the ID of the added row

$values = [
    'name'    => 'test',
    'email'   => 'test',
    'address' => 'test',
];
$id = $db->table('users')
    ->insert($values);

Batch Insert

public function insertBatch(array $values): int;

Insert a lot of data into a table and returns the number of row added

Example SQL:

INSERT INTO (col1, col2, ...colN) VALUES (val1, val2, ...valN), (val1, val2, ...valN), ...;

$values = [
    [
        'name'    => 'test',
        'email'   => 'test',
        'address' => 'test',
    ],
    [
        'name'    => 'test 2',
        'email'   => 'test 2',
        'address' => 'test 2',
    ],
];
$count = $db->table('users')
    ->insertBatch($values);

Mass Insert

public function insertMass(array $values, bool $useTransaction = false): array;

Insert a set of data into a table as a prepared query and returns an array of added row IDs

Example SQL:

INSERT INTO (col1, col2, ...colN) VALUES (?, ?, ...);

$values = [
    [
        'name'    => 'test',
        'email'   => 'test',
        'address' => 'test',
    ],
    [
        'name'    => 'test 2',
        'email'   => 'test 2',
        'address' => 'test 2',
    ],
];
$ids = $db->table('users')
    ->insertMass($values);

Iterable Insert

public function insertIterable(array $schema, iterable $iterator, bool $useTransaction = false): iterable;

$schema = [
    'name',
    'email',
    'address',
];

$iterator = function (): iterable {
    // ...
    yield [
        'name'    => 'test 1',
        'email'   => 'test 2',
        'address' => 'test 3',
    ];
    // OR
    yield [
        'test 1',
        'test 2',
        'test 3',
    ];
};

$idsIterator = $db->table('users')
    ->insertIterable($schema, $iterator);

Delete

Delete rows conditionally and returns the number of rows deleted

$count = $db->table('users')
    ->where('status', 'outdated')
    ->delete();

Update

Update rows conditionally and returns the number of rows modified

$count = $db->table('users')
    ->where('status', 'outdated')
    ->update(['status' => 'deleted']);

Select

`getAll`

Gets an associative array of rows

$id = $db->table('users')
    ->select(['id', 'status'])
    ->getAll();

[
    [
        'id' => 1,
        'status' => 'active',
    ],
    [
        'id' => 2,
        'status' => 'deleted',
    ],
]

`getAssoc`

Gets an associative array of row whose keys are the specified column If column is null - assoc by first column of select

$id = $db->table('users')
    ->select(['id', 'status'])
    ->getAssoc('id');

[
    4 => [
        'id' => 4,
        'status' => 'active',
    ],
    3 => [
        'id' => 3,
        'status' => 'deleted',
    ],
]

`getIterable`

Get rows from the result one by one

$users = $db->table('users')
    ->select(['id', 'status'])
    ->getIterable();

foreach ($users as $user) {
    
}

[
    'id' => 4,
    'status' => 'active',
]

[
    'id' => 3,
    'status' => 'deleted',
]

Get rows from the result one by one with associated key

$users = $db->table('users')
    ->select(['id', 'status'])
    ->getIterable('id');

foreach ($users as $id => $user) {
    
}


4 => [
    'id' => 4,
    'status' => 'active',
]

3 => [
    'id' => 3,
    'status' => 'deleted',
]

`getRow`

Gets an associative array of one row

$id = $db->table('users')
    ->select(['id', 'status'])
    ->getRow();

[
    'id' => 4,
    'status' => 'active',
]

`getCol`

Gets an array of rows of values of one column

$emails = $db->table('users')
    ->getCol('email');

[
    'test.user1@gmail.com',
    'ivan.ivanov@gmail.com',
    'petro.petrov@gmail.com',
]

`getColIterable`

$emails = $db->table('users')
    ->getColIterable('email');

foreach ($emails as $email) {
    
}


'test.user1@gmail.com'

'ivan.ivanov@gmail.com'

'petro.petrov@gmail.com'

`getOne`

Gets one value

$id = $db->table('users')
    ->select($db->raw('COUNT(amount)'))
    ->getOne();

Aliases

$id = $db->table('users')
    ->select([
        'id' => 'user_id', 
        'status', 
        $db->raw('IF(deleted_at IS NULL, 1, 0)') => 'is_active'
    ])
    ->getAll();

[
    [
        'user_id' => 1,
        'status' => 'active',
        'is_active' => 1,
    ],
    [
        'user_id' => 2,
        'status' => 'inactive',
        'is_active' => 0,
   ],
]

`DISTINCT`

$id = $db->table('users')
    ->select(['id', 'status'])
    ->distinct()
    ->getAll();

Raw Column Expressions

$columns = [
    'id',
    $db->raw('address AS user_address'),
    // or
    QueryBuilder::asRaw('address AS user_address'),
    // TODO: subquery
];
$results = $db->table('users')
    ->select($columns)
    ->getAll();

Conditions

TODO ...

public function where(string $column, array|string|int|bool $value);
public function where(string $column, string $operator, array|string|int|bool $value);
public function where(string $column, Closure $value);
// WHERE column operator (condition|subquery)

public function where(Closure $condition); // WHERE (condition)

public function where(Raw $expression);

public function where(array $conditions); // WHERE [condition 1] AND [condition 2] AND [condition N] ... public function whereAnd(array $conditions); // WHERE [condition 1] AND [condition 2] AND [condition N] ... public function whereOr(array $conditions); // WHERE [condition 1] OR [condition 2] OR [condition N] ...

// TODO public function whereColumn public function whereExists

upsert increment decrement