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 recommanded way (calling SQL functions such as NOW(), ADD_DATE(), … for example), you can do:

<?php
$DB->request('SELECT id FROM glpi_users WHERE end_date > NOW()');

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 10.0.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('glpi_computers', ['FIELDS' => 'id']);
// => SELECT `id` FROM `glpi_computers`

$DB->request(['SELECT' => 'name', 'DISTINCT' => true, 'FROM' => 'glpi_computers']);
// => SELECT DISTINCT `name` FROM `glpi_computers`

$DB->request('glpi_computers', ['FIELDS' => 'name', 'DISTINCT' => true]);
// => SELECT DISTINCT `name` FROM `glpi_computers`

The fields array can also contain per table sub-array:

<?php
$DB->request('glpi_computers', ['FIELDS' => ['glpi_computers' => ['id', 'name']]]);
// => 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 aslo 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 deduplicate a UNION query may have a huge cost on database server.

Most of the time, you can issue a UNION ALL and dedup 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

Other option are considered as an array of criteria (implicit logicical AND)

The WHERE can also be used for legibility.

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('glpi_computers', ['is_deleted' => 0,
                                'name'       => 'foo']);
// => SELECT * FROM `glpi_computers` WHERE `is_deleted` = 0 AND `name` = 'foo'

$DB->request('glpi_computers', ['users_id' => [1,5,7]]);
// => SELECT * FROM `glpi_computers` WHERE `users_id` IN (1, 5, 7)

Logical OR, AND, NOT

Using the OR, AND, or NOT option with an array of criteria:

<?php
$DB->request('glpi_computers', ['OR' => ['is_deleted' => 0,
                                         'name'       => 'foo']]);
// => SELECT * FROM `glpi_computers` WHERE (`is_deleted` = 0 OR `name` = 'foo')"

$DB->request('glpi_computers', ['NOT' => ['id' => [1,2,7]]]);
// => SELECT * FROM `glpi_computers` WHERE NOT (`id` IN (1, 2, 7))

Operators

Default operator is =, but other operators can be used, by giving an array containing operator and value.

<?php
$DB->request('glpi_computers', ['date_mod' => ['>' , '2016-10-01']]);
// => SELECT * FROM `glpi_computers` WHERE `date_mod` > '2016-10-01'

$DB->request('glpi_computers', ['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('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()

Another 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'  => [
         'LOWER(' . DBmysql::quoteName('field') . ')' => strtolower('Value')
     ]
   ]
]);
// SELECT * FROM `my_table` WHERE LOWER(`field`) = 'value'

Creative Commons License