-
Notifications
You must be signed in to change notification settings - Fork 0
Sub & Nested Queries
Glynn Quelch edited this page Feb 6, 2022
·
14 revisions
It is also possible to create a sub query to use as the "table".
// Create an instance of a builder (static method not suitable)
$builder = new QueryBuilderHandler();
// Create the sub query
$sub_query = $builder
->table( 'table' )
->where( 'col', Binding::asString( 'a' ) );
// Put it all together
$count = $builder
->table( $builder->subQuery( $sub_query, 'count' ) )
->select( Raw::val( 'COUNT(*) AS field' ) )
->first();
Sometimes it is needed to make a nested where condition. To do this, you can pass a closure into the first parameter to handle this.
/**
* @param NestedCriteria $builder
* @return void
*/
function( NestedCriteria $builder ): void
QB::table('foo')
->where('key', '=', 'value')
->where(
/**
* @param \Closure( NestedCriteria $builder ): void
* @return void
*/
function( NestedCriteria $builder ): void{
$builder->where('key2', '<>', 'value2');
$builder->where('key3', '=', 'value3');
}
)
->get();
// Would result in the following query
"SELECT * FROM foo WHERE key = 'value' AND (key2 <> 'value2' AND key3 = 'value3')"
Can also be used with
orWhere()
whereNot()
andorWhereNot()
, even supports JSON selectors.
QB::table('foo')
->where('key', '=', 'value')
->where(
/**
* @param \Closure( NestedCriteria $builder ): void
* @return void
*/
function( NestedCriteria $builder ): void {
$builder->where('key2', '<>', 'value2')->orWhereNot('key3', 'value3');
}
)->get();
// Would result in the following query
"SELECT * FROM foo WHERE key = 'value' AND (key2 <> 'value2' OR NOT key3 = 'value3')"
It is possible to create joins with multiple conditions, this can be done with any existing (none json) join method (join(), leftJoin(), rightJoin(), crossJoin() and outerJoin())
Passing a closure as the second parameter, will allow the defining of multiple on
conditions.
/**
* Closure for Join
* @param JoinBuilder $builder
* @return void
*/
function(JoinBuilder $builder): void {
/**
* On condition
* @param string|Raw $column Column or expression for rows from table being joined
* @param string $operator Any valid MYSQL operators [=, !=, <, >]
* @param string|Raw $value Column or expression to join from existing table
*/
$builder->on('table.col', '=', 'reference.col');
/**
* Or On condition
* @param string|Raw $column Column or expression for rows from table being joined
* @param string $operator Any valid MYSQL operators [=, !=, <, >]
* @param string|Raw $value Column or expression to join from existing table
*/
$builder->orOn('table.col', '=', 'reference.col');
}
example
// AND ON
QB::able('foo')
->lefJoin('bar', function (JoinBuilder $builder): void {
$builder->on('bar.id', '!=', 'foo.id');
$builder->on('bar.baz', '!=', 'foo.baz');
});
"SELECT * FROM foo LEFT JOIN bar ON bar.id != foo.id AND bar.baz != foo.baz"
// OR ON
QB::able('foo')
->lefJoin('bar', function (JoinBuilder $builder): void {
$builder->on('bar.id', '!=', 'foo.id');
$builder->orOn('bar.baz', '!=', 'foo.baz');
});
"SELECT * FROM foo LEFT JOIN bar ON bar.id != foo.id OR bar.baz != foo.baz"
It is possible to grouped joins using type/methods which do not have helpers
QB::table('foo')
->join('bar', function($builder){...}, null, null, 'STRAIGHT_JOIN')