Extended selections

  1. Introduction
  2. Adding columns
  3. Adding aggregates
  4. Adding functions

Introduction

Let’s assume that you want to select from a table, the minimum, the maximum and the average value of a column. Of course, to achieve this, you could simply use the aggregates functions but, unfortunately, this means to make three separate queries to the database. To overcome this issue Opis Database provides developers a mechanism that can be used to define the columns or other properties that will be included in the final result set.

In order to use this mechanism you must pass as an argument to the select method an anonymous callback function, which in turn will receive as an argument an object that must be used to specify which columns should be added to the result set.

$result = $db->from('users')
             ->select(function($include){
                //code here
             })
             ->all();

Adding columns

Adding columns to the result set is done using the column method.

$result = $db->from('users')
             ->select(function($include){
                $include->column('name');
             })
             ->all();
SELECT `name` FROM `users`

If you want to provide an alias name for the column, you must pass that alias as the second argument to the column method.



$result = $db->from('users')
             ->select(function($include){
                $include->column('name', 'n');
             })
             ->all();
SELECT `name` AS `n` FROM `users`

Of course, you can add multiple columns, by calling the column method multiple times, once for each column.

$result = $db->from('users')
             ->select(function($include){
                $include->column('name')
                        ->column('age');
             })
             ->all();
SELECT `name`, `age` FROM `users`

Adding multiple columns at once can be achieved by using the columns method and passing as an argument to the method an array of column names.

$result = $db->from('users')
             ->select(function($include){
                $include->columns(['name', 'age']);
             })
             ->all();
SELECT `name`, `age` FROM `users`

Aliasing column names is done by passing as an argument to the columns method a key => value mapped array, where the key represents the column name and the value represents the alias name. If you want that a column to not being aliased, all you have to do is to omit the key for that column, providing only the value.

$result = $db->from('users')
             ->select(function($include){
                $include->columns(['user' => 'u', 'age']);
             })
             ->all();
SELECT `user` AS `u`, `age` FROM `users`

Adding aggregates

Adding aggregates is done by using the count, max, min, avg, and sum methods. These methods can be used in conjunction with column and columns methods.

Counting

Counting records is done using the count method.

$result = $db->from('users')
             ->select(function($include){
                $include->count();
             })
             ->all();
SELECT COUNT(*) FROM `users`

Counting all values(NULL values will not be counted) of a column is done by passing the column’s name as an argument to the count method.

$result = $db->from('users')
             ->select(function($include){
                $include->count('email');
             })
             ->all();
SELECT COUNT(`email`) FROM `users`

Adding an alias for the value returned by this aggregate function is done by passing the alias name as the second argument to the count method.

$result = $db->from('users')
             ->select(function($include){
                $include->count('email', 'email_count');
             })
             ->all();
SELECT COUNT(`email`) AS `email_count` FROM `users`

If you want to count only the distinct values of a column, you must pass true as the third argument to the count method.



$result = $db->from('users')
             ->select(function($include){
                $include->count('email', 'email_count', true);
             })
             ->all();
SELECT COUNT(DISTINCT `email`) AS `email_count` FROM `users`

Largest value

Finding the largest value of a column is done using the max method.

$result = $db->from('users')
             ->select(function($include){
                $include->max('age');
             })
             ->all();
SELECT MAX(`age`) FROM `users`

Adding an alias for the value returned by this aggregate function is done by passing the alias name as the second argument to the max method.

$result = $db->from('users')
             ->select(function($include){
                $include->max('age', 'max_age');
             })
             ->all();
SELECT MAX(`age`) AS `max_age` FROM `users`

Smallest value

Finding the smallest value of a column is done using the min method.

$result = $db->from('users')
             ->select(function($include){
                $include->min('age');
             })
             ->all();
SELECT MIN(`age`) FROM `users`

Adding an alias for the value returned by this aggregate function is done by passing the alias name as the second argument to the min method.

$result = $db->from('users')
             ->select(function($include){
                $include->min('age', 'min_age');
             })
             ->all();
SELECT MIN(`age`) AS `min_age` FROM `users`

Summing

Findind the sum of a column is done using the sum method.

$result = $db->from('users')
             ->select(function($include){
                $include->sum('wallet');
             })
             ->all();
SELECT SUM(`wallet`) FROM `users`

Adding an alias for the value returned by this aggregate function is done by passing the alias name as the second argument to the sum method.

$result = $db->from('users')
             ->select(function($include){
                $include->sum('wallet', 'total_amount');
             })
             ->all();
SELECT SUM(`wallet`) AS `total_amount` FROM `users`

If you want to sum only the distinct values of a column, you must pass true as the third argument to the sum method.

$result = $db->from('users')
             ->select(function($include){
                $include->sum('wallet', 'total_amount', true);
             })
             ->all();
SELECT SUM(DISTINCT `wallet`) AS `total_amount` FROM `users`

Average

Finding the average value of a column is done using the avg method.

$result = $db->from('users')
             ->select(function($include){
                $include->avg('wallet');
             })
             ->all();
SELECT AVG(`wallet`) FROM `users`

Adding an alias for the value returned by this aggregate function is done by passing the alias name as the second argument to the avg method.

$result = $db->from('users')
             ->select(function($include){
                $include->sum('wallet', 'average_amount');
             })
             ->all();
SELECT SUM(`wallet`) AS `average_amount` FROM `users`

Adding functions

Adding functions is done using one of the following methods: ucase, lcase, mid, len, round, format and now.

Upper case

Selecting the upper case value of a column is done by using the ucase method.

$result = $db->from('users')
             ->select(function($include){
                $include->ucase('name');
             })
             ->all();
SELECT UCASE(`name`) FROM `users`

You can also alias the returned value by passing an alias name as the second argument of the method.

$result = $db->from('users')
             ->select(function($include){
                $include->ucase('ucname');
             })
             ->all();
SELECT UCASE(`name`) AS `ucname` FROM `users` 

Lower case

Selecting the lower case value of a column is done by using the lcase method.

$result = $db->from('users')
             ->select(function($include){
                $include->lcase('name');
             })
             ->all();
SELECT LCASE(`name`) FROM `users`

You can also alias the returned value by passing an alias name as the second argument of the method.

$result = $db->from('users')
             ->select(function($include){
                $include->lcase('lcname');
             })
             ->all();
SELECT LCASE(`name`) AS `lcname` FROM `users`

Substring

Extracting a substring from a column is done by using the mid method. The method takes as arguments the column’s name and the starting position from which the substring will be extracted. The substring counting starts from 1.

$result = $db->from('users')
             ->select(function($include){
                $include->mid('name', 3);
             })
             ->all();
SELECT MID(`name`, 3) FROM `users`

You can also alias the returned value by passing an alias name as the third argument of the method.

$result = $db->from('users')
             ->select(function($include){
                $include->mid('name', 3, 'sname');
             })
             ->all();
SELECT MID(`name`, 3) AS `sname` FROM `users`

You can limit the length of the substring by passing a fourth argument to the mid method.

$result = $db->from('users')
             ->select(function($include){
                $include->mid('name', 3, null, 2);
             })
             ->all();
SELECT MID(`name`, 3, 2) FROM `users`

Length

Adding the length of a column is done by using the len method.

$result = $db->from('users')
             ->select(function($include){
                $include->len('name');
             })
             ->all();
SELECT LENGTH(`name`) FROM `users`

You can also alias the returned value by passing an alias name as the second argument of the method.

$result = $db->from('users')
             ->select(function($include){
                $include->len('lname');
             })
             ->all();
SELECT LENGTH(`name`) AS `lname` FROM `users`

Formatting

Formatting the value of a column to a number with a specified number of decimals is done by using the format method.

$result = $db->from('users')
             ->select(function($include){
                $include->format('wallet', 4);
             })
             ->all();
SELECT FORMAT(`wallet`, 4) FROM `users`

The same effect can be obtained by using the round method.

$result = $db->from('users')
             ->select(function($include){
                $include->round('wallet', 4);
             })
             ->all();
SELECT FORMAT(`wallet`, 4) FROM `users`

You can also alias the returned value by passing an alias name as the second argument of the method.

$result = $db->from('users')
             ->select(function($include){
                $include->format('wallet', 4, 'fwallet');
             })
             ->all();
SELECT FORMAT(`wallet`, 4) AS `fwallet` FROM `users`