Skip to content

Query Methods

Jump to bottom
Glynn Quelch edited this page Feb 13, 2022 · 89 revisions

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): QueryBuilderHandler

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"

asObject()

It is possible to return your results back in as an object, you can read more about it on the Result Hydration page.

/**
 * @param class-string      $className        The fully namespaced name of the object
 * @param array<int, mixed> $constructorArgs  Array of any values needed to construct the object.
 * @return QueryBuilderHandler
 */
public function asObject($className, $constructorArgs = array()): QueryBuilderHandler

example

$model = QB::table( 'my_table' )->asObject(MyModel::class)->select( 'id' )->first();

var_dump($model);
/*
object(MyModel)#1 (2) {
  ["id"] => int(1)
  ["name"] => string(5) "Glynn"
}
*/

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): QueryBuilderHandler

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): QueryBuilderHandler
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()

You can define the offset to return the results from.

/**
 * @param int $offset
 * @return QueryBuilderHandler
 */
public function offset(int $offset): QueryBuilderHandler

Usage

QB::table('players')
    ->offset(12)
    ->get();
// SELECT * FROM players OFFSET 12

limit()

You can limit the number of results that are returned

/**
 * @param int $limit
 * @return QueryBuilderHandler
 */
public function limit(int $limit): QueryBuilderHandler

Usage

QB::table('players')
    ->limit(12)
    ->get();
// SELECT * FROM players LIMIT 12

orderBy()

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

/**
 * @param string|array<string|int, string|Raw> $fields            Fields to order by
 * @param string                               $defaultDirection  Direction to order by, if not defined
 * @return QueryBuilderHandler
 */
public function orderBy($fields, string $defaultDirection = 'ASC'): QueryBuilderHandler

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

groupBy()

You can group your results when used with joins and other expressions.

 /**
 * @param string|string[] $field either the single field or an array of fields
 * @return QueryBuilderHandler
 */
public function groupBy($field): QueryBuilderHandler

Example

$customersInCountries = QB::table('Customers')
    ->select(new Raw('COUNT(CustomerID)'), 'Country')
    ->groupBy('Country')
    ->get();
// SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;

QB::table('Customers')
    ->select('COUNT(CustomerID)', 'Country')
    ->groupBy('Country')
    ->orderBy(Raw::val('COUNT(CustomerID)'), 'DESC')
    ->get();

// SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC

having()

You have full access to having, allowing for single or multiple conditions

 /**
 * @param string|string[]|Raw|Raw[]  $key       The column to reference
 * @param string                     $operator  =, <>, >, < 
 * @param mixed                      $value     The value to look for
 * @param string                     $joiner    If used on second having statement, the method to join with.
 * @return QueryBuilderHandler
 */
public function having($key, string $operator, $value, string $joiner = 'AND'): QueryBuilderHandler

examples

// Using SUM function
QB::table('order_details')
    ->select(['product', 'SUM(quantity)' => '"Total quantity"'])
    ->groupBy('product')
    ->having('SUM(quantity)', '>', 10)
    ->get();
// SELECT product, SUM(quantity) AS "Total quantity" FROM order_details GROUP BY product HAVING SUM(quantity) > 10

// Group by multiple 
QB::table('movies')
    ->select('category_id', 'year_released')
    ->groupBy('year_released')
    ->having('category_id', '<', 4)
    ->having('category_id', '>', 2);
//SELECT category_id, year_released FROM movies GROUP BY year_released HAVING category_id < 4 AND category_id > 2

orHaving()

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

/**
 * @param string|string[]|Raw|Raw[]  $key       The column to reference
 * @param string                     $operator  =, <>, >, < 
 * @param mixed                      $value     The value to look for
 * @return QueryBuilderHandler
 */
    public function orHaving($key, $operator, $value): QueryBuilderHandler

examples

QB::table('movies')
    ->select('category_id', 'year_released')
    ->groupBy('year_released')
    ->having('category_id', '<', 4)
    ->orHaving('category_id', '>', 2);
//SELECT category_id, year_released FROM movies GROUP BY year_released HAVING category_id < 4 OR category_id > 2

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 QueryBuilderHandler
 */
public function where($key, $operator, $value ): QueryBuilderHandler

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 QueryBuilderHandler
 */
public function orWhere($key, $operator, $value ): QueryBuilderHandler

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

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 QueryBuilderHandler
 */
public function whereNot($key, $operator, $value ): QueryBuilderHandler

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 QueryBuilderHandler
 */
public function orWhereNot($key, $operator, $value ): QueryBuilderHandler

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 QueryBuilderHandler
 */
public function whereNull($key): QueryBuilderHandler

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 QueryBuilderHandler
 */
public function orWhereNull($key): QueryBuilderHandler

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 QueryBuilderHandler
 */
public function whereNotNull($key): QueryBuilderHandler

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 QueryBuilderHandler
 */
public function OrWhereNotNull($key): QueryBuilderHandler

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 QueryBuilderHandler
 */
public function whereIn($key, $values): QueryBuilderHandler

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 QueryBuilderHandler
 */
public function orWhereIn($key, $values): QueryBuilderHandler

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 QueryBuilderHandler
 */
public function whereNotIn($key, $values): QueryBuilderHandler

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 QueryBuilderHandler
 */
public function orWhereNotIn($key, $values): QueryBuilderHandler

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 QueryBuilderHandler
 */
public function whereBetween($key, $valueFrom , $valueTo): QueryBuilderHandler

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 QueryBuilderHandler
 */
public function orWhereBetween($key, $valueFrom , $valueTo): QueryBuilderHandler

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"

whereDate()

Its possible to filter date column (date, datetime, unix) types for matching (full) dates.

/**
 * @param string|Raw        $key      
 * @param string|mixed|null $operator Can be used as value, if 3rd arg not passed
 * @param mixed|null $value
 * @return QueryBuilderHandler
 */
public function whereDate($key, $operator = null, $value = null):QueryBuilderHandler

DB Table

id title date datetime
1 5 Easy Rules Of Tree 2022-10-10 2022-10-10 18:19:03
2 The Death Of Lard 2010-10-05 2010-10-05 18:19:03
3 Is Bacon Still Relevant? 2020-03-10 2020-03-10 18:19:03

examples

// Filtering date with a date.
QB::table('foo')->select('id','title')->whereDate('date', '2022-10-10')->first();
// Result { id:1, title:'5 Easy Rules Of Tree' }

// Filtering DateTime with just a date, this works with UNIX format too
QB::table('foo')->select('id','title')->whereDate('datetime', '2010-10-05')->first();
// Result { id:2, title:'The Death Of Lard' }

// Finding all results after a date.
QB::table('foo')->select('id','title')->whereDate('date', '>', '2019-03-12')->get();
// Result [
//    {id: 2, title: 'The Death Of Lard' },
//    {id: 3, title: 'Is Bacon Still Relevant?' },
// ]

JSON Selectors are not currently supported with WHERE DATE functions (including whereDay(), whereMonth() and whereYear())

whereDay()

It is possible to query a valid date column for any dates with the same day

/**
 * @param string|Raw        $key      
 * @param string|mixed|null $operator Can be used as value, if 3rd arg not passed
 * @param mixed|null $value
 * @return QueryBuilderHandler
 */
public function whereDay($key, $operator = null, $value = null):QueryBuilderHandler

examples

// Filtering date with a date.
QB::table('foo')->select('id','title')->whereDay('date', '10')->get();
// Result [
//    {id: 1, title: '5 Easy Rules Of Tree' },
//    {id: 3, title: 'Is Bacon Still Relevant?' },
// ]


// Filtering DateTime with just a date, this works with UNIX format too
QB::table('foo')->select('id','title')->whereDay('datetime', '5')->first();
// Result {id:2, title:'The Death Of Lard'}

whereMonth()

It is possible to query a valid date column for any dates with the same month

/**
 * @param string|Raw        $key      
 * @param string|mixed|null $operator Can be used as value, if 3rd arg not passed
 * @param mixed|null $value
 * @return QueryBuilderHandler
 */
public function whereMonth($key, $operator = null, $value = null):QueryBuilderHandler

examples

// Filtering date with a date.
QB::table('foo')->select('id','title')->whereMonth('date', '10')->get();
// Result [
//    {id: 1, title: '5 Easy Rules Of Tree' },
//    {id: 2, title: 'The Death Of Lard' },
// ]


// Filtering DateTime with just a date, this works with UNIX format too
QB::table('foo')->select('id','title')->whereMonth('datetime', '3')->first();
// Result {id: 3, title: 'Is Bacon Still Relevant?' }

whereYear()

It is possible to query a valid date column for any dates with the same year

/**
 * @param string|Raw        $key      
 * @param string|mixed|null $operator Can be used as value, if 3rd arg not passed
 * @param mixed|null $value
 * @return QueryBuilderHandler
 */
public function whereYear($key, $operator = null, $value = null):QueryBuilderHandler

examples

// Filtering date with a date.
QB::table('foo')->select('id','title')->whereYear('date', '!=', '2010')->get();
// Result [
//    {id: 1, title: '5 Easy Rules Of Tree' },
//    {id: 3, title: 'Is Bacon Still Relevant?' },
// ]


// Filtering DateTime with just a date, this works with UNIX format too
QB::table('foo')->select('id','title')->whereYear('datetime', '2022')->first();
// Result {id: 1, title: '5 Easy Rules Of Tree' }

when()

It is possible to have an inline if or if/else as part of a statement declaration.

/**
 * @param bool             $condition
 * @param \Closure         $if
 * @param \Closure|null    $else
 * @return self
 */
public function when(bool $condition, \Closure $if, ?\Closure $else = null): self

example

# IF expression
QB::table('foo')
    ->where('type', 'free')
    ->when(
        $user->type === 'premium', 
        fn($builder)=> $builder->orWhere('type', 'premium')
    );
// Would only add the orWhere() condition if the user type was premium

# IFELSE expression
QB::table('bar')
    ->select('id', 'bio')
    ->when(
        $foo->showFullName,
        fn($builder) => $builder->select(['fullname' => 'name']),
        fn($builder) => $builder->select(['username' => 'name']),
    )
    ->where('bio', 'like', '%something%')
    ->get();
// Would return either fullname or username as name field based on  $foo->showFullName

join()

It is possible to join 2 tables or sub queries using any of the common MYSQL methods.

 /**
 * @param string|Raw          $table     Table name or expression to join 
 * @param string|Raw|Closure  $key       Column/field of $table to join on
 * @param string|null         $operator  The operation to use (=, !=, <, > etc)
 * @param string|Raw|null     $value     
 * @param string              $type
 *
 * @return QueryBuilderHandler
 */
public function join($table, $key, ?string $operator = null, $value = null, $type = 'inner'): QueryBuilderHandler

examples

QB::table('reference')
    ->join('other', 'other.reference', '=', 'reference.id')
    ->get();

"SELECT * FROM reference INNER JOIN other ON other.reference = reference.id"

// Multiple tables can be joined.
QB::table('reference')
    ->join('other', 'other.reference', '=', 'reference.id')
    ->join('another', 'another.id', '=', 'reference.id');
    ->get();

"SELECT * FROM reference 
INNER JOIN other ON other.reference = reference.id
INNER JOIN another ON another.id = reference.id"

The parent join() method allows the passing of the $type, so those without helpers can still be used.

QB::table('reference')
    ->join('other', 'other.reference', '=', 'reference.id', 'NATURAL')
    ->get();

"SELECT * FROM reference NATURAL JOIN other ON other.reference = reference.id"

JSON Arrow selectors are allows to be used for both $key and $value in all join methods.

QB::table('reference')
   ->join('other', 'other.json->someKey->value', '=', 'reference.json->id')
   ->get();

You can do joins ON multiple conditions using a grouped join

leftJoin()

As with join, but for LEFT JOIN

/**
 * @param string|Raw $table
 * @param string|Raw|Closure $key
 * @param string|null $operator
 * @param mixed $value
 *
 * @return static
 */
public function leftJoin($table, $key, $operator = null, $value = null): self

example

QB::table('reference')
    ->leftJoin('other', 'other.reference', '=', 'reference.id')
    ->get();

"SELECT * FROM reference LEFT JOIN other ON other.reference = reference.id"

JSON Arrow selectors are allows to be used for both $key and $value in all join methods.

QB::table('reference')
   ->leftJoin('other', 'other.json->someKey->value', '=', 'reference.json->id')
   ->get();

rightJoin()

As with join, but for RIGHT JOIN

/**
 * @param string|Raw $table
 * @param string|Raw|Closure $key
 * @param string|null $operator
 * @param mixed $value
 *
 * @return static
 */
public function rightJoin($table, $key, $operator = null, $value = null): self

example

QB::table('reference')
    ->rightJoin('other', 'other.reference', '=', 'reference.id')
    ->get();

"SELECT * FROM reference RIGHT JOIN other ON other.reference = reference.id"

JSON Arrow selectors are allows to be used for both $key and $value in all join methods.

QB::table('reference')
   ->rightJoin('other', 'other.json->someKey->value', '=', 'reference.json->id')
   ->get();

outerJoin()

As with join, but for OUTER JOIN

/**
 * @param string|Raw $table
 * @param string|Raw|Closure $key
 * @param string|null $operator
 * @param mixed $value
 *
 * @return static
 */
public function outerJoin($table, $key, $operator = null, $value = null): self

example

QB::table('reference')
    ->outerJoin('other', 'other.reference', '=', 'reference.id')
    ->get();

"SELECT * FROM reference OUTER JOIN other ON other.reference = reference.id"

JSON Arrow selectors are allows to be used for both $key and $value in all join methods.

QB::table('reference')
   ->outerJoin('other', 'other.json->someKey->value', '=', 'reference.json->id')
   ->get();

crossJoin()

@TODO

Clone this wiki locally