ClanCats Logo

Hydrahon

SQL Select Runner Methods

One important thing to know here is that there are multiple so-called "runner methods". Think of them as helpers for common queries. These methods can modify your query, execute it and can do special operations with the returned result.

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


Fetching


Execute

The absolute base runner method is execute. It's what all other runner methods are based on.

It's an alias of executeResultFetcher, this means the method just forwards the plain data that you return inside your ClanCats\Hydrahon\Builder instance callback.

$h->table('people')->select()->execute();

Get

The default runner method is the get method, it handles most of the built-in result modifications.

As an example the handling of an expected single result vs. collections.

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

$people->select()->where('name', 'Trevor')->execute(); // [[id: 1, name: 'Trevor']]
$people->select()->where('name', 'Trevor')->limit(1)->execute(); // [[id: 1, name: 'Trevor']]

Will return a collection or in other words an array of arrays.

Using the get method Hydrahon knows that you are expecting a single result by setting the limit to 1.

$people->select()->where('name', 'Trevor')->get(); // [[id: 1, name: 'Trevor']]
$people->select()->where('name', 'Trevor')->limit(1)->get(); // [id: 1, name: 'Trevor']

Executes the executeResultFetcher callback and handles the results.

Method definition:

public function get()

Returns

mixed The fetched result.


One

The method one hopefully speaks like a lot of methods for itself. It will add limit 1 to your query and return a single result.

$jeffry = $people->select()->where('name', 'jeffry')->one(); // [id: 2, name: 'jeffry']

Sets the limit to 1, executes and returns the first result using get.

Method definition:

public function one()

Returns

mixed The single result.


First & Last

Selects the first / last result ordered by the given key (default is id).

$firstPerson = $people->select()->first(); 
$lastPerson = $people->select()->last();

You can set on what key the first / last item should be selected:

$youngest = $people->select()->first('age');
$oldest = $people->select()->last('age');

Get the first result orderd by the given key.

Method definition:

public function first($key = 'id')

Arguments

Data type Variable name Comment
string $key By what should the first item be selected? Default is: 'id'

Returns

mixed The first result.


Find

Selects one item with the given value. This translates to a simple where with limit 1.

// SQL: select * from `questions` where `id` = 42 limit 0, 1
$theAnswer = $h->table('questions')->select()->find(42);

You can also set the key.

// SQL: select * from `people` where `name` = John limit 0, 1
$john = $people->select()->find('John', 'name');

Find something, means select one item by key

Method definition:

public function find($id, $key = 'id')

Arguments

Data type Variable name Comment
int $id
string $key

Returns

mixed


Column

Select one specific value.

// SQL: select `age` from `people` where `name` = Ray limit 0, 1
$age = $people->select()->where('name', 'Ray')->column('age'); // returns 26

Just get a single value from the result

Method definition:

public function column($column)

Arguments

Data type Variable name Comment
string $column The name of the column.

Returns

mixed The columns value


Aggregators


Count

Selects using the MySQL count function and returns the number of results.

// SQL: select count(*) from `people` limit 0, 1
$peopleCount = $people->select()->count();

By default the wildcard * is used but you can pass a field name:

// SQL: select count(`deleted_at`) from `people` limit 0, 1
$deletedCount = $people->select()->count('deleted_at');

Just return the number of results

Method definition:

public function count($field = null)

Arguments

Data type Variable name Comment
string $field

Returns

int


Sum

Selects using the MySQL sum function and returns the result.

// SQL: select sum(`number_of_visits`) from `people` limit 0, 1
$totalVisits = $people->select()->sum('number_of_visits');

Helper for the SQL sum aggregation.

Method definition:

public function sum($field)

Arguments

Data type Variable name Comment
string $field

Returns

int


Min

Selects using the MySQL min function and returns the result.

// SQL: select min(`score`) from `game`.`ranking` limit 0, 1
$lowestScore = $h->table('game.ranking')->select()->min('score');

Helper for the SQL min aggregation.

Method definition:

public function min($field)

Arguments

Data type Variable name Comment
string $field

Returns

int


Max

Selects using the MySQL max function and returns the result.

// SQL: select max(`score`) from `game`.`ranking` limit 0, 1
$highestScore = $h->table('game.ranking')->select()->max('score');

Helper for the SQL max aggregation.

Method definition:

public function max($field)

Arguments

Data type Variable name Comment
string $field

Returns

int


Avarage

Selects using the MySQL avg function and returns the result.

// SQL: select avg(`age`) from `people` limit 0, 1
$avarageAge = $people->select()->avg('age');

Helper for the SQL avarage aggregation.

Method definition:

public function avg($field)

Arguments

Data type Variable name Comment
string $field

Returns

int


Exists

Returns a bool value if anything under the queries conditions exists.

// SQL: select exists(select * from `people` where `age` > 89) as `exists
$hasOldPeople = $people->select()->where('age', '>', '89')->exists();

Do any results of this query exist?

Method definition:

public function exists()

Returns

bool