• Navigation
• Fetching
• Execute
• Get
• One
• First & Last
• Find
• Column
• Aggregators
• Count
• Sum
• Min
• Max
• Average
• Exists

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.

#  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']

public function get()

#  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']

public function one()

#  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');

public function first($key = 'id')

#  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');

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

#  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

public function column($column)

#  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');

public function count($field = null)

#  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');

public function sum($field)

#  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');

public function min($field)

#  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');

public function max($field)

#  Average

Selects using the MySQL avg function and returns the result.

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

public function avg($field)

#  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();

public function exists()