Download the PHP package lucinda/queries without Composer
On this page you can find all versions of the php package lucinda/queries. It is possible to download/install these versions without Composer. Possible dependencies are resolved automatically.
Download lucinda/queries
More information about lucinda/queries
Files in lucinda/queries
Package queries
Short Description API that generates SQL statements programmatically based on official standards or their vendor-specific derivation
License MIT
Homepage https://github.com/aherne/php-sql-statements-api
Informations about the package queries
SQL Statements API
Table of contents:
- About
- Installation
- Unit Tests
- Examples
- Reference Guide
About
The purpose of this API is to automate generation of SQL statements (queries) based on SQL standards or their vendor-specific derivation. API is fully PSR-4 compliant, only requiring PHP 8.1+ interpreter. To quickly see how it works, check:
- installation: describes how to install API on your computer
- UnitTest API instead of PHPUnit for greater flexibility
- examples: shows a example of API functionality
All classes inside use Lucinda\Query namespace!
Installation
To install this api, you only need to go to your project root then run this command from console:
Once you have it installed, you're able to generate queries. Each standard SQL statement corresponds to one or more classes:
- SELECT:
- Lucinda\Query\Select: encapsulates a single SELECT statement (eg: SELECT id FROM table)
- Lucinda\Query\SelectGroup: encapsulates a group of SELECT statements united by a SET operator (eg: (SELECT id from table1) UNION (SELECT id FROM table2))
- INSERT:
- Lucinda\Query\Insert: encapsulates an INSERT INTO ... VALUES statement (eg: INSERT INTO table (id, name) VALUES (1, 'asd'))
- Lucinda\Query\InsertSelect: encapsulates an INSERT INTO ... SELECT statement (eg: INSERT INTO table (id, name) SELECT id, name FROM table2)
- UPDATE:
- Lucinda\Query\Update: encapsulates an UPDATE statement (eg: UPDATE users SET name='Lucian' WHERE id=18)
- DELETE:
- Lucinda\Query\Delete: encapsulates a DELETE statement (eg: DELETE FROM users WHERE id=18)
- TRUNCATE:
- Lucinda\Query\Truncate: encapsulates a TRUNCATE statement (eg: TRUNCATE TABLE users)
For each vendor implementing SQL standards, you can either use above or their vendor-specific derivations. MySQL vendor is already supported:
- SELECT:
- Lucinda\Query\Select in order to support vendor-specific operations (eg: SQL_NO_CACHE)
- INSERT:
- Lucinda\Query\Insert in order to support vendor-specific operations (eg: IGNORE)
- Lucinda\Query\InsertSelect in order to support vendor-specific operations (eg: IGNORE)
- Lucinda\Query\Vendor\MySQL\InsertSet: encapsulates vendor-specific statement INSERT INTO ... SET statement (eg: INSERT INTO table (id, name) SET id=1, name='Lucian')
- REPLACE:
- Lucinda\Query\Insert in order to support vendor-specific REPLACE INTO ... VALUES statement
- Lucinda\Query\InsertSelect in order to support vendor-specific REPLACE INTO ... SELECT statement
- Lucinda\Query\Vendor\MySQL\ReplaceSet: encapsulates vendor-specific statement REPLACE INTO ... SET statement (eg: REPLACE INTO table (id, name) SET id=1, name='Lucian')
- UPDATE
- Lucinda\Query\Update in order to support vendor-specific operations (eg: IGNORE)
- DELETE
- Lucinda\Query\Delete in order to support vendor-specific operations (eg: IGNORE)
Each of above or clauses they individually call to implement \Stringable, which manages conversion of statement/clause into SQL via __toString() method.
Unit Tests
For tests and examples, check following files/folders in API sources:
- test.php: runs unit tests in console
- unit-tests.xml: sets up unit tests
- tests: unit tests for classes from src folder
- tests_drivers: unit tests for classes from drivers folder
NOTICE: super-global functions __toString() (used by statements and clauses) were tested via toString() unit test methods. By themselves they cannot be mirrored by a unit test (due to string instead of Result return type), so they will give: Invalid unit test response
Examples
To see examples how each classes are used, check unit tests in tests or tests_drivers folder! Simple example:
Encapsulating:
Reference Guide
Class Select
Lucinda\Query\Select encapsulates a standard SELECT statement via following public methods:
Method | Arguments | Returns | Description |
---|---|---|---|
__construct | string $table, string $alias="" | void | Constructs a SELECT statement based on table name and optional alias |
distinct | void | void | Sets statement as DISTINCT, filtering out repeating rows |
fields | array $columns = [] | Lucinda\Query\Clause\Fields | Sets fields or columns to select |
joinLeft | string $tableName, string $tableAlias = "" | Lucinda\Query\Clause\Join | Adds a LEFT JOIN statement |
joinRight | string $tableName, string $tableAlias = "" | Lucinda\Query\Clause\Join | Adds a RIGHT JOIN statement |
joinInner | string $tableName, string $tableAlias = "" | Lucinda\Query\Clause\Join | Adds a INNER JOIN statement |
joinCross | string $tableName, string $tableAlias = "" | Lucinda\Query\Clause\Join | Adds a CROSS JOIN statement |
where | array $condition=[], Lucinda\Query\Operator\Logical $logicalOperator = Lucinda\Query\Operator\Logical::AND | Lucinda\Query\Clause\Condition | Sets up WHERE clause. |
groupBy | array $columns = [] | Lucinda\Query\Clause\Columns | Sets up GROUP BY statement |
having | array $condition=[], Lucinda\Query\Operator\Logical $logicalOperator = Lucinda\Query\Operator\Logical::AND | Lucinda\Query\Clause\Condition | Sets up HAVING clause. |
orderBy | array $fields = [] | Lucinda\Query\Clause\OrderBy | Sets up ORDER BY clause |
limit | int $limit, int $offset=0 | void | Sets a LIMIT clause |
__toString | void | string | Converts object to SQL statement. |
Class SelectGroup
Lucinda\Query\SelectGroup encapsulates a list of SELECT statements joined by a SET operator (eg: UNION) via following public methods:
Method | Arguments | Returns | Description |
---|---|---|---|
__construct | Lucinda\Query\Operator\Set $operator = Lucinda\Query\Operator\Set::UNION | void | Constructs a SELECT ... OPERATOR ... SELECT statement based on Set OPERATOR |
addSelect | Lucinda\Query\Select $select | void | Adds SELECT statement to group |
addSelect | Lucinda\Query\SelectGroup $select | void | Adds SELECT ... OPERATOR ... SELECT statement to group |
orderBy | array $fields = [] | Lucinda\Query\Clause\OrderBy | Sets up ORDER BY clause |
limit | int $limit, int $offset=0 | void | Sets a LIMIT clause |
__toString | void | string | Converts object to SQL statement. |
Class Insert
Lucinda\Query\Insert encapsulates a standard INSERT INTO VALUES statement via following public methods:
Method | Arguments | Returns | Description |
---|---|---|---|
__construct | string $table | void | Constructs a INSERT INTO ... VALUES statement based on table name |
columns | array $columns = [] | Lucinda\Query\Clause\Columns | Sets columns that will be inserted into. |
values | array $updates = [] | Lucinda\Query\Clause\Row | Adds row to table via list of values to insert in columns |
__toString | void | string | Converts object to SQL statement. |
Class InsertSelect
Lucinda\Query\InsertSelect encapsulates a standard INSERT INTO SELECT statement via following public methods:
Method | Arguments | Returns | Description |
---|---|---|---|
__construct | string $table | void | Constructs a INSERT INTO ... SELECT statement based on table name |
columns | array $columns = [] | Lucinda\Query\Clause\Columns | Sets columns that will be inserted into. |
select | Lucinda\Query\Select $select | void | Sets rows to insert based on a SELECT statement |
select | Lucinda\Query\SelectGroup $select | void | Sets rows to insert based on a SELECT ... OPERATOR ... SELECT group statement |
__toString | void | string | Converts object to SQL statement. |
Class Update
Lucinda\Query\Update encapsulates a standard UPDATE statement via following public methods:
Method | Arguments | Returns | Description |
---|---|---|---|
__construct | string $table | void | Constructs a UPDATE statement based on table name |
set | array $contents = [] | Lucinda\Query\Clause\Set | Sets up SET clause. |
where | array $condition = [], Lucinda\Query\Operator\Logical $logicalOperator = Lucinda\Query\Operator\Logical::AND | Lucinda\Query\Clause\Condition | Sets up WHERE clause. |
__toString | void | string | Converts object to SQL statement. |
Class Delete
Lucinda\Query\Delete encapsulates a standard DELETE statement via following public methods:
Method | Arguments | Returns | Description |
---|---|---|---|
__construct | string $table | void | Constructs a DELETE statement based on table name |
where | array $condition=[], Lucinda\Query\Operator\Logical $logicalOperator = Lucinda\Query\Operator\Logical::AND | Lucinda\Query\Clause\Condition | Sets up WHERE clause. |
__toString | void | string | Converts object to SQL statement. |
Class Truncate
Lucinda\Query\Truncate encapsulates a standard TRUNCATE statement via following public methods:
Method | Arguments | Returns | Description |
---|---|---|---|
__construct | string $table | void | Constructs a TRUNCATE statement based on table name |
__toString | void | string | Converts object to SQL statement. |
Class MySQL Select
Lucinda\Query\Vendor\MySQL\Select encapsulates a MySQL SELECT statement on top of Lucinda\Query\Select via following extra methods:
Method | Arguments | Returns | Description |
---|---|---|---|
setCalcFoundRows | void | void | Appends a SQL_CALC_FOUND_ROWS option to SELECT |
setStraightJoin | void | void | Appends a STRAIGHT_JOIN option to SELECT |
getCalcFoundRows | void | string | Gets query to retrieve found rows after a SELECT with SQL_CALC_FOUND_ROWS has ran |
In addition of above operations, where method can use:
- Lucinda\Query\Vendor\MySQL\Clause\Condition to support regexp condition and fulltext searches
- Lucinda\Query\Vendor\MySQL\Operator\Logical to support XOR operator
Class MySQL Insert
Lucinda\Query\Vendor\MySQL\Insert encapsulates a MySQL INSERT INTO VALUES statement on top of Lucinda\Query\Insert via following extra methods:
Method | Arguments | Returns | Description |
---|---|---|---|
ignore | void | void | Sets statement as IGNORE, ignoring foreign key errors and duplicates |
onDuplicateKeyUpdate | array $contents = [] | Lucinda\Query\Clause\Set | Sets up ON DUPLICATE KEY UPDATE clause. |
Class MySQL InsertSelect
Lucinda\Query\Vendor\MySQL\InsertSelect encapsulates a MySQL INSERT INTO SELECT statement on top of Lucinda\Query\InsertSelect via following extra methods:
Method | Arguments | Returns | Description |
---|---|---|---|
ignore | void | void | Sets statement as IGNORE, ignoring foreign key errors and duplicates |
onDuplicateKeyUpdate | array $contents = [] | Lucinda\Query\Clause\Set | Sets up ON DUPLICATE KEY UPDATE clause. |
Class MySQL InsertSet
Lucinda\Query\Vendor\MySQL\InsertSet encapsulates a MySQL INSERT INTO SET statement via following public methods:
Method | Arguments | Returns | Description |
---|---|---|---|
__construct | string $table | void | Constructs a INSERT INTO ... SET statement based on table name |
ignore | void | void | Sets statement as IGNORE, ignoring foreign key errors and duplicates |
set | array $contents = [] | Lucinda\Query\Clause\Set | Sets up SET clause. |
onDuplicateKeyUpdate | array $contents = [] | Lucinda\Query\Clause\Set | Sets up ON DUPLICATE KEY UPDATE clause. |
__toString | void | string | Converts object to SQL statement. |
Class MySQL Replace
Lucinda\Query\Vendor\MySQL\Replace encapsulates a MySQL REPLACE INTO VALUES statement on top of Lucinda\Query\Insert with no extra methods, except that INSERT will have REPLACE instead.
Class MySQL ReplaceSelect
Lucinda\Query\Vendor\MySQL\ReplaceSelect encapsulates a MySQL REPLACE INTO SELECT statement on top of Lucinda\Query\InsertSelect with no extra methods, except that INSERT will have REPLACE instead.
Class MySQL ReplaceSet
Lucinda\Query\Vendor\MySQL\ReplaceSet encapsulates a MySQL REPLACE INTO SET statement via following public methods:
Method | Arguments | Returns | Description |
---|---|---|---|
__construct | string $table | void | Constructs a REPLACE INTO ... SET statement based on table name |
set | array $contents = [] | Lucinda\Query\Clause\Set | Sets up SET clause. |
__toString | void | string | Converts object to SQL statement. |
Class MySQL Update
Lucinda\Query\Vendor\MySQL\Update encapsulates a MySQL UPDATE statement on top of Lucinda\Query\Update via following extra methods:
Method | Arguments | Returns | Description |
---|---|---|---|
ignore | void | void | Sets statement as IGNORE, ignoring foreign key errors and duplicates |
In addition of above operations, where method can use:
- Lucinda\Query\Vendor\MySQL\Clause\Condition to support regexp condition and fulltext searches
- Lucinda\Query\Vendor\MySQL\Operator\Logical to support XOR operator
Class MySQL Delete
Lucinda\Query\Vendor\MySQL\Delete encapsulates a MySQL DELETE statement on top of Lucinda\Query\Delete via following extra methods:
Method | Arguments | Returns | Description |
---|---|---|---|
ignore | void | void | Sets statement as IGNORE, ignoring foreign key errors and duplicates |
In addition of above operations, where method can use:
- Lucinda\Query\Vendor\MySQL\Clause\Condition to support regexp condition and fulltext searches
- Lucinda\Query\Vendor\MySQL\Operator\Logical to support XOR operator