lucinda/queries

API that generates SQL statements programmatically based on official standards or their vendor-specific derivation

Maintainers

Details

github.com/aherne/php-sql-statements-api

Homepage

Source

Issues

Installs: 18 076

Dependents: 0

Suggesters: 0

Security: 0

Stars: 1

Watchers: 2

Forks: 1

Open Issues: 0

v3.0.3 2022-06-11 15:04 UTC

Requires

  • php: ^8.1

Requires (Dev)

MIT 8b7148f1ba9d60fd7e0fd40b67ff8acf84468ed9

  • Lucian Popescu <lucian.gabriel.popescu.woop@gmail.com>

sqlquerygeneration

This package is auto-updated.

Last update: 2024-04-11 19:18:07 UTC

README

Table of contents:

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
  • unit tests: API has 100% Unit Test coverage, using 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:

composer require lucinda/queries

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:
  • TRUNCATE:

For each vendor implementing SQL standards, you can either use above or their vendor-specific derivations. MySQL vendor is already supported:

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:

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:

$statement = new \Lucinda\Query\Select("users", "t1");
$statement->fields(["t3.name"]);
$statement->joinInner("user_departments", "t2")->on(["t1.id"=>"t2.user_id"]);
$statement->joinInner("departments", "t3")->on(["t2.department_id"=>"t3.id"]);
$statement->where(["t1.id"=>":id"]);
$statement->orderBy(["t3.name"]);

Encapsulating:

SELECT t3.name
FROM users AS t1
INNER JOIN user_departments AS t2 ON t1.id = t2.user_id
INNER JOIN departments AS t3 ON t2.department_id = t3.id
WHERE t1.id = :id
ORDER BY t3.name

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:

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:

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: