Query Builder

As of version 1.9, PHPLucidFrame added a new feature called query builder that allows data to be retrieved in your database without writing raw SQL statements.

Selecting Data for Multiple Results

If you want to fetch an array of result set, you can use getResult().

$result = db_select('post')->getResult();
_pr($result); // array of results

This generates the following query:

SELECT * FROM `post`

Selecting Data for Single Result

To get a single result set, you can use getSingleResult().

$result = db_select('post')->getSingleResult();
_pr($result); // the result object

This generates the following query:

SELECT * FROM `post` LIMIT 1

Selecting Data for Muliple Fields

To fetch multiple fields, you can use fields('table`, array('field1', 'field2', ...)). The first parameter is table name or alias. The second paramter is a list of field names to fetch from the table.

$result = db_select('post', 'p')
    ->fields('p', array('id', 'title', 'created'))
    ->getResult();

_pr($result); // array of results

This generates the following query:

SELECT `p`.`id`, `p`.`title`, `p`.`created` FROM `post` `p`

If you want field alias, you can use nested array in fields(), for example,

$result = db_select('post', 'p')
    ->fields('p', array('id', array('title', 'title'), 'created'))
    ->getResult();

_pr($result); // array of results

In this case, post_title is alias for title. This generates the following query:

SELECT `p`.`id`, `p`.`title` `title`, `p`.`created` FROM `post` `p`

Selecting Data for Single Field

To fetch a single field, you can use field('field_name') and then fetch().

$title = db_select('post', 'p')
    ->field('title')
    ->fetch();

echo $title;

This generates the following query:

SELECT `p`.`title` FROM `post`

As of version 3.4.0, you can also use db_findColumn():

$title = db_findColumn('post', 'title');
echo $title;

Joining Tables

If you want to join multiple tables, you can use join($table, $alias, $condition, $type = 'INNER'). Here is explanation of the list of arguments:

  • $table is the table name to join.

  • $alias is the alias for the table name and you can also set null for this.

  • $condition is the joining condition e.g., table1.pk_id = table2.fk_id.

  • $type is the join type. Available options are INNER, LEFT, RIGHT, OUTER. Default is INNER.

    $result = db_select('post', 'p')
    ->fields('p', array('id', 'title'))
    ->fields('u', array(array('name', 'author')))
    ->fields('c', array(array('name', 'categoryName')))
    ->join('user', 'u', 'p.uid = u.uid')
    ->leftJoin('category', 'c', 'p.cat_id = c.cat_id')
    ->getResult();
_pr($result); // array of results

It generates the following query:

SELECT `p`.`id`, `p`.`title`, `u`.`name` `author`, `c`.`name` `categoryName`
FROM `post` `p`
INNER JOIN `user` `u` ON `p`.`uid` = `u`.`uid`
LEFT JOIN `category` `c` ON `p`.`id` = `c`.`id`

Note

  • Instead of fourth parameter to join(), you could also use the individual methods - leftJoin(), rightJoin() and outerJoin().

Fetching Specific Data (WHERE condition)

There are some methods available to create query conditions - where(), andWhere(), orWhere() and condition(). where() is an alias of andWhere(). You can use where(), andWhere() and orWhere() with array parameter or without parameter.

Simple condition

For array parameter, it accepts all conditional operators described in the previous section, for example,

$result = db_select('post', 'p')
    ->fields('p', array('id', 'title'))
    ->fields('u', array(array('name', 'author')))
    ->fields('c', array(array('name', 'categoryName')))
    ->join('user', 'u', 'p.user_id = u.id')
    ->leftJoin('category', 'c', 'p.cat_id = c.id')
    ->where(array(
        'c.id' => 1,
        'u.id' => 2
    ))
    ->getResult();

Without parameter, it initializes to create conditions by using condition():

$result = db_select('post', 'p')
    ->fields('p', array('id', 'title'))
    ->fields('u', array(array('name', 'author')))
    ->fields('c', array(array('name', 'categoryName')))
    ->join('user', 'u', 'p.user_id = u.id')
    ->leftJoin('category', 'c', 'p.cat_id = c.id')
    ->where()
        ->condition('c.id', 1)
        ->condition('u.id', 2)
    ->getResult();

The above two queries would generate the following same query:

SELECT `p`.`id`, `p`.`title`, `u`.`name` `author`, `c`.`name` `categoryName`
FROM `post` `p`
INNER JOIN `user` `u` ON `p`.`user_id` = `u`.`id`
LEFT JOIN `category` `c` ON `p`.`cat_id` = `c`.`id`
WHERE `c`.`id` = 1
AND `u`.`id` = 2

Complex condition using AND/OR

You can use the operator keys, $and and $or, for complex conditions. Here is an exmaple:

$result = db_select('post', 'p')
    ->fields('p')
    ->fields('u', array('username', array('name', 'author')))
    ->join('user', 'u', 'p.user_id = u.id')
    ->leftJoin('category', 'c', 'p.cat_id = c.id')
    ->where(array(
        'title like' => 'Sample project',
        '$or' => array(
            'p.id' => array(1, 2, 3),
            'u.id' => 1
        )
    ))
    ->orderBy('p.created', 'desc')
    ->limit(0, 20)
    ->getResult();

It generates the following query:

SELECT `p`.*, `u`.`username`, `u`.`name` `author`
FROM `post` `p`
INNER JOIN `user` `u` ON `p`.`user_id` = `u`.`id`
LEFT JOIN `category` `c` ON `p`.`cat_id` = `c`.`id`
WHERE `p`.`title` LIKE "%Sample project%"
AND ( `p`.`id` IN (1, 2, 3) OR `u`.`id` = 1 )
ORDER BY `p`.`created` DESC
LIMIT 0, 20

Complex nested condition using OR/AND/OR

The following is an example for complex nested conditions using AND/OR:

$result = db_select('post', 'p')
    ->fields('p')
    ->fields('u', array('username', array('name', 'author')))
    ->join('user', 'u', 'p.user_id = u.id')
    ->leftJoin('category', 'c', 'p.cat_id = c.id')
    ->orWhere(array(
        'p.title nlike' => 'Sample project',
        '$and' => array(
            'p.id' => array(1, 2, 3),
            'p.status <=' => 10,
            '$or' => array(
                'p.created >' => '2020-12-31',
                'p.deleted' => null
            )
        )
    ))
    ->orderBy('p.created', 'desc')
    ->limit(5)
    ->getResult()

It generates the following query:

SELECT `p`.*, `u`.`username`, `u`.`name` `author`
FROM `post` `p`
INNER JOIN `user` `u` ON `p`.`user_id` = `u`.`id`
LEFT JOIN `category` `c` ON `p`.`cat_id` = `c`.`id`
WHERE `p`.`title` NOT LIKE "%Sample project%"
OR (
    `p`.`id` IN (1, 2, 3)
    AND `p`.`status` <= 10
    AND ( `p`.`created` > "2020-12-31" OR `p`.`deleted` IS NULL )
)
ORDER BY `p`.`created` DESC
LIMIT 5

EXITS and NOT EXISTS

As of version 3.2, PHPLucidFrame added support for EXISTS and NOT EXISTS conditions. Here is an example.

$subquery = db_select('post_to_tag', 'pt')
    ->where()
    ->condition('post_id', db_raw('p.id'))
    ->condition('tag_id', 1)
    ->getReadySQL();

$qb = db_select('post', 'p')
    ->where()
    ->condition('deleted', null)
    ->exists($subquery);

$result = $qb->getResult();

It generates the following query:

SELECT `p`.* FROM `post` `p` WHERE `deleted` IS NULL
AND EXISTS (SELECT `pt`.* FROM `post_to_tag` `pt` WHERE `post_id` = `p`.`id` AND `tag_id` = 1)

You can also use notExists() for NOT EXISTS.

$subquery = db_select('post_to_tag', 'pt')
    ->where()
    ->condition('post_id', db_raw('p.id'))
    ->condition('tag_id', 1)
    ->getReadySQL();

$qb = db_select('post', 'p')
    ->where()
    ->condition('deleted', null)
    ->notExists($subquery);

$result = $qb->getResult();

orExists() and orNotExists() are also available to add multiple OR EXISTS or OR NOT EXISTS statements to your query.

Grouping Results

You can use groupBy() to write the GROUP BY portion of your query:

$result = db_select('post', 'p')
    ->groupBy('p.cat_id')
    ->getResult();

You can use multiple groupBy() calls. This generates the following query:

SELECT `p`.* FROM `post` `p`
GROUP BY `p`.`cat_id`

HAVING Condition on Group Result

There are some methods available to create having conditions - having(), andHaving(), orHaving(). having() is an alias of andHaving(). You can use them with array parameter of conditional operators described in the previous section, for example,

$result = db_select('post', 'p')
    ->groupBy('p.cat_id')
    ->having(array(
        'p.cat_id >' => 10,
        'p.status' => 1
    ))
    ->getResult();

This generates the following query:

SELECT `p`.* FROM `post` `p`
GROUP BY `p`.`cat_id`
HAVING `p`.`cat_id` > 10 AND `p`.`status` = 1

You can create OR condition on having using orHaving() like this:

$result = db_select('post', 'p')
    ->groupBy('p.cat_id')
    ->orHaving(array(
        'p.cat_id >' => 10,
        'p.status' => 1
    ))
    ->getResult();

Ordering Results

You can use orderBy('field', 'asc|desc'). The first parameter contains the name of the field you would like to order by. The second parameter lets you set the direction of the result. Options are asc and desc. Default to asc.:

$result = db_select('post', 'p')
    ->fields('p', array('id', 'title', 'created'))
    ->orderBy('p.title', 'asc)
    ->orderBy('p.created', 'desc')
    ->getResult();

_pr($result); // array of results

This generates the following query:

SELECT `p`.`id`, `p`.`title`, `p`.`created` FROM `post` `p`
ORDER BY `p`.`title` ASC, `p`.`created` DESC

As of version 3.5.0, you can also use orderRand, which will order the results randomly, to get a random record:

$result = db_select('post', 'p')
    ->fields('p', array('id', 'title', 'created'))
    ->orderRand()
    ->getSingleResult();

Counting Results

db_count() lets you determine the number of rows in a particular table.

$rowCount = db_count('post')
    ->where()->condition('deleted', null)
    ->fetch();

echo $rowCount;

This generates the following query:

SELECT COUNT(*) count FROM `post` WHERE deleted IS NULL

Limiting Results

limit() permits to limit the number of rows you would like returned by the query:

$result = db_select('post')
    ->limit(10)
    ->getResult();
_pr($result); // array of results

This generates the following query to return the first 10 records from the table post:

SELECT * FROM `post` LIMIT 10

You can also set offset to limit():

$result = db_select('post')
    ->limit(0, 10)
    ->getResult();

The following query will be executed:

SELECT * FROM `post` LIMIT 0, 10

Aggregates

There are aggregate functions available - db_min(), db_max(), db_sum(), db_avg().

MAX

Syntax: db_max($table, $field, $alias = null)

$max = db_max('post', 'view_count')->fetch();
// SELECT MAX(`view_count`) max FROM `post` `post`

MIN

Syntax: db_min($table, $field, $alias = null)

$min = db_min('post', 'view_count')->fetch();
// SELECT MIN(`view_count`) min FROM `post` `post`

SUM

Syntax: db_sum($table, $field, $alias = null)

$sum = db_sum('post', 'view_count')->fetch();
// SELECT SUM(`view_count`) sum FROM `post` `post`

AVG

Syntax: db_avg($table, $field, $alias = null)

$sum = db_avg('post', 'view_count')->fetch();
// SELECT SUM(`view_count`) avg FROM `post` `post`

Aggregate functions together

You can use aggregate function together like below:

$result = db_select('post', 'p')
    ->max('view_count', 'max')
    ->min('view_count', 'min')
    ->getResult();

This generates:

SELECT MAX(`view_count`) max, MIN(`view_count`) min FROM `post` `p`

Note