scaleplan / sql-templater
Query templater for PostgreSQL
Requires
- php: >=7.0
This package is auto-updated.
Last update: 2025-03-16 20:27:15 UTC
README
Helper class for SQL templating.
Installation
composer reqire scaleplan/sql-templater
Description
Includes several predefined directives that can be included in SQL queries for simplicity:
- ####[fields]
Instead of this part of the SQL query inserts a string of data keys that came to the input, i.e. if we have a query:
INSERT INTO
user
([fields])
VALUES
...
And data:
$data = [
'name' = > 'Ivan'
'surname' = > 'Fuckov'
];
then after processing the request will take the form:
INSERT INTO
user
(name,
surname)
VALUES
...
This is useful if we do not know exactly which set of data will come to the entrance.
The action is similar to [fields] + inside : not(...) (instead of points) you can specify a comma separated list of non-include fields, for example:
INSERT INTO
user
([fields:not (sur)])
VALUES
...
converted to:
INSERT INTO
user
(name)
VALUES
...
with the same data.
- ####[expression]
Similar to [fields] only fills in another part of the query, for example:
Request:
INSERT INTO
user
([fields])
VALUES
[expression]
and data:
$data = [
'name' = > 'Ivan'
'surname' = > 'Fuckov'
];
The result will be:
INSERT INTO
user
(name,
surname)
VALUES
(:name,
: surname)
If there are multiple lines in the input:
$data = [
[
'name' = > 'Ivan'
'surname' = > 'Fuckov'
],
[
'name' = > 'Vasiliy'
'surname' = > 'Chekhov'
]
];
the result will be:
INSERT INTO
user
(name,
surname)
VALUES
(: name0,
: surname0),
(: name1,
: surname1)
and the data will look like:
$data = [
'name0' = > 'Ivan'
'surname0' = > 'Fuckov'
'name1' = > 'Vasiliy'
'surname1' = > 'Chekhov'
];
I think here everything is clear - the substitution of placeholders - all except those inside not(...)
It should be noted that if among the parameter values there is an array, it can also be correctly processed:
Request:
INSERT INTO
user
([fields])
VALUES
[expression]
Characteristic:
$data = [
'name' = > 'Ivan'
'surname' = > 'Fuckov',
'phone_numbers' => [
'+7905555555',
'+7904444444'
]
];
Result:
INSERT INTO
user
(name,
surname)
VALUES
(:name,
: surname,
ARRAY [: phone_numbers0, phone_numbers1])
This conversion can be disabled by passing the template parameter $convertArrays with the value false.
In addition, it is possible to use the optional parts of the query, which are used only if the parameters used in them, for example:
SELECT
*
FROM
user
[WHERE
group =: group]
If data is transferred:
$data = [
'group' = > 'admins'
];
then the resulting query will be:
SELECT
*
FROM
user
WHERE
group =: group
and if the group parameter is passed, the condition will be completely thrown out and the query will return all users.