Querying¶
GLPI framework provides a simple request generator:
- without having to write SQL
- without having to quote table and field name
- without having to take care of freeing resources
- iterable
- countable
Basic usage¶
<?php
foreach ($DB->request(...) as $id => $row) {
//... work on each row ...
}
$req = $DB->request(...);
if ($row = $req->next()) {
// ... work on a single row
}
$req = $DB->request(...);
if (count($req)) {
// ... work on result
}
Arguments¶
The request
method takes two arguments:
- table name(s): a string or an array of string
(optional when given as
FROM
option) - option(s): array of options
Giving full SQL statement¶
If the only option is a full SQL statement, it will be used. This usage is deprecated, and should be avoid when possible.
Note
To make a database query that could not be done using recommended way (calling SQL functions such as NOW()
, ADD_DATE()
, … for example), you can do:
<?php
$DB->request('SHOW COLUMNS FROM `glpi_computers`');
Without option¶
In this case, all the data from the selected table is iterated:
<?php
$DB->request(['FROM' => 'glpi_computers']);
// => SELECT * FROM `glpi_computers`
$DB->request('glpi_computers');
// => SELECT * FROM `glpi_computers`
Fields selection¶
You can use either the SELECT
or FIELDS
options, an additional DISTINCT
option might be specified.
Note
Changed in version 9.5.0.
Using DISTINCT FIELDS
or SELECT DISTINCT
options is deprecated.
<?php
$DB->request(['SELECT' => 'id', 'FROM' => 'glpi_computers']);
// => SELECT `id` FROM `glpi_computers`
$DB->request(['FIELDS' => 'id', 'FROM' => 'glpi_computers']);
// => SELECT `id` FROM `glpi_computers`
$DB->request(['SELECT' => 'name', 'DISTINCT' => true, 'FROM' => 'glpi_computers']);
// => SELECT DISTINCT `name` FROM `glpi_computers`
$DB->request(['FIELDS' => 'name', 'DISTINCT' => true, 'FROM' => 'glpi_computers']);
// => SELECT DISTINCT `name` FROM `glpi_computers`
The fields array can also contain per table sub-array:
<?php
$DB->request(['FIELDS' => ['glpi_computers' => ['id', 'name']], 'FROM' => 'glpi_computers']);
// => SELECT `glpi_computers`.`id`, `glpi_computers`.`name` FROM `glpi_computers`"
Using JOINs¶
You need to use criteria, usually a FKEY
to describe how to join the tables.
Note
New in version 9.3.1.
The ON
keyword can also be used as an alias of FKEY
.
Multiple tables, native join¶
You need to use criteria, usually a FKEY
(or the ON
equivalent), to describe how to join the tables:
<?php
$DB->request(['FROM' => ['glpi_computers', 'glpi_computerdisks'],
'FKEY' => ['glpi_computers'=>'id',
'glpi_computerdisks'=>'computer_id']]);
$DB->request(['glpi_computers', 'glpi_computerdisks'],
['FKEY' => ['glpi_computers'=>'id',
'glpi_computerdisks'=>'computer_id']]);
// => SELECT * FROM `glpi_computers`, `glpi_computerdisks`
// WHERE `glpi_computers`.`id` = `glpi_computerdisks`.`computer_id`
Left join¶
Using the LEFT JOIN
option, with some criteria, usually a FKEY
(or the ON
equivalent):
<?php
$DB->request(['FROM' => 'glpi_computers',
'LEFT JOIN' => ['glpi_computerdisks' => ['FKEY' => ['glpi_computers' => 'id',
'glpi_computerdisks' => 'computer_id']]]]);
// => SELECT * FROM `glpi_computers`
// LEFT JOIN `glpi_computerdisks`
// ON (`glpi_computers`.`id` = `glpi_computerdisks`.`computer_id`)
Inner join¶
Using the INNER JOIN
option, with some criteria, usually a FKEY
(or the ON
equivalent):
<?php
$DB->request(['FROM' => 'glpi_computers',
'INNER JOIN' => ['glpi_computerdisks' => ['FKEY' => ['glpi_computers' => 'id',
'glpi_computerdisks' => 'computer_id']]]]);
// => SELECT * FROM `glpi_computers`
// INNER JOIN `glpi_computerdisks`
// ON (`glpi_computers`.`id` = `glpi_computerdisks`.`computer_id`)
Right join¶
Using the RIGHT JOIN
option, with some criteria, usually a FKEY
(or the ON
equivalent):
<?php
$DB->request(['FROM' => 'glpi_computers',
'RIGHT JOIN' => ['glpi_computerdisks' => ['FKEY' => ['glpi_computers' => 'id',
'glpi_computerdisks' => 'computer_id']]]]);
// => SELECT * FROM `glpi_computers`
// RIGHT JOIN `glpi_computerdisks`
// ON (`glpi_computers`.`id` = `glpi_computerdisks`.`computer_id`)
Join criterion¶
New in version 9.3.1.
It is also possible to add an extra criterion for any JOIN clause. You have to pass an array with first key equal to AND
or OR
and any iterator valid criterion:
<?php
$DB->request([
'FROM' => 'glpi_computers',
'INNER JOIN' => [
'glpi_computerdisks' => [
'FKEY' => [
'glpi_computers' => 'id',
'glpi_computerdisks' => 'computer_id',
['OR' => ['glpi_computers.field' => ['>', 42]]]
]
]
]
]);
// => SELECT * FROM `glpi_computers`
// INNER JOIN `glpi_computerdisks`
// ON (`glpi_computers`.`id` = `glpi_computerdisks`.`computer_id` OR
// `glpi_computers`.`field` > '42'
// )
UNION queries¶
New in version 9.4.0.
An union query is an object, which contains an array of Sub queries. You just have to give a list of Subqueries you have already prepared, or arrays of parameters that will be used to build them.
<?php
$sub1 = new \QuerySubQuery([
'SELECT' => 'field1 AS myfield',
'FROM' => 'table1'
]);
$sub2 = new \QuerySubQuery([
'SELECT' => 'field2 AS myfield',
'FROM' => 'table2'
]);
$union = new \QueryUnion([$sub1, $sub2]);
$DB->request([
'FROM' => $union
]);
// => SELECT * FROM (
// SELECT `field1` AS `myfield` FROM `table1`
// UNION ALL
// SELECT `field2` AS `myfield` FROM `table2`
// )
As you can see on the above example, a UNION ALL
query is built. If you want your results to be deduplicated,
(standard UNION
):
<?php
//...
//passing true as second argument will activate deduplication.
$union = new \QueryUnion([$sub1, $sub2], true);
//...
Warning
Keep in mind that deduplicating a UNION query may have a huge cost on database server.
Most of the time, you can issue a UNION ALL
and deduplicate the results in the code.
Counting¶
Using the COUNT
option:
<?php
$DB->request(['FROM' => 'glpi_computers', 'COUNT' => 'cpt']);
// => SELECT COUNT(*) AS cpt FROM `glpi_computers`
Grouping¶
Using the GROUPBY
option, which contains a field name or an array of field names.
<?php
$DB->request(['FROM' => 'glpi_computers', 'GROUPBY' => 'name']);
// => SELECT * FROM `glpi_computers` GROUP BY `name`
$DB->request('glpi_computers', ['GROUPBY' => ['name', 'states_id']]);
// => SELECT * FROM `glpi_computers` GROUP BY `name`, `states_id`
Order¶
Using the ORDER
option, with value a field or an array of fields. Field name can also contains ASC or DESC suffix.
<?php
$DB->request(['FROM' => 'glpi_computers', 'ORDER' => 'name']);
// => SELECT * FROM `glpi_computers` ORDER BY `name`
$DB->request('glpi_computers', ['ORDER' => ['date_mod DESC', 'name ASC']]);
// => SELECT * FROM `glpi_computers` ORDER BY `date_mod` DESC, `name` ASC
Request pager¶
Using the START
and LIMIT
options:
<?php
$DB->request('glpi_computers', ['START' => 5, 'LIMIT' => 10]);
// => SELECT * FROM `glpi_computers` LIMIT 10 OFFSET 5"
Criteria¶
Using the WHERE
option with an array of criteria.
The first level of the array is considered as an implicit logical AND
.
By default, the array keys are considered as field names, and the values as values.
If this differs from what you want, there are a few workarounds that are covered later.
Simple criteria¶
A field name and its wanted value:
<?php
$DB->request(['FROM' => 'glpi_computers', 'WHERE' => ['is_deleted' => 0]]);
// => SELECT * FROM `glpi_computers` WHERE `is_deleted` = 0
$DB->request(['FROM' => 'glpi_computers', 'WHERE' => ['is_deleted' => 0,
'name' => 'foo']);
// => SELECT * FROM `glpi_computers` WHERE `is_deleted` = 0 AND `name` = 'foo'
$DB->request(['FROM' => 'glpi_computers', 'WHERE' => ['users_id' => [1,5,7]]]);
// => SELECT * FROM `glpi_computers` WHERE `users_id` IN (1, 5, 7)
When using an array as a value, the operator is automatically set to IN
.
Make sure that you verify that the array cannot be empty, otherwise an error will be thrown.
When using null
as a value, the operator is automatically set to IS
and the value is set to the NULL
keyword.
Logical OR
, AND
, NOT
¶
Using the OR
, AND
, or NOT
option with an array of criteria:
<?php
$DB->request(['FROM' => 'glpi_computers', 'WHERE' => ['OR' => ['is_deleted' => 0,
'name' => 'foo']]]);
// => SELECT * FROM `glpi_computers` WHERE (`is_deleted` = 0 OR `name` = 'foo')"
$DB->request(['FROM' => 'glpi_computers', 'WHERE' => ['NOT' => ['id' => [1,2,7]]]]);
// => SELECT * FROM `glpi_computers` WHERE NOT (`id` IN (1, 2, 7))
Using a more complex expression with AND
and OR
:
<?php
$DB->request(['FROM' => 'glpi_computers', 'WHERE' => ['is_deleted' => 0,
['OR' => ['name' => 'foo', 'otherserial' => 'otherunique']],
['OR' => ['locations_id' => 1, 'serial' => 'unique']]]
]);
// => SELECT * FROM `glpi_computers` WHERE `is_deleted` = '0' AND ((`name` = 'foo' OR `otherserial` = 'otherunique')) AND ((`locations_id` = '1' OR `serial` = 'unique'))
Operators¶
Default operator is =
, but other operators can be used, by giving an array containing operator and value.
<?php
$DB->request(['FROM' => 'glpi_computers', 'WHERE' => ['date_mod' => ['>' , '2016-10-01']]]);
// => SELECT * FROM `glpi_computers` WHERE `date_mod` > '2016-10-01'
$DB->request(['FROM' => 'glpi_computers', 'WHERE' => ['name' => ['LIKE' , 'pc00%']]]);
// => SELECT * FROM `glpi_computers` WHERE `name` LIKE 'pc00%'
Known operators are =
, !=
, <
, <=
, >
, >=
, LIKE
, REGEXP
, NOT LIKE
, NOT REGEX
, &
(BITWISE AND), and |
(BITWISE OR).
Aliases¶
You can use SQL aliases (SQL AS
keyword). To achieve that, just write the alias you want on the table name or the field name; then use it in your parameters:
<?php
$DB->request(['FROM' => 'glpi_computers AS c']);
// => SELECT * FROM `glpi_computers` AS `c`
$DB->request(['SELECT' => 'field AS f', 'FROM' => 'glpi_computers AS c']);
// => SELECT `field` AS `f` FROM `glpi_computers` AS `c`
Aggregate functions¶
New in version 9.3.1.
You can use some aggregation SQL functions on fields: COUNT
, SUM
, AVG
, MIN
and MAX
are supported. Just set the function as the key in your fields array:
<?php
$DB->request(['SELECT' => ['COUNT' => 'field', 'bar'], 'FROM' => 'glpi_computers', 'GROUPBY' => 'field']);
// => SELECT COUNT(`field`), `bar` FROM `glpi_computers` GROUP BY `field`
$DB->request(['SELECT' => ['bar', 'SUM' => 'amount AS total'], 'FROM' => 'glpi_computers', 'GROUPBY' => 'amount']);
// => SELECT `bar`, SUM(`amount`) AS `total` FROM `glpi_computers` GROUP BY `amount`
Sub queries¶
New in version 9.3.1.
You can use subqueries, using the specific QuerySubQuery class. It takes two arguments: the first is an array of criteria to get the query built, and the second is an optional operator to use. Allowed operators are the same than documented below plus IN and NOT IN. Default operator is IN.
<?php
$sub_query = new \QuerySubQuery([
'SELECT' => 'id',
'FROM' => 'subtable',
'WHERE' => [
'subfield' => 'subvalue'
]
]);
$DB->request(['FROM' => 'glpi_computers', 'WHERE' => ['field' => $sub_query]]);
// => SELECT * FROM `glpi_computers` WHERE `field` IN (SELECT `id` FROM `subtable` WHERE `subfield` = 'subvalue')
$sub_query = new \QuerySubQuery([
'SELECT' => 'id',
'FROM' => 'subtable',
'WHERE' => [
'subfield' => 'subvalue'
]
]);
$DB->request(['FROM' => 'glpi_computers', 'WHERE' => ['NOT' => ['field' => $sub_query]]]);
// => SELECT * FROM `glpi_computers` WHERE NOT `field` IN (SELECT `id` FROM `subtable` WHERE `subfield` = 'subvalue')
$sub_query = new \QuerySubQuery([
'SELECT' => 'id',
'FROM' => 'subtable',
'WHERE' => [
'subfield' => 'subvalue'
]
], 'myalias');
$DB->request(['FROM' => 'glpi_computers', 'SELECT' => [$sub_query, 'id']]);
// => SELECT (SELECT `id` FROM `subtable` WHERE `subfield` = 'subvalue') AS `myalias`, id FROM `glpi_computers`
What if iterator does not provide what I’m looking for?¶
Even if we do our best to get as many things as possible implemented in the iterator, there are several things that are missing… Consider for example you want to use the SQL NOW() function, or want to use a value based on another field: there is no native way to achieve that.
Right now, there is a QueryExpression class that would permit to do such things on values (an not on fields since it is not possible to use a class instance as an array key).
Warning
The QueryExpression class will pass raw SQL. You are in charge to escape name and values you use into it!
For example, to use the SQL NOW() function:
<?php
$DB->request([
'FROM' => 'my_table',
'WHERE' => [
'date_end' => ['>', new \QueryExpression('NOW()')]
]
]);
// SELECT * FROM `my_table` WHERE `date_end` > NOW()
An example with a field value:
<?php
$DB->request([
'FROM' => 'my_table',
'WHERE' => [
'field' => new \QueryExpression(DBmysql::quoteName('other_field'))
]
]);
// SELECT * FROM `my_table` WHERE `field` = `other_field`
New in version 9.3.1.
You can also use some function or non supported stuff on field part by using a RAW entry in the query:
<?php
$DB->request([
'FROM' => 'my_table',
'WHERE' => [
'RAW' => [
DBmysql::quoteName('field') => DBmysql::quoteName('field2')
]
]
]);
// SELECT * FROM `my_table` WHERE LOWER(`field`) = 'value'
New in version 9.5.0.
You can use a QueryExpression object in the FIELDS statement:
<?php
$DB->request([
'FIELDS' => [
'glpi_computers' => ['id'],
new QueryExpression("CONCAT(`glpi_computers`.`name`, '.', `glpi_domains`.`name`) AS `fullname`")
],
'FROM' => 'glpi_computers',
'LEFT JOIN' => [
'glpi_domains' => [
'FKEY' => [
'glpi_computers' => 'domains_id',
'glpi_domains' => 'id',
]
]
]
]);
// => SELECT `glpi_computers`.`id`, CONCAT(`glpi_computers`.`name`, '.', `glpi_domains`.`name`) AS `fullname` FROM `glpi_computers` LEFT JOIN `glpi_domains` ON (`glpi_computers`.`domains_id` = `glpi_domains`.`id`)
You can use a QueryExpression object in the FROM statement:
<?php
$DB->request([
'FROM' => new QueryExpression('(SELECT * FROM glpi_computers) as computers'),
]);
// => SELECT * FROM (SELECT * FROM glpi_computers) as computers
When you need to manually quote identifies or values, it is recommended that you use $DB::quoteName
and $DB::quoteValue
respectively rather than directly adding the quotes to ensure future compatibility.