Query Methods

table()

Also includes from()

This not only allows for the defining of which table(s) will be the start of our query but also the start of a new query

from()

If you wish to add additional tables to the query, please use from() which follows the same parameters as table()

/**
 * @param string|Raw  $table  Each table name used  
 * @return QueryBuildHandler
 */
public function table(...$table): self

Usage

// Assign multiple tables in a single call
$builder->table('foo', 'bar', 'baz')->get();

// As multiple calls.
$builder->table('foo')
    ->from('bar')
    ->from('baz', 'daz')
    ->get();

// These would both result in 
"SELECT * FROM foo, bar, baz, 'daz'"

// With table alias
$builder->table('foo AS f')->get();
"SELECT * FROM foo AS f"

// This also works with defined prefix in connection.
# with 'p_' as a prefix
$builder->table('foo AS f')->get();
"SELECT * FROM p_foo AS f"

select()

Also includes selectDistinct()

Used to specify the columns of data to return.

Defaults to '*' if not set

/**
 * @param string[]|string|Raw  $field  Fields to be selected. 
 * @return QueryBuildHandler
 */
public function select(...$field): self

Usage

// Single column
QB::table( 'my_table' )->select( 'id' );
"SELECT id FROM my_table"

// Single Column from within JSON Document
QB::table('foo')->select(['column->someObj->a' => 'jsonAlias']);

// Multiple
QB::from('foo')->select( 'mytable.myfield1', 'mytable.myfield2', 'another_table.myfield3' );
"SELECT mytable.myfield1, mytable.myfield2, another_table.myfield3 FROM foo"

// With Alias
QB::table( 'my_table' )->select( ['userID' => 'id', 'parentUserID' => 'parent'] );
"SELECT userID AS id, parentUserID AS parent from my_table"

Using select method multiple times select( 'a' )->select( 'b' ) will also select a and b . Can be useful if you want to do conditional selects (within a PHP if ).

$builder = QB::table('foo')->select('colA');
if(thing()){
    $builder->select('colB');
}

related selectJson() docs

selectDistinct()

/**
 * @param string[]|string|Raw  $field  Fields to be selected. 
 * @return QueryBuildHandler
 */
public function selectDistinct(...$field): self

QB::table('foo')->selectDistinct( 'mytable.myfield1', 'mytable.myfield2');

Adds DISTINCT to the select query SELECT DISTINCT mytable.myfield1, mytable.myfield2

get()

Once you have built your queries conditions (see Where & Join), you can retrieve your results.

/**
 * @return array<mixed,mixed>|null
 */
public function get()

Usage

$results = QB::table('my_table')->where('name', '=', 'Sana')->get();
// You can loop through it like:
foreach ($results as $row) {
    echo $row->name;
}

first()

/**
 * @return array<mixed,mixed>|object|null
 */
public function first()

Usage

$query = QB::table('my_table')->where('name', '=', 'Sana');
$row = $query->first();

Returns the first row, or null if there is no record. Using this method you can also make sure if a record exists. Access these like echo $row->name .

find()

Acts a shortcut for a simple WHERE condition, with a single result. By default assumes 'id' field, but can be set to any

/**
 * @return array<mixed,mixed>|object|null
 */
public function find($value, $fieldName = 'id')

Usage

$user = QB::table('users')->find(12);
"SELECT * FROM users WHERE id = 12 LIMIT 1"

$user = QB::table('users')->find(12, 'userId');
"SELECT * FROM users WHERE userId = 12 LIMIT 1"

findAll()

Same as find() but not limited to the first row.

/**
 * @return array<mixed,mixed>|object|null
 */
public function findAll($value, $fieldName = 'id')

Usage

$user = QB::table('users')->findAll(12);
"SELECT * FROM users WHERE id = 12"

$user = QB::table('users')->findAll(12, 'userId');
"SELECT * FROM users WHERE userId = 12"

findOrFail()

A version of find() that will throw an exception if no result found.

count()

This will return a count of the number of row returned by the query.

/**
 * @param string $field
 * @return int
 */
public function count(string $field = '*'): int

If you pass a different field, it must either be a field that is defined in the query

Data

id	name	team	scored	played
1	Jon	Team A	12	2
2	Sam	Team A	34	5
3	Dave	Team B	12	7
4	Mike	Team B	23	12
5	James	Team A	11	15
4	Dexter	Team B	9	5

Usage

$playerCount = $query = QB::table('players')
    ->where('team', '=', 'Team A')
    ->count();
// 3

average()

Would return the average over the defined column. Column selected must be present in the query,if not using the '*' wildcard for select().

/**
 * @param string $field
 * @return float
 */
public function average(string $field): float

Usage

$avg = QB::table('players')
    ->select('team','scored')
    ->where('team', 'Team B')
    ->average('scored');
// 12 + 23 + 9 = 44 | 44 / 3 = 14.66666

min()

Returns the lowest value from the defined column. Column selected must be present in the query,if not using the '*' wildcard for select().

/**
 * @param string $field
 * @return float
 */
public function min(string $field): float

Usage

$min = QB::table('players')
    ->where('played', '>', 6)
    ->min('scored');
// 3:12, 4:23, 5:11 = 11

max()

Returns the highest value from the defined column. Column selected must be present in the query,if not using the '*' wildcard for select().

/**
 * @param string $field
 * @return float
 */
public function max(string $field): float

Usage

$min = QB::table('players')
    ->where('scored', '<', 20)
    ->max('played');
// 2,7,15,5 = 15

sum()

Returns the total value from the defined column. Column selected must be present in the query,if not using the '*' wildcard for select().

/**
 * @param string $field
 * @return float
 */
public function sum(string $field): float

Usage

$min = QB::table('players')
    ->where('team',  'Team A')
    ->sum('scored');
// 12 + 34 + 11 = 57

offset()

@TODO

limit()

@TODO

orderBy()

It is possible to order the results by single or multiple columns in either direction.

Single Column

// ASC Direction by default.
QB::table('my_table')
    ->orderBy('created_at');

// You can change direction using.
QB::table('my_table')
    ->orderBy(['created_at' => 'DESC'])

// If you wish to use a Raw expression, you can set the direction using the
// default direction.
QB::table('my_table')
    ->orderBy(new Raw('column = %s',['foo']), 'DESC')
// ORDER BY column = 'foo' DESC

Multiple Columns

// ASC Direction by default.
$results = QB::table('my_table')
    ->orderBy(['points','goals']);
    
// This allows adding additional sorting rules conditionally.
$query = QB::table('my_table')->orderBy('points', 'DESC');
if( 1==1 ){
    $query->orderBy('goals', 'DESC');
}
$results = $query->get();

// Order by JSON
QB::table('my_table')
    ->orderBy(['jsonData->post_data->upvotes' => 'DESC'])
    ->orderBy(['jsonData->post_data->downvotes' => 'ASC'])

Additional orderByJson() docs can be found here

group()

@TODO

having()

@TODO

where()

It is possible to define a where condition as part of a query. Basic syntax is (fieldname, operator, value), if you give two parameters then = operator is assumed. So where('name', 'Glynn') and where('name', '=', 'Glynn') is the same.

/**
 * @param string|Raw|Closure(NestedCriteria):void        $key       The field key to use to match
 * @param string|mixed|null $operator  Can be used as value, if 3rd arg not passed
 * @param mixed $value
 * @return static
 */
public function where($key, $operator, $value ): self

Usage

// Simple where without operator
QB::table('players')->where('team',  'Team A')->get();
// SELECT * FROM players WHERE team = 'Team A';

// Simple where with operator
QB::table('players')->where('team', '<>', 'Team B')->get();
// SELECT * FROM players WHERE team <> 'Team B';

// Simple where using JSON arrow selectors
QB::table('players')->where('column->keyA->keyB', 'foo')->get();

related whereJson() docs

When adding more than 1 where clause, the default joiner used is AND

// Simple where without operator
QB::table('players')
    ->where('team',  'Team A')
    ->where('position', '<>', 'goalkeeper')
    ->get();
// SELECT * FROM players WHERE team = 'Team A' AND position <> goalkeeper;

The first WHERE conditions joiner is ignored in final query.

orWhere()

The same as where() but uses OR as it joiner.

/**
 * @param string|Raw|Closure(NestedCriteria):void        $key       The field key to use to match
 * @param string|mixed|null $operator  Can be used as value, if 3rd arg not passed
 * @param mixed $value
 * @return static
 */
public function orWhere($key, $operator, $value ): self

Usage

// As mentioned above, the first where statements joiner is ignored, so use where first
QB::table('players')
    ->where('team',  'Team A')
    ->orWhere('team',  'Team B')
    ->get();
// SELECT * FROM players WHERE team = 'Team A' OR team = 'Team B';

// Remember to use BINDINGS if the value comes from any remote source (REST, Database or User Input)

QB::table('players')
    ->where('team',  'Team A')
    ->orWhere('team',  Binding::asString($userInput)) // Assuming $userInput = 'Team C' 
    ->get();
//SELECT * FROM players WHERE team = 'Team A' OR team = 'Team C';

JSON arrow selectors are allows in keys, we also have JSON Helper methods whereJson() & orWhereJson()

Grouped Where

Sometimes it is needed to make a grouped where condition. To do this, you can pass a closure into the first parameter to handle this.

QB::table('foo')
    ->where('key', '=', 'value')
    ->where(
        /**
         * @param \Closure( NestedCriteria $builder ): void
         * @return void
         */
        function( NestedCriteria $builder ): void{
            $query->where('key2', '<>', 'value2');
            $query->orWhereNot('key3', '=', 'value3');
        }
    )
    ->get();
// Would result in the following query
"SELECT * FROM foo WHERE key = 'value' AND (key2 <> 'value2' OR NOT key3 = 'value3')"

Can also be used with orWhere() whereNot() and orWhereNot(), even supports JSON selectors.

whereNot()

Helps to apply WHERE NOT to the query.

/**
 * @param string|Raw|Closure(NestedCriteria):void        $key       The field key to use to match
 * @param string|mixed|null $operator  Can be used as value, if 3rd arg not passed
 * @param mixed $value
 * @return static
 */
public function whereNot($key, $operator, $value ): self

Usage

// Simple where without operator
QB::table('players')->whereNot('team',  'Team A')->get();
// SELECT * FROM players WHERE NOT team = 'Team A';

// Simple where with operator
QB::table('players')->whereNot('team', '<>', 'Team B')->get();
// SELECT * FROM players WHERE NOT team <> 'Team B';

// Simple where using JSON arrow selectors
QB::table('players')->where('column->keyA->keyB', 'foo')->get();

related whereNotJson() docs

orWhereNot()

The same as whereNot() but uses OR as it joiner.

/**
 * @param string|Raw|Closure(NestedCriteria):void        $key       The field key to use to match
 * @param string|mixed|null $operator  Can be used as value, if 3rd arg not passed
 * @param mixed $value
 * @return static
 */
public function orWhereNot($key, $operator, $value ): self

Usage

// As mentioned above, the first where statements joiner is ignored, so use where first
QB::table('players')
    ->whereNot('team',  'Team A')
    ->orWhereNot('team',  'Team B')
    ->get();
// SELECT * FROM players WHERE NOT team = 'Team A' OR NOT team = 'Team B';

// Remember to use BINDINGS if the value comes from any remote source (REST, Database or User Input)

QB::table('players')
    ->whereNot('team',  'Team A')
    ->orWhereNot('team',  Binding::asString($userInput)) // Assuming $userInput = 'Team C' 
    ->get();
//SELECT * FROM players WHERE NOT team = 'Team A' OR NOT team = 'Team C';

whereNull()

Checks if a columns value is null

/**
 * @param string|Raw|Closure(NestedCriteria):void  $key  The field key to use to match
 * @return static
 */
public function whereNull($key): self

Usage

// Will filter the table where teams value is NOT NULL
QB::table('players')
    ->whereNull('team')
    ->get();
// SELECT * FROM players WHERE team IS NULL;

// Can also be used with JSON array selectors.
QB::table('players')
    ->whereNull('column->keyA->keyB')
    ->get();

orWhereNull()

The same as whereNull() but uses OR as it joiner.

Checks if a columns value is null

/**
 * @param string|Raw|Closure(NestedCriteria):void  $key  The field key to use to match
 * @return static
 */
public function orWhereNull($key): self

Usage

// Will filter the table where either teams OR bar values are NULL
QB::table('players')
    ->whereNull('team')
    ->orWhereNull('bar')
    ->get();
// SELECT * FROM players WHERE team IS NULL OR bar IS NULL;

// Can also be used with JSON array selectors.
QB::table('players')
    ->whereNull('column->keyA->keyB')
    ->get();

whereNotNull()

Checks if a columns value is NOT null

/**
 * @param string|Raw|Closure(NestedCriteria):void  $key  The field key to use to match
 * @return static
 */
public function whereNotNull($key): self

Usage

// Will filter the table where teams value is NOT NULL
QB::table('players')
    ->whereNotNull('team')
    ->get();
// SELECT * FROM players WHERE team IS NOT NULL;

// Can also be used with JSON array selectors.
QB::table('players')
    ->whereNotNull('column->keyA->keyB')
    ->get();

orWhereNotNull()

The same as whereNotNull() but uses OR as it joiner.

/**
 * @param string|Raw|Closure(NestedCriteria):void  $key  The field key to use to match
 * @return static
 */
public function OrWhereNotNull($key): self

Usage

// Will filter the table where either teams OR bar values are NULL
QB::table('players')
    ->whereNull('team')
    ->orWhereNull('bar')
    ->get();
// SELECT * FROM players WHERE team IS NOT NULL OR bar IS NOT NULL;

whereIn()

It is possible to create a WHERE IN condition

/**
 * @param string|Raw|Closure(NestedCriteria):void  $key  The field key to use to match
 * @param mixed[] $values The collection of values to looks for a match
 * @return static
 */
public function whereIn($key, $values): self

Usage

QB::table('players')
    ->whereIn('team', ['Team A', 'Team B'])
    ->get();
// SELECT * FROM players WHERE team IN ('Team A', 'Team B');

// Can be applied to multi conditions.
QB::table('players')
    ->whereIn('team', ['Team A', 'Team B'])
    ->whereIn('position', ['Striker', 'Goalkeeper'])
    ->get();
// "SELECT * FROM players 
// WHERE team IN ('Team A', 'Team B') AND position IN ('Striker', 'Goalkeeper')"

// You can use JSON arrow selectors here too.
QB::table('players')
    ->whereIn('column->keyA->keyB', ['Value 1', 'Value 2'])
    ->get();

related whereInJson() docs

orWhereIn()

Applies an OR joiner with the previous condition.

/**
 * @param string|Raw|Closure(NestedCriteria):void  $key  The field key to use to match
 * @param mixed[] $values The collection of values to looks for a match
 * @return static
 */
public function orWhereIn($key, $values): self

Usage

// Can be applied to multi conditions.
QB::table('players')
    ->whereIn('team', ['Team A', 'Team B'])
    ->orWhereIn('position', ['Striker', 'Goalkeeper'])
    ->get();
// "SELECT * FROM players 
// WHERE team IN ('Team A', 'Team B') OR position IN ('Striker', 'Goalkeeper')"

whereNotIn()

Filters all column values that are in a collection of options.

/**
 * @param string|Raw|Closure(NestedCriteria):void  $key  The field key to use to match
 * @param mixed[] $values The collection of values to looks for a match
 * @return static
 */
public function whereNotIn($key, $values): self

Usage

QB::table('players')
    ->whereNotIn('team', ['Team A', 'Team B'])
    ->get();
// SELECT * FROM players WHERE team NOT IN ('Team A', 'Team B');

// Can be applied to multi conditions.
QB::table('players')
    ->whereNotIn('team', ['Team A', 'Team B'])
    ->whereNotIn('position', ['Striker', 'Goalkeeper'])
    ->get();
// "SELECT * FROM players 
// WHERE team NOT IN ('Team A', 'Team B') AND position NOT IN ('Striker', 'Goalkeeper')"

// You can use JSON arrow selectors here too.
QB::table('players')
    ->whereNotIn('column->keyA->keyB', ['Value 1', 'Value 2'])
    ->get();

related whereNotInJson() docs

orWhereNotIn()

Applies an OR joiner with the previous condition.

/**
 * @param string|Raw|Closure(NestedCriteria):void  $key  The field key to use to match
 * @param mixed[]|Raw[] $values The collection of values to looks for a match
 * @return static
 */
public function orWhereNotIn($key, $values): self

Usage

// Can be applied to multi conditions.
QB::table('players')
    ->whereNotIn('team', ['Team A', 'Team B'])
    ->orWhereNotIn('position', ['Striker', 'Goalkeeper'])
    ->get();
// "SELECT * FROM players 
// WHERE team NOT IN ('Team A', 'Team B') OR position NOT IN ('Striker', 'Goalkeeper')"

whereBetween()

Filters rows where the defined column/value is between 2 values.

/**
 * @param string|Raw|Closure(NestedCriteria):void  $key  The field key to use to match
 * @param mixed|Raw $valueFrom From value
 * @param mixed|Raw  $valueTo From value
 * @return static
 */
public function whereBetween($key, $valueFrom , $valueTo): self

Usage

QB::table('players')
    ->whereBetween('goals_scored', 25, 30)
    ->get();
// SELECT * FROM players WHERE goals_scored BETWEEN 25 AND 30;

// Can be applied to multi conditions.
QB::table('players')
    ->whereBetween('goals_scored', 10, 49)
    ->whereBetween('games_played', 5, 40)
    ->get();
// "SELECT * FROM players WHERE goals_scored BETWEEN 10 AND 49 AND games_played BETWEEN 5 AND 40"

// You can use JSON arrow selectors here too.
QB::table('players')
    ->whereBetween('column->keyA->keyB', 10, 49)
    ->get();

related whereBetweenJson() docs

orWhereBetween()

The same as whereBetween() but uses OR as the joiner

/**
 * @param string|Raw|Closure(NestedCriteria):void  $key  The field key to use to match
 * @param mixed|Raw $valueFrom From value
 * @param mixed|Raw  $valueTo From value
 * @return static
 */
public function orWhereBetween($key, $valueFrom , $valueTo): self

Usage

// Can be applied to multi conditions.
QB::table('players')
    ->whereBetween('goals_scored', 10, 49)
    ->orWhereBetween('games_played', 5, 40)
    ->get();
// "SELECT * FROM players WHERE goals_scored BETWEEN 10 AND 49 OR games_played BETWEEN 5 AND 40"

Query Methods

table()

from()

select()

selectDistinct()

get()

first()

find()

findAll()

findOrFail()

count()

average()

min()

max()

sum()

offset()

limit()

orderBy()

group()

having()

where()

orWhere()

Grouped Where

whereNot()

orWhereNot()

whereNull()

orWhereNull()

whereNotNull()

orWhereNotNull()

whereIn()

orWhereIn()

whereNotIn()

orWhereNotIn()

whereBetween()

orWhereBetween()

whereDate()

whereDay()

whereMonth()

whereYear()

join()

leftJoin()

leftJoin()

rightJoin()

outerJoin()

crossJoin()

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Clone this wiki locally