Sub & Nested Queries

Sub Queries

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

Nested Where

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() and orWhereNot(), 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')"

Grouped Join

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