Filtering records

Filtering records is one of the most important and intensively used operation in SQL. The library aims to make data filtering an easy task for the developers, by implementing a set of targeted methods capable of simplifying the filtering process.

Adding filters

The where() method is used to add filters to the query. The method returns a Where clause object, which can be used to add multiple filters to the query.

$query = new QueryBuilder();

$query->select()
	->from( 'users' )
	->where( 'age' )->is( 18 );

Multiple conditions

Adding multiple conditions to your query is done by using the and_where and or_where methods. Those methods works exactly as the where method and depending on which method you use, they will combine with the previous declared condition by using an AND or an OR operator.

$query = new QueryBuilder();

$query->select()
	->from( 'users' )
	->where( 'age' )->is( 18 )
	->where( 'name' )->is( 'John' )
	->and_where( 'surname' )->is( 'Doe' );

Grouping conditions

Grouping conditions is done by using the group_where method. The group_where() method takes two parameters:

• A callback function that defines the grouped conditions
• An optional operator ('AND' or 'OR') to connect this group with other conditions (default is 'AND')

Inside the callback function, you can chain multiple conditions that will be grouped together.

Here's an example of how to use group_where():

$query->where('status')->is('active')
    ->group_where(function($q) {
        $q->where('age')->greater_than(18)
          ->and_where('age')->less_than(65);
    }, 'AND')
	->or_where('vip')->is(true);

This flexibility allows you to create complex WHERE clauses with nested conditions as needed.

The is() method

The is() method is used to filter records by a specific value. The method takes a single parameter, which is the value to filter by.

$query = new QueryBuilder();

$query->select()
	->from( 'users' )
	->where( 'age' )->is( 18 );

You can also chain multiple conditions:

$query->where('status')->is('active')
    ->and_where('age')->is(18)
    ->or_where('vip')->is(true);

The is_not() method

Adds a filtering condition, so that only those records, that have the specified column’s value not equal to a given value, are returned.

$query = new QueryBuilder();

$query->select()
	->from( 'users' )
	->where( 'age' )->is_not( 18 );

The less_than() method

The less_than() method is part of the Where clause class and is used to add a "less than" comparison to your SQL query.

$query = new QueryBuilder();

$query->select()
	->from( 'users' )
	->where( 'age' )->less_than( 18 );

The greater_than() method

The greater_than() method is part of the Where clause class and is used to add a "greater than" comparison to your SQL query.

$query = new QueryBuilder();

$query->select()
	->from( 'users' )
	->where( 'age' )->greater_than( 18 );

The at_least() method

The at_least() method is part of the Where clause class and is used to add a "greater than or equal to" condition to your SQL query.

$query = new QueryBuilder();

$query->select()
	->from( 'users' )
	->where( 'age' )->at_least( 18 );

The at_most() method

The at_most() method is part of the Where clause class and is used to add a "less than or equal to" condition to your SQL query.

$query = new QueryBuilder();

$query->select()
	->from( 'users' )
	->where( 'age' )->at_most( 18 );

The between() method

The between() method is part of the Where clause class and is used to add a "between" condition to your SQL query.

$query = new QueryBuilder();

$query->select()
	->from( 'users' )
	->where( 'age' )->between( 18, 65 );

The in() method

The in() method is part of the Where clause class and is used to add an "IN" condition to your SQL query.

$query = new QueryBuilder();

$query->select()
	->from( 'users' )
	->where( 'age' )->in( [ 18, 20, 22 ] );

The not_in() method

The not_in() method is part of the Where clause class and is used to add a "NOT IN" condition to your SQL query.

$query = new QueryBuilder();

$query->select()
	->from( 'users' )
	->where( 'age' )->not_in( [ 18, 20, 22 ] );

The is_null() method

The is_null() method is part of the Where clause class and is used to add a "IS NULL" condition to your SQL query.

$query = new QueryBuilder();

$query->select()
	->from( 'users' )
	->where( 'age' )->is_null();

The is_not_null() method

The is_not_null() method is part of the Where clause class and is used to add a "IS NOT NULL" condition to your SQL query.

$query = new QueryBuilder();

$query->select()
	->from( 'users' )
	->where( 'age' )->is_not_null();

The where_raw() method

The where_raw() method allows you to add a raw SQL condition to the WHERE clause of your query. This is useful when you need to add complex conditions that are not easily expressed using the standard query builder methods.

To use this method in your query, you would typically chain it to your query builder like this:

$query = new QueryBuilder();

$result = $query->select()
    ->from('users')
    ->where_raw('age > %s AND status = %s', [18, 'active']);