ClanCats Logo

Hydrahon

SQL Select Basics

The basics are where, group, order and limit conditions. For joins check out Joining data.

Keep in mind that i'm also aliasing the people table inside the $people variable for the following examples.

$people = $h->table('people');

Note: The displayed SQL query in the examples has no prepared statements. In other words the "?" have been replaced with the actual parameter.


Columns / fields

By default Hydrahon will simply select all fields using the * asterisks.

// SQL: select * from `people`
$people->select()->get();

You can pass an array of column / field names as argument to the select.

// SQL: select `name`, `age` from `people`
$people->select(['name', 'age'])->get();

Aliasing a field works by writing as.

// SQL: select `name`, `some_way_to_long_column_name` as `col` from `people`
$people->select(['name', 'some_way_to_long_column_name as col'])->get();

Note: that the column names are escaped for more infos about that read: parameter parsing and escaping.

You can overwrite the initial fields / columns any time using the field method.

// SQL: select `name`, `group` from `people`
$people->select()->fields(['name', 'group'])->get();

Adding fields

Also using the addField method you can add additional fields any time.

$query = $people->select('name');

if ($iNeedTheAge) {
    $query->addField('age');
}

Conditions / Aggregations / Raw

Because by default the columns will be quoted, you need to specify when you want to make some kind of raw operation or call a aggregation function.

// SQL: select `name`, deleted_at is not null as is_deleted from `people`
use ClanCats\Hydrahon\Query\Expression as Ex;

$people->select([
    'name',
    new Ex('deleted_at is not null as is_deleted')
])->get();

This also works using the addField method. There you can pass the alias name a second argument.

// SQL: select `name`, deleted_at is not null as `is_deleted` from `people`
use ClanCats\Hydrahon\Query\Expression as Ex;

$people->select('name')
    ->addField(new Ex('deleted_at is not null'), 'is_deleted')
    ->get();

Using the Func object you can still make use of Hydrahons escaping functionality.

// SQL: select count(`people`.`group_id`) from `people`
use ClanCats\Hydrahon\Query\Sql\Func as F;

$people->select(new F('count', 'people.group_id'))->get();

Where condition

A where equals condition is build like this:

// SQL: select * from `people` where `name` = James
$people->select()->where('name', 'James')->get(); 

If you need to use a different operator just pass it as the second argument.

// SQL: select * from `people` where `age` > 18
$people->select()->where('age', '>', '18')->get(); 

Passing an array as value will comma separate the values:

// SQL: select * from `people` where `city` in (Zürich, Bern, Basel)
$people->select()->where('city', 'in', ['Zürich', 'Bern', 'Basel'])->get(); 

You can also use the the whereIn method which will do exactly the same thing.

// SQL: select * from `people` where `city` in (Zürich, Bern, Basel)
$people->select()->whereIn('city', ['Zürich', 'Bern', 'Basel'])->get(); 

Warning: When passing an empty array to whereIn the condition will be simply skipped.

Create a where statement

->where('name', 'ladina')
->where('age', '>', 18)
->where('name', 'in', array('charles', 'john', 'jeffry'))

Method definition:

public function where($column, $param1 = null, $param2 = null, $type = 'and')

Arguments

Data type Variable name Comment
string $column The SQL column
mixed $param1 Operator or value depending if $param2 isset.
mixed $param2 The value if $param1 is an opartor.
string $type the where type ( and, or )

Returns

self


Chaining conditions

By default using where will add the conditions using the logical and operator:

// SQL: select * from `people` where `age` > 18 and `city` = Zürich
$people->select()
    ->where('age', '>', '18')
    ->where('city', 'Zürich')
    ->get(); 

use can use the methods orWhere and andWhere to specify which logical operator you want to use.

// SQL: select * from `people` where `city` = Zürich or `city` = Bern
$people->select()
    ->where('city', 'Zürich')
    ->orWhere('city', 'Bern')
    ->get(); 

Where NULL

Sometimes you need to know where something is nothing.

// SQL: select * from `people` where `deleted_at` is NULL
$people->select()->whereNull('deleted_at')->get();

Or reverse where it is not nothing.

// SQL: select * from `people` where `deleted_at` is not NULL
$people->select()->whereNotNull('deleted_at')->get();

And of course this also works with a or operator between the conditions.

// SQL: 
//   select * from `people` 
//   where `last_login` > 1502276478 
//   and `deleted_at` is NULL 
//   or `is_admin_since` is not NULL
$people->select()
    ->where('last_login', '>', time() - 86400)
    ->whereNull('deleted_at')
    ->orWhereNotNull('is_admin_since')
    ->get();

Grouped Where

Sometimes things get more complicated and you need to group some conditions together, we can do that by passing a function to the where method.

// SQL: 
//   select * from `people` 
//   where `is_admin` = 1 
//   or ( 
//      `is_active` = 1 
//      and `deleted_at` is NULL 
//      and `email_confirmed_at` is not NULL 
//   )
$people->select()
    ->where('is_admin', 1)
    ->orWhere(function($q) 
    {
        $q->where('is_active', 1);
        $q->whereNull('deleted_at');
        $q->whereNotNull('email_confirmed_at');
    })
    ->get();

There is no layer limit so you can but Closure into Closure.

// SQL: select * from `people` where `is_admin` = 1 or ( ( `is_active` = 1 and `is_moderator` = 1 ) or ( `is_active` = 1 and `deleted_at` is NULL and `email_confirmed_at` is not NULL ) )
$people->select()
    ->where('is_admin', 1)
    ->orWhere(function($q) 
    {
        $q->where(function($q) 
        {
            $q->where('is_active', 1);
            $q->where('is_moderator', 1);
        });
        $q->orWhere(function($q)
        {
            $q->where('is_active', 1);
            $q->whereNull('deleted_at');
            $q->whereNotNull('email_confirmed_at');
        });
    })
    ->get();

Reset Where

If you find yourself in a situation where you just need a clean start you can reset all where conditions any time:

$mySelectQuery->resetWheres();

Grouping / Group By

Adding a group by statements is fairly simple.

// SQL: select * from `people` group by `age`
$people->select()->groupBy('age')->get();

Or with multiple groups:

// SQL: select * from `people` group by `age`, `is_active`
$people->select()->groupBy(['age', 'is_active'])->get();

Add a group by statement to the current query

->groupBy('category')
->gorupBy(['category', 'price'])

Method definition:

public function groupBy($groupKeys)

Arguments

Data type Variable name Comment
array|string $keys

Returns

self


Ordering / Order By

Also nothing overwhelming here. By default the order direction is asc.

Order by single column:

// SQL: select * from `people` order by `created` asc
$people->select()->orderBy('created')->get();

Change the direction:

// SQL: select * from `people` order by `created` desc
$people->select()->orderBy('created', 'desc')->get();

Multiple columns

Sort on multiple fields:

// SQL: select * from `people` order by `created` asc, `firstname` asc
$people->select()->orderBy(['created', 'firstname'])->get();

Sort on multiple fields with different directions:

// SQL: select * from `people` order by `lastname` asc, `firstname` asc, `score` desc
$people->select()->orderBy([
    'lastname' => 'asc',
    'firstname' => 'asc',
    'score' => 'desc',
])->get();

Add an order by statement to the current query

->orderBy('created_at')
->orderBy('modified_at', 'desc')
// multiple order statements
->orderBy(['firstname', 'lastname'], 'desc')
// muliple order statements with diffrent directions
->orderBy(['firstname' => 'asc', 'lastname' => 'desc'])

Method definition:

public function orderBy($columns, $direction = 'asc')

Arguments

Data type Variable name Comment
array|string $cols
string $order

Returns

self


Limit / Offset

Because fetching trillions of records from the database makes your app probably crash we need to be able to limit queries.


Limit

To set the limit use the method with that exact name:

// SQL: select * from `people` limit 0, 10
$people->select()->limit(10)->get();

Using the exact same method you can also set the offset. So when two arguments are given the second one acts as limit and the first one as offset. This might seem confusing but I wanted to stay as close as possible to sql.

// with offset 100
// SQL: select * from `people` limit 100, 10
$people->select()->limit(100, 10)->get();

Set the query limit

// limit(<limit>)
->limit(20)
// limit(<offset>, <limit>)
->limit(60, 20)

Method definition:

public function limit($limit, $limit2 = null)

Arguments

Data type Variable name Comment
int $limit
int $limit2

Returns

self


Offset

If you like things a little bit more expressive you can also use the offset method.

// SQL: select * from `people` limit 100, 10
$people->select()->limit(10)->offset(100)->get();

Set the queries current offset

Method definition:

public function offset($offset)

Arguments

Data type Variable name Comment
int $offset

Returns

self


Page

The page method is just a helper that also sets a limit and offset. The default page size is 25.

// SQL: select * from `people` limit 0, 25
$people->select()->page(0)->get();

// SQL: select * from `people` limit 125, 25
$people->select()->page(5)->get();

// SQL: select * from `people` limit 250, 50
$people->select()->page(5, 50)->get();

Note: Pages always start at 0!

Create an query limit based on a page and a page size

Method definition:

public function page($page, $size = 25)

Arguments

Data type Variable name Comment
int $page
int $size

Returns

self