Download the PHP package qstart-soft/query-builder without Composer

qstart-soft/query-builder

Library for creating DML (Data Manipulation Language) SQL statements.

• Part 1. Basics
• Part 2. Table format
• Part 3. Conditions format
• Part 4. Select Sql Statement
• Part 5. INSERT Sql Statement
• Part 6. UPDATE Sql Statement
• Part 7. DELETE Sql Statement

Part 1. Basics

Entry point: \ The entry point for creating SQL statements is the Query::class factory.

Creation: \ Getting sql statement and binding parameters:

Expressions: \ Expressions are classes that inherit an \Qstart\Db\QueryBuilder\DML\Expression\ExprInterface interface. \ These classes allow you to create specific expressions for a SQL query.

The initial expression class allows you to pass an immutable expression that will be added to the query without modification

Several other expressions:

It is true that for different SQL dialects the same constructions may have different syntax. \ To do this, you can pass the dialect into the expression.

Part 2. Table format

The table format is the same for all available methods

• join()
• innerJoin()
• leftJoin()
• rightJoin()
• SelectQuery::from()
• UpdateQuery::joinFrom()
• UpdateQuery::setTable()
• InsertQuery::into()
• DeleteQuery::from()

Available formats (Using the SelectQuery::from() method as an example):

You can also change the alias of the first table for methods:

• SelectQuery::from()
• UpdateQuery::setTable()
• InsertQuery::into()
• DeleteQuery::from()

An example:

Part 3. Conditions format

Any condition can be passed in the following formats:

1. Array with equality conditions.

An array is a key-value pair. The key is the left expression. The value is ine of the options below

2. Any Expression instance of ExprInterface

3. String format

4. Group using the "OR", "AND" "NOT" operators

Then it becomes necessary to combine conditions using the operators AND, OR, NOT. \ All of these combinations have the same format [operator, condition, condition, ...]:

The first in the array must be the operator AND / OR / NOT. \ Next, separated by commas, are conditions in one of three formats (array, expression, string). These conditions can also be in the format with the operator AND / OR / NOT

For example: ['AND', $condition1, $condition2, ['OR', $condition3, $condition4]]

How to use this with a SELECT query, for example:

Part 4. Select Sql Statement

1. Select

To construct a SELECT clause, you need to use methods

• Query::select()->select() to create SELECT clause
• Query::select()->addSelect() to add values to SELECT clause
• Query::select()->distinct(true) to add DISTINCT keyword

The method select() overwrites all previously added values!

The clause can be construct in different formats.

2. Where

To construct a WHERE clause, you need to use methods

• Query::select()->where() \ to create WHERE clause
• Query::select()->andWhere() \ Adding a condition using the AND operator to the current conditions. Identical - ['and', current conditions, new conditions]
• Query::select()->orWhere() \ Adding a condition using the OR operator to the current conditions. Identical - ['or', current conditions, new conditions]

The method where() overwrites all previously added values!

All methods accept conditions in the format described above in 'Conditions format'.

You can also use methods that will remove all NULL values from the condition. For Expression instances the method ExprInterface::isEmpty() will be called.

• Query::select()->filterWhere()
• Query::select()->andFilterWhere()
• Query::select()->orFilterWhere()

The method filterWhere() overwrites all previously added values!

All methods accept conditions in the format described above in 'Conditions format'.

3. Group By

To construct a GROUP BY clause, you need to use methods

• Query::select()->groupBy() to create GROUP BY clause
• Query::select()->addGroupBy() to add values to GROUP BY clause

The method groupBy() overwrites all previously added values!

The clause can be construct in different formats.

4. Order By

To construct a ORDER BY clause, you need to use methods

• Query::select()->orderBy() to create ORDER BY clause
• Query::select()->addOrderBy() to add values to ORDER BY clause

The method orderBy() overwrites all previously added values!

The clause can be construct in different formats.

5. Having

To construct a HAVING clause, you need to use methods

• Query::select()->having()
• Query::select()->andHaving()
• Query::select()->orHaving()

The method having() overwrites all previously added values!

These methods work just look like 'WHERE' methods.

6. OFFSET

To construct a OFFSET clause, you need to use method

• Query::select()->offset()

Use null value to disable offset. \ The offset may be int|ExprInterface|SelectQuery|null

7. LIMIT

To construct a LIMIT clause, you need to use method

• Query::select()->limit()

Use null value to disable limit. \ The offset may be int|ExprInterface|SelectQuery|null

8. JOIN

To construct a different JOIN clauses, you need to use method

• Query::select()->join()
• Query::select()->leftJoin()
• Query::select()->rightJoin()
• Query::select()->innerJoin()

All these methods accept conditions in the format described above in 'Conditions format'. \ Also, all these methods accept table in the format described above in 'Table format'.

Example of usage:

9. UNION

To use union queries, you need to use method.

• Query::select()->union() to add union query
• Query::select()->deleteUnion() to delete all union queries

ORDER BY clause will be combined from all queries and added to the end of the union queries

The query may be string|ExprInterface|SelectQuery

Part 5. INSERT Sql Statement

Creating the INSERT INTO statement with format: \ INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

To specify table name use:

• Query::insert()->into() \ This method accept table in the format described above in 'Table format'.

To add group of values to a VALUES clause use methods:

• Query::insert()->addValues($data) \ The data should be an array in the format [column1 => value1, ...] or instance of QueryInterface
• Query::insert()->addMultipleValues($data) \ The data should be an array of arrays in the format [column1 => value1, ...]

To change start or end of statement use methods:

• Query::insert()->setStartOfQuery() \ The expression INSERT INTO will be replaced with the passed expression
• Query::insert()->setEndOfQuery() \ The expression will be added to the end of the query

Part 6. UPDATE Sql Statement

1. Table

To specify table name use:

• Query::update()->setTable() \ This method accept table in the format described above in 'Table format'.

2. SET

To construct a SET clause, you need to use methods

• Query::update()->set($attributes) \ To create SET clause
• Query::update()->addSet($attributes) \ To add attributes to SET clause

The method set() overwrites all previously added attributes!

Attributes can be passed in different formats. \ If we pass it with the key, we will try to add the value as a query parameter. \ If passed without a key, the value will be a string. \ The value can be passed as a string, an ExprInterface instance, or a QueryInterface instance.

3. Where

To construct a WHERE clause see the description in "Part 3. Select Sql Statement". Format and methods will be completely identical with Select Sql Statement

4. Join

To construct a different JOIN clauses see the description in "Part 3. Select Sql Statement". Format and methods will be completely identical with Select Sql Statement.

5. JOIN FROM

To construct a FROM clause, you need to use method:

• Query::update()->joinFrom() This method accept table in the format described above in 'Table format'.

Use null value to disable FROM clause

6. Limit

To construct a LIMIT clause see the description in "Part 3. Select Sql Statement". Format and methods will be completely identical with Select Sql Statement

7. Start and end clauses

To change start or end of statement use methods:

• Query::update()->setStartOfQuery() \ The expression UPDATE will be replaced with the passed expression
• Query::update()->setEndOfQuery() \ The expression will be added to the end of the query

Part 7. DELETE Sql Statement

1. From

To specify table name use:

• Query::delete()->from() \ This method accept table in the format described above in 'Table format'.

2. Where

To construct a WHERE clause see the description in "Part 3. Select Sql Statement". Format and methods will be completely identical with Select Sql Statement

3. Join

To construct a different JOIN clauses see the description in "Part 3. Select Sql Statement". Format and methods will be completely identical with Select Sql Statement.

4. Using

To construct a USING clause, you need to use method:

• Query::delete()->using() This method accept table in the format described above in 'Table format'.

Use null value to disable USING clause

5. Limit

To construct a LIMIT clause see the description in "Part 3. Select Sql Statement". Format and methods will be completely identical with Select Sql Statement

6. Start and end clauses

To change start or end of statement use methods:

• Query::delete()->setStartOfQuery() \ The expression DELETE FROM will be replaced with the passed expression
• Query::delete()->setEndOfQuery() \ The expression will be added to the end of the query