Skip to content

Build A Query

Jump to bottom
Ale Mostajo edited this page Apr 6, 2020 · 8 revisions

The QueryBuilder class provides various methods (similar to SQL statements) to build a query.

How to build

Every method in the class can be used in a chain to provide a consistent and clean code or can be used independently to provide versatility when building complex queries.

// Single method calling
$builder = QueryBuilder::create();
$builder->select( '*' );
$builder->from( 'table_name' );

// Chaining
$builder = QueryBuilder::create()
    ->select( '*' )
    ->from( 'table_name' );

Build Statements

Select Statement

select( $statement )

Parameter Type Description
$statement string Select SQL statement.

Usage sample:

$builder->select( 'column_a' )
    ->select( 'min(column_b) as `minimum`' )
    ->select( 'case column_a = 1 then column_b else table_b.id end as `case`' );

From Statement

from( $from, $add_prefix = true )

Parameter Type Description
$from string From SQL statement.
$add_prefix bool Flag that inidicates if DB prefix should be added at the beginning of the statement. Default: true

Usage sample:

$builder->select( 'ta.*' )
    ->from( 'table_a as `ta`' );

NOTE: The previous statement will add the prefix to table_a. For example, if the configured prefix is wp_, the table name used in the built query would be wp_table_a.

No prefix usage sample:

$builder->select( 'tc.*' )
    ->from( $other_prefix . 'table_c as `tc`', false );

NOTE: The previous statement will not add the prefix to table_c, instead, it is using a variable containing other custom prefix. For example, if $other_prefix value is other_, the table name used in the built query would be other_table_c.

Join Statement

join( $table, $args, $type = false, $add_prefix = true )

Parameter Type Description
$table string Join table name.
$args array Join arguments. A collection of arrays with different arguments
$type bool or string Flag that inidicates if its LEFT JOIN or regular JOIN``. Or string indicating join type (i.e. 'RIGHT'). Default: false`
$add_prefix bool Flag that inidicates if DB prefix should be added at the beginning of the statement. Default: true

Usage sample:

$builder->select( 'a.*' )
    ->from( 'table_a as `a`' )
    ->join( 'table_b as `b`', [
        [
            'key_a' => 'a.id',
            'key_b' => 'b.table_a_id',
        ],
    ] );

Join Arguments

There are multiple ways to join two tables using this statement:

  1. Using columns within 2 different tables:

By creating a subarray with the keys key_a and key_b, the builder will create the join using 2 column names, see an example:

$builder->select( 'a.id' )
    ->select( 'b.name' )
    ->from( 'table_a as `a`' )
    ->join( 'table_b as `b`', [
        [
            'key_a' => 'a.id',
            'key_b' => 'b.table_a_id',
        ],
    ] );
  1. Using a given value:

By creating a subarray with the keys key and value, the builder will create the join using a given value:

$builder->select( 'a.id' )
    ->select( 'b.name' )
    ->from( 'table_a as `a`' )
    ->join( 'table_b as `b`', [
        [
            'key'   => 'b.id',
            'value' => $id,
        ],
    ] );
  1. Using a raw join statement:

By creating a subarray with the key raw the builder will create the join using a raw statement:

$builder->select( 'a.id' )
    ->select( 'b.name' )
    ->from( 'table_a as `a`' )
    ->join( 'table_b as `b`', [
        [
            'raw' => 'b.table_a_id = a.id',
        ],
    ] );

Join Operator

The join argument operator can be changed from the default (=) by adding the key operator, see an example:

$builder->select( 'a.id' )
    ->select( 'b.name' )
    ->from( 'table_a as `a`' )
    ->join( 'table_b as `b`', [
        [
            'key_a' => 'a.id',
            'operator' => '<>',
            'key_b' => 'b.table_a_id',
        ],
    ] );

Betweem operator

The between values can be defined using keys min and/or max:

$builder->select( 'a.id' )
    ->select( 'b.name' )
    ->from( 'table_a as `a`' )
    ->join( 'table_b as `b`', [
        [
            'key' => 'b.price',
            'operator' => 'BETWEEN',
            'min' => 1,
            'max' => 10,
        ],
    ] );

Between columns can be defined using keys key_a, key_b and/or key_c:

$builder->select( 'a.id' )
    ->select( 'b.name' )
    ->from( 'table_a as `a`' )
    ->join( 'table_b as `b`', [
        [
            'key_a' => 'b.price',
            'operator' => 'BETWEEN',
            'key_b' => 'a.min_price',
            'key_c' => 'a.max_price',
        ],
    ] );

Multiple Arguments

Multiple join arguments can be used within an statement, see an example:

$builder->select( 'a.id' )
    ->select( 'b.name' )
    ->from( 'table_a as `a`' )
    ->join( 'table_b as `b`', [
        [
            'key_a' => 'a.id',
            'key_b' => 'b.table_a_id',
        ],
        [
            'key'   => 'b.id',
            'value' => $id,
        ],
    ] );

The joint within an argument can be changed from the default (AND) by adding the key joint, see an example:

$builder->select( 'a.id' )
    ->select( 'b.name' )
    ->from( 'table_a as `a`' )
    ->join( 'table_b as `b`', [
        [
            'key_a' => 'a.id',
            'key_b' => 'b.table_a_id',
        ],
        [
            'key'   => 'b.id',
            'value' => $id,
            'joint' => 'OR',
        ],
    ] );

Join types

The third parameter indicates the type of JOIN to build.

If a bool value is used, the builder will either use "LEFT JOIN" when true and regular "JOIN" when false:

$builder->select( 'a.*' )
    ->from( 'table_a as `a`' )
    // LEFT JOIN
    ->join( 'table_b as `b`', [...], true );

If a string value is used, the builder will used the passed value to determine the type:

$builder->select( 'a.*' )
    ->from( 'table_a as `a`' )
    // RIGHT OUTER JOIN (caps are not validated)
    ->join( 'table_b as `b`', [...], 'RIGHT outer');

The following is the list of supported string join types:

String value Join built
`` JOIN
INNER INNER JOIN
LEFT LEFT JOIN
RIGHT RIGHT JOIN
CROSS CROSS JOIN
LEFT OUTER LEFT OUTER JOIN
RIGHT OUTER RIGHT OUTER JOIN

Where Statement

where( $args )

Parameter Type Description
$args array Where arguments. A collection of where statements

Where Arguments

The array key within the arguments identifies the column to which the statement will be applied, see an example:

$builder->select( '*' )
    ->from( 'table_a as `a`' )
    ->where( [
        'a.id' => $id
    ] );

Multiple Where Arguments

Where supports multiple arguments as the parameter.

If the statement is a simple comparison with a value, use the example provided above. Otherwise, convert it to an array to specify a variety in the statement.

Usage sample:

$builder->select( '*' )
    ->from( 'table_a as `a`, table_b as `b`' )
    ->where( [
        'a.id' => $id,
        'a.type_id' => [
            'key' => 'b.id',
        ],
    ] );

Where Argument Operator

Provide a subarray on the argument with the subkey operator to change the default comparisson operator (default is =), see an example:

$builder->select( '*' )
    ->from( 'table_a as `a`, table_b as `b`' )
    ->where( [
        'a.id' => [
            'operator' => '<>',
            'value'    => $id,
        ],
    ] );

Where Argument Operator

Provide a subarray on the argument with the subkey operator to change the default comparisson operator (default is =), see an example:

$builder->select( '*' )
    ->from( 'table_a as `a`, table_b as `b`' )
    ->where( [
        'a.id' => [
            'operator' => '<>',
            'value'    => $id,
        ],
    ] );
Between operator

Between values can be defined using keys min and/or max:

$builder->select( '*' )
    ->from( 'table_a as `a`' )
    ->where( [
        'a.price' => [
            'operator' => 'BETWEEN',
            'min' => 1,
            'max' => 10,
        ],
    ] );

Between columns can be defined using keys key and/or key_b:

$builder->select( '*' )
    ->from( 'table_a as `a`' )
    ->where( [
        'a.price' => [
            'operator' => 'BETWEEN',
            'key' => 'a.min_price',
            'key_b' => 'a.max_price',
        ],
    ] );

Where Raw Argument

Specify an argument with key name raw to provide a raw statement:

$builder->select( '*' )
    ->from( 'table_a as `a`, table_b as `b`' )
    ->where( [
        'raw' => 'a.type_id = b.id',
    ] );

Keywords Statement

This statement generates a where statement suited for keyword searches.

keywords( $keywords, $columns, $separator = ' ' )

Parameter Type Description
$keywords string Searched keywords as a string.
$columns array List of columns where to search for the keywords.
$separator string String character that separates the words within the string. Default: ' '

Usage sample:

$keywords = 'typed search';
$builder->select( '*' )
    ->from( 'table_a as `a`' )
    ->keywords( $keywords, [ 'a.name', 'a.description' ] );

Group By Statement

group_by( $statement )

Parameter Type Description
*$statement * string Group by SQL statement.

Usage sample:

$builder->select( '*' )
    ->from( 'table_a as `a`' )
    ->group_by( 'a.id' )
    ->group_by( 'a.type_id' );

Having Statement

having( $statement )

Parameter Type Description
*$statement * string Group by SQL statement.

Usage sample:

$builder->select( '*' )
    ->from( 'table_a as `a`' )
    ->group_by( 'a.id' )
    ->having( 'count(a.id) > 1' );

Order By Statement

order_by( $key, $direction = 'ASC' )

Parameter Type Description
$key string Order by SQL statement.
*$direction * string Order direction (ASC or DESC). Default: 'ASC'

Usage sample:

$builder->select( '*' )
    ->from( 'table_a as `a`' )
    ->order_by( 'a.name' )
    ->order_by( 'a.id', 'DESC' );

Limit and Offset Statements

limit( $limit )

Parameter Type Description
$limit int Limit value.

offset( $offset)

Parameter Type Description
$offset int Offset value.

Usage sample:

$builder->select( '*' )
    ->from( 'table_a as `a`' )
    ->limit( 10 )
    ->offset( 2 );

(c) 2019 - 10 Quality - Query Builder Library for Wordpress

Clone this wiki locally