SELECT Query Builder
Introduction
Select is most used operation and most complex query builder because of all its capabilities. If you've ever needed to
use select
queries that are more complex from just using one table in database, then you'll know that most used
parts of select
query are:
- fields you want to select, either from one or more tables, using aliases or aggregation functions
- from [table name]
- joins, next to from
- where
- group by
- having
- order by
- limit
We'll cover it all in next sections. Check the simplest SELECT
statement example:
$select = new Select();
$select->from('user');
Or:
$select = Db::select('user');
Or if you have User
model:
$select = User::select();
All three examples from above will prepare the following query:
SELECT * FROM user
Difference between select
and all other query builders is how you need to run them. When you want to run insert,
update or delete query statement, you'll probably just call exec()
method and you're good. If you call
exec()
method on select
, query will be executed on database, but you won't get anything back like you would expect to.
To execute select
query and get something back, you have few methods to choose from.
Retrieving Records
Retrieving All Records
Db::select('user')->fetchAll();
Db::select('user')->fetchAllObj();
Db::select('user')->fetchAllObj(User::class);
Explanations:
fetchAll
will return array of results - it's using PDO::FETCH_ASSOCfetchAllObj
will return array ofstdClass
instances - it's using PDO::FETCH_OBJ- If you pass class name to
fetchAllObj
method, then framework will fetch results as array and it'll try to create class instance of every record from result set by passing array of values to class's constructor. Here, if you passUser::class
, framework will return you this:
$result = [];
foreach (Db::select('user')->fetchAll() as $record) {
$result[] = new User($record);
}
This is useful and wanted behaviour when you're working with database models, but it'll work with any class that accepts array of data in constructor.
Retrieving First Record
Db::select('user')->fetchFirst();
Db::select('user')->fetchFirstObj();
Db::select('user')->fetchFirstObj(User::class);
These methods are working the same way as methods for fetching all records, except that it resets SQL limit
and sets the limit
to fetch first record only, so you'll get back array
, stdClass
or instance of User
. If there's nothing fetched from
database, you'll get null
back.
Defining Fields
You'll often want to define which columns you want to fetch from table. Use field
method for that:
Db::select('user')
->field('id')
->field('first_name')
->field('last_name', 'surname');
The query above looks like this:
SELECT
id,
first_name,
last_name as surname
FROM
user
If you want to use aggregation functions, simply put it like this:
Db::select('user')
->field('gender')
->field('COUNT(*)', 'count_per_gender')
->groupBy('gender');
And the query is:
SELECT
gender,
COUNT(*) as count_per_gender
FROM
user
GROUP BY gender
Defining FROM And Table Aliases
You have already seen simple from
statement:
$select = new Select();
$select->from('user');
As you already know, it's possible to have more than one table from where you're selecting your records. Let's say that
each user belongs to user_group
and each record in user
table has column group_id
which is foreign key to user_group.id
:
$select = new Select();
$select->from('user');
$select->from('user_group');
$select->where('user.group_id', Db::expr('user_group.id')); // using Expr because value on right side is not primitive value
This example will generate:
SELECT
*
FROM
user,
user_group
WHERE
(user.group_id = user_group.id)
If you want to select only columns from user
table? One way would be to add:
$select->field('user.*');
Or alternative, you can do it when calling 1st from()
method:
$select->from('user', null, '*');
If you want to select specific columns from 1st table and specific columns from 2nd table:
$select = new Select();
$select->from('user', null, ['first_name', 'last_name']);
$select->from('user_group', null, ['name']);
$select->where('user.group_id', Db::expr('user_group.id'));
You'll get:
SELECT
user.first_name,
user.last_name,
user_group.name
FROM
user,
user_group
WHERE
(user.group_id = user_group.id)
Using Aliases In FROM
We're recommend using SQL table aliases:
$select = new Select();
$select->from('user', 'u', ['first_name', 'last_name']);
$select->from('user_group', 'ug', ['name']);
$select->where('u.group_id', Db::expr('ug.id'));
Produces:
SELECT
u.first_name,
u.last_name,
ug.name
FROM
user as u,
user_group as ug
WHERE
(u.group_id = ug.id)
Using Joins
There are four types of join that framework supports:
- inner join or just join -> methods
innerJoin()
andjoin()
- left join -> method
leftJoin()
- right join -> method
rightJoin()
- full join -> method
fullJoin()
Framework supports building simple and some of the complex join statements. All join methods from above accepts the same
parameters, so to simplify this documentation, all our examples will be based on INNER JOIN
.
Simple INNER JOIN Statement
The simplest INNER JOIN
statement whould be:
$select = new Select('user');
$select->innerJoin('user_group', 'user_group.id', '=', 'user.group_id');
You'll get:
SELECT
*
FROM
user
INNER JOIN user_group ON user_group.id = user.group_id
Using Table Aliases In Join Statements
$select = new Select('user', 'u');
$select->innerJoin('user_group ug', 'ug.id', '=', 'u.group_id');
You'll get:
SELECT
*
FROM
user as u
INNER JOIN user_group ug ON ug.id = u.group_id
Joining More Than One Table
To join more than one table, simply call any join method more than one time:
$select = new Select('user', 'u');
$select->innerJoin('user_group ug', 'ug.id', '=', 'u.group_id');
$select->innerJoin('user_email ue', 'ue.user_id', '=', 'u.id');
You'll get:
SELECT
*
FROM
user as u
INNER JOIN user_group ug ON ug.id = u.group_id
INNER JOIN user_email ue ON ue.user_id = u.id
Table Joins With More Than One Relation
Sometimes, you'd want join two tables by using more relations. To do that, you can pass array of join statements:
$select = new Select('user', 'u');
$select->innerJoin('user_group ug', [
['ug.id', 'u.group_id'],
['ug.first_name', 'u.name'],
]);
// to use different operator, simply put it in between, like:
// ['ug.first_name', '!=', 'u.name'],
You'll get:
SELECT
*
FROM
user as u
INNER JOIN user_group ug ON ug.id = u.group_id AND ug.first_name = u.name
All joins from array are joined by AND
. If you want to put OR
, simply put it on first position, but then you have to
define =
for operator:
$select = new Select('user', 'u');
$select->innerJoin('user_group ug', [
['ug.id', 'u.group_id'],
['OR', 'ug.first_name', '=', 'u.name'],
]);
You'll get:
SELECT
*
FROM
user as u
INNER JOIN user_group ug ON ug.id = u.group_id OR ug.first_name = u.name
Note that join methods always assume that you'll pass another table's column name for the value on right.
WHERE statements
Check the detailed documentation here.
Using GROUP BY
To create aggregation query, you'll need to use GROUP BY statements every. Method groupBy()
on Select
instance accepts
just one parameter. You can pass here anything you want that's meaningful for your database. Here are few examples:
$select->groupBy('group_id');
$select->groupBy('u.group_id');
$select->groupBy('1');
$select->groupBy('COUNT(*)');
You can add as many GROUP BY statements as you want.
Using HAVING
More complex queries might have HAVING statements. To define HAVING statement on instance of Select
, use
having()
or orHaving()
methods. If you call having()
method multiple times, it'll be linked by AND
, so use
orHaving()
method to link it with OR
. Here are few examples:
$select->having('group_id', '>', 10);
$select->having('u.group_id', '=', 10);
Using ORDER BY
To sort you results while fetching data, use orderBy()
method. You can call orderBy()
method multiple times to sort
your results using multiple columns. Here are few examples:
$select->orderBy('first_name'); // will sort it asceding
$select->orderBy('last_name', 'asc');
$select->orderBy('u.age', 'desc');
Using LIMIT
To limit the number of results from Select
, use limit()
method. First parameter is the record from which you want to
start fetching results. To fetch all records starting from first result, put 0
as first parameter. 2nd parameter is number
on how many records you want to fetch. So if you pass limit(0, 10)
, you'll get first 10 records.
$select->limit(0, 10);
Calling this method multiple times will override any previously set limit settings from before.
In addition to limit()
, you can you use alternate page()
method. First parameter in page()
method is page number, second
parameter is the number of records per page:
$select->page(2, 10);
Example from above is the same as $select->limit(10, 10)
- the 2nd page from "10 results per page" result sets.