PDOx: Эффективный конструктор запросов для PHP PDO
PDOx - это быстрый, эффективный и полезный конструктор запросов и класс PDO для PHP. Является отличной альтернативой для RedBeanPHP.
Репозиторий на GitHub по ссылке. Там же можно найти документацию.
Установка
В файл composer.json
добавить:
{ "require": { "izniburak/pdox": "^1" } }
Затем выполнить команду:
composer install
Или установить командой:
composer require izniburak/pdox
Настройка
Необходимо в настройках установить соединение с базой данных:
require 'vendor/autoload.php'; $config = [ 'host' => 'localhost', 'driver' => 'mysql', 'database' => 'pdox', 'username' => 'root', 'password' => 'root', 'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'cachedir' => __DIR__ . 'cache/sql/' # указать свой путь(директории cache/sql необходимо создать заранее) ]; $db = new \Buki\Pdox($config);
select
# Вариант #1: Параметры в строку через запятую $db->select('name, status, image')->table('product')->getAll(); # Output: "SELECT name, status, image FROM product" $db->select('name AS n, status AS s, image AS i')->table('product')->getAll(); # Output: "SELECT name AS n, status AS s, image AS i FROM product" # Вариант #2: Параметры массивом $db->select(['name', 'status', 'image'])->table('product')->getAll(); # Output: "SELECT name, status, image FROM product" $db->select(['name AS n', 'status AS s'])->table('product')->getAll(); # Output: "SELECT name AS n, status AS s FROM product"
select functions
# Вариант #1: $db->table('price')->max('tarif')->get(); # Output: "SELECT MAX(tarif) FROM price" # Вариант #2: $db->table('product')->count('id', 'total_count')->get(); # Output: "SELECT COUNT(id) AS total_row FROM product"
get AND getAll
# get(): Одна запись. # getAll(): Множество записей. $db->table('product')->getAll(); # Output: "SELECT * FROM product" $db->select('name')->table('product')->where('category_id', 1)->getAll(); # Output: "SELECT name FROM product WHERE category_id='1'" $db->select('name, created_at')->table('product')->where('id', 10)->get(); # Output: "SELECT title FROM pages WHERE id='17' LIMIT 1"
where
$where = [ 'name' => 'Adam Smith', 'age' => 22, 'email' => 'email@adam.com', ]; $db->table('customer')->where($where)->get(); # Output: "SELECT * FROM customer WHERE name='Adam Smith' AND age='22' AND email='email@adam.com' LIMIT 1" $db->table('customer')->where('age', 30)->getAll(); # Output: "SELECT * FROM customer WHERE age='30'" $db->table('customer')->where('age', '>=', 30)->getAll(); # Output: "SELECT * FROM customer WHERE age>='30'" $db->table('customer')->where('age = ? OR age = ?', [25, 35])->getAll(); # Output: "SELECT * FROM customer WHERE age='25' OR age='35'" $db->table('product')->where('status', '=', 1)->notWhere('category_id', 1)->getAll(); # Output: "SELECT * FROM product WHERE status = '1' AND NOT category_id = '1'" $db->table('product')->where('category_id', 1)->orWhere('category_id', '>', 5)->getAll(); # Output: "SELECT * FROM product WHERE category_id = '1' OR category_id > '5'" $db->table('product')->whereNotNull('image')->getAll(); # Output: "SELECT * FROM product WHERE image IS NOT NULL"
grouped
$db->table('product') ->grouped(function($query) { $query->where('image', null)->orWhere('category_id', 2); }) ->where('status', 1) ->getAll(); # Output: "SELECT * FROM product WHERE (image = NULL OR category_id ='2') AND status ='1'"
in
$db->table('product')->where('status', 1)->in('id', [1, 5, 12])->getAll(); # Output: "SELECT * FROM product WHERE status = '1' AND id IN ('1', '5', '12')" $db->table('product')->where('status', 1)->notIn('id', [1, 2, 3])->getAll(); # Output: "SELECT * FROM product WHERE status = '1' AND id NOT IN ('1', '2', '3')" $db->table('product')->where('status', 1)->orIn('id', [1, 2, 3])->getAll(); # Output: "SELECT * FROM product WHERE status = '1' OR id IN ('1', '2', '3')"
between
$db->table('customer')->where('status', 1)->between('age', 18, 25)->getAll(); # Output: "SELECT * FROM customer WHERE status = '1' AND age BETWEEN '18' AND '25'" $db->table('customer')->where('status', 1)->notBetween('age', 18, 25)->getAll(); # Output: "SELECT * FROM customer WHERE status = '1' AND age NOT BETWEEN '18' AND '25'" $db->table('customer')->where('status', 1)->orBetween('age', 18, 25)->getAll(); # Output: "SELECT * FROM customer WHERE status = '1' OR age BETWEEN '18' AND '25'"
like
$db->table('customer')->like('name', "%Vir%")->getAll(); # Output: "SELECT * FROM customer WHERE name LIKE '%Vir%'" $db->table('customer')->where('status', 1)->notLike('name', '%Vir%')->getAll(); # Output: "SELECT * FROM customer WHERE status = '1' AND name NOT LIKE '%Vir%'" $db->table('customer')->like('name', '%Vir%')->orLike('name', '%Jen%')->getAll(); # Output: "SELECT * FROM customer WHERE name LIKE '%Vir%' OR name LIKE '%Jen%'"
groupBy
# Пример #1: Один параметр $db->table('product')->where('status', 1)->groupBy('manufacturer_id')->getAll(); # Output: "SELECT * FROM product WHERE status = '1' GROUP BY category_id" # Пример #2: Массив параметров $db->table('product')->where('status', 1)->groupBy(['name', 'manufacturer_id'])->getAll(); # Output: "SELECT * FROM test WHERE status = '1' GROUP BY cat_id, user_id"
having
$db->table('customer')->where('status', 1)->groupBy('name')->having('MAX(age)', 20)->getAll(); # Output: "SELECT * FROM customer WHERE status='1' GROUP BY name HAVING MAX(age) > '20'" $db->table('customer')->where('status', 1)->groupBy('name')->having('AVG(age)', '<=', 30)->getAll(); # Output: "SELECT * FROM customer WHERE status='1' GROUP BY name HAVING AVG(age) <= '30'" $db->table('customer')->where('status', 1)->groupBy('name')->having('AVG(age) > ? AND MAX(age) < ?', [20, 30])->getAll(); # Output: "SELECT * FROM test WHERE active='1' GROUP BY department_id HAVING AVG(salary) > '250' AND MAX(salary) < '1000'"
orderBy
# Пример #1: Один параметр $db->table('product')->where('status', 1)->orderBy('category_id')->getAll(); # Output: "SELECT * FROM product WHERE status='1' ORDER BY category_id ASC" $db->table('product')->where('status', 1)->orderBy('category_id desc')->getAll(); # Output: "SELECT * FROM product WHERE status='1' ORDER BY category_id DESC" // Пример #2: Два параметра $db->table('product')->where('status', 1)->orderBy('category_id', 'desc')->getAll(); # Output: "SELECT * FROM product WHERE status='1' ORDER BY category_id DESC" # Пример #3: Rand() $db->table('product')->where('status', 1)->orderBy('rand()')->limit(10)->getAll(); # Output: "SELECT * FROM product WHERE status='1' ORDER BY rand() LIMIT 10"
limit-offset
# Пример #1: Один параметр $db->table('product')->limit(10)->getAll(); # Output: "SELECT * FROM product LIMIT 10" # Пример #2: Два параметра $db->table('product')->limit(10, 20)->getAll(); # Output: "SELECT * FROM product LIMIT 10, 20" # Пример #3: С методом offset() $db->table('product')->limit(10)->offset(10)->getAll(); # Output: "SELECT * FROM product LIMIT 10 OFFSET 10"
pagination
# Первый параметр: количество данных на странице # Второй параметр: Активная страница $db->table('product')->pagination(15, 1)->getAll(); # Output: "SELECT * FROM product LIMIT 15 OFFSET 0" $db->table('product')->pagination(15, 2)->getAll(); # Output: "SELECT * FROM product LIMIT 15 OFFSET 15"
insert
$insert = [ 'name' => 'Product 31', 'category_id' => 5, 'manufacturer_id' => 1, 'status' => 1, 'image' => null, ]; $db->table('product')->insert($insert); # Output: "INSERT INTO product (name, category_id, manufacturer_id, status, image) # VALUES ('Product 31', '5', '1', '1', NULL") var_dump($db->insertId()); // Если успешное добавление - вернётся ID добавленной записи
update
# Если данные обновились, то вернётся 1, иначе вернётся 0 $update = [ 'name' => 'Product 1 updated', 'status' => 1, 'image' => null, ]; $db->table('product')->where('id', 1)->update($update); # Output: "UPDATE product SET name='Product 1 updated', status='1', image=NULL WHERE id='1'"
delete
# Удалить запись в таблице $db->table('product')->where('id', 31)->delete(); # Output: "DELETE FROM product WHERE id = '31'" # Очистить всю таблицу $db->table('product')->delete(); # Output: "TRUNCATE TABLE delete"
query
# Пример #1: Найти все записи $db->query('SELECT * FROM product WHERE `category_id` = ? AND status = ?', [1, 1])->fetchAll(); # Пример #2: Найти одну запись $db->query('SELECT * FROM product WHERE id = ? AND status = ?', [1, 1])->fetch(); # Пример #3: Другие запросы: like, update, insert, delete, etc... $db->query("UPDATE `product` SET `status` = ? WHERE id = ?", [0, 1])->exec();
numRows
var_dump($db->numRows());
cache
# Используйте: ...->cache($time)->... $db->table('product')->where('status', 1)->cache(60)->getAll(); # cache time: 60 seconds
error
var_dump($db->error());
queryCount
# Количество всех SQL-запросов на странице $queryCount = $db->queryCount(); var_dump($queryCount);
getQuery
# Последний SQL запрос $lastQuery = $db->getQuery(); var_dump($lastQuery);
join
# Пример #1: $db->table('product as p') ->select('p.id as id, p.name as name, c.name as category') ->join('category as c', 'c.id', 'p.category_id') ->where('p.status', 1) ->getAll(); # Output: # "SELECT p.id AS id, p.name AS name, c.name AS category # FROM product as p # JOIN category as c ON c.id=p.category_id WHERE p.status='1'" # Пример #2: $db->table('product as p') ->select('p.id as id, p.name as name, c.name as category') ->leftJoin('category as c', 'c.id', '=', 'p.category_id') ->where('p.status', 1) ->limit(5) ->getAll(); # Output: "SELECT p.id AS id, p.name AS name, c.name AS category # FROM product as p # JOIN category as c ON c.id=p.category_id WHERE p.status='1' LIMIT 5"