Overview

Namespaces

  • LucidFrame
    • Console
    • Core
    • File
  • None

Classes

  • App
  • Database
  • Form
  • Middleware
  • Pager
  • QueryBuilder
  • Router
  • SchemaManager
  • Seeder
  • Validation
  • View
  • Overview
  • Namespace
  • Class

Class QueryBuilder

QueryBuilder class is responsible to dynamically create SQL queries.

Namespace: LucidFrame\Core
Located at classes/QueryBuilder.php
Methods summary
public
# __construct( string $table = null, string $alias = null )

Constructor

Constructor

Parameters

$table
The base table to select from
$alias
The alias for the table
public object
# from( string $table, string $alias = null )

Table to SELECT

Table to SELECT

Parameters

$table
The table name
$alias
The table alias

Returns

object
QueryBuilder
public object
# fields( string $alias, array $fields = array() )

Add fields to SELECT

Add fields to SELECT

Parameters

$alias
The table alias
$fields
Array of field names

Returns

object
QueryBuilder
public object
# field( string $field, array $alias = null )

Add field to SELECT

Add field to SELECT

Parameters

$field
The field name
$alias
The alias for the field name

Returns

object
QueryBuilder
public object
# join( string $table, string $alias, string $condition, string $type = 'INNER' )

Add a table to join

Add a table to join

Parameters

$table
The table name
$alias
The alias for the table
$condition
The join condition e.g., t1.pk = t2.fk
$type
INNER, LEFT, RIGHT or OUTER

Returns

object
QueryBuilder
public object
# leftJoin( string $table, string $alias, string $condition )

Add a table to perform left join

Add a table to perform left join

Parameters

$table
The table name
$alias
The alias for the table
$condition
The join condition e.g., t1.pk = t2.fk

Returns

object
QueryBuilder
public object
# rightJoin( string $table, string $alias, string $condition )

Add a table to perform right join

Add a table to perform right join

Parameters

$table
The table name
$alias
The alias for the table
$condition
The join condition e.g., t1.pk = t2.fk

Returns

object
QueryBuilder
public object
# outerJoin( string $table, string $alias, string $condition )

Add a table to perform outer join

Add a table to perform outer join

Parameters

$table
The table name
$alias
The alias for the table
$condition
The join condition e.g., t1.pk = t2.fk

Returns

object
QueryBuilder
public object
# where( array|null $condition = null )

Alias of andWhere() Create WHERE ... AND condition

Alias of andWhere() Create WHERE ... AND condition

Parameters

$condition
The array of conditions

Returns

object
QueryBuilder
public object
# andWhere( array|null $condition = null )

Create WHERE ... AND condition

Create WHERE ... AND condition

Parameters

$condition

The array of conditions

array( 'fieldName1' => $value1, 'fieldName2 >=' => $value2, 'fieldName3 => NULL )

OR

array( 'fieldName1' => $value1, 'fieldName2 >=' => $value2, 'fieldName3 => NULL, '$or' => array( 'fieldName4' => array(1, 2, 3) 'fieldName4 <' => 10 ) )

Returns

object
QueryBuilder
public object
# orWhere( array|null $condition = null )

Create WHERE ... OR condition

Create WHERE ... OR condition

Parameters

$condition

The array of conditions

array( 'fieldName1' => $value1, 'fieldName2 >=' => $value2, 'fieldName3 => NULL )

OR

array( 'fieldName1' => $value1, 'fieldName2 >=' => $value2, 'fieldName3 => NULL, '$and' => array( 'fieldName4' => array(1, 2, 3) 'fieldName4 <' => 10 ) )

Returns

object
QueryBuilder
public object
# condition( string $field, mixed $value )

Create simple WHERE condition with field/value assignment

Create simple WHERE condition with field/value assignment

Parameters

$field
The field name
$value

The value to check against the field name

$qb = db_select('post', 'p') ->orWhere() ->condition('catId', 1) ->condition('catId', 2);

Returns

object
QueryBuilder
public object
# exists( string $subquery, string $type = 'AND' )

Add EXISTS clause to WHERE condition

Add EXISTS clause to WHERE condition

Parameters

$subquery
The sub-query statement
$type
AND|OR

Returns

object
QueryBuilder
public object
# notExists( string $subquery, string $type = 'AND' )

Add NOT EXISTS clause to WHERE condition

Add NOT EXISTS clause to WHERE condition

Parameters

$subquery
The sub-query statement
$type
AND|OR

Returns

object
QueryBuilder
public object
# orExists( string $subquery )

Add OR EXISTS clause to WHERE condition

Add OR EXISTS clause to WHERE condition

Parameters

$subquery
The sub-query statement

Returns

object
QueryBuilder
public object
# orNotExists( string $subquery )

Add OR NOT EXISTS clause to WHERE condition

Add OR NOT EXISTS clause to WHERE condition

Parameters

$subquery
The sub-query statement

Returns

object
QueryBuilder
public object
# orderBy( string $field, string $sort = 'ASC' )

Add ORDER BY clause

Add ORDER BY clause

Parameters

$field
The field name to sort
$sort
ASC or DESC

Returns

object
QueryBuilder
public object
# groupBy( string $field )

Add GROUP BY clause

Add GROUP BY clause

Parameters

$field
The field name

Returns

object
QueryBuilder
public object
# having( array $condition )

Create HAVING ... condition

Create HAVING ... condition

Parameters

$condition

The array of conditions

array( 'fieldName1' => $value1, 'fieldName2 >=' => $value2, 'fieldName3 => NULL )

OR

array( 'fieldName1' => $value1, 'fieldName2 >=' => $value2, 'fieldName3 => NULL, '$or' => array( 'fieldName4' => array(1, 2, 3) 'fieldName4 <' => 10 ) )

Returns

object
QueryBuilder
public object
# andHaving( array $condition )

Create AND HAVING ... condition

Create AND HAVING ... condition

Parameters

$condition
The array of conditions

Returns

object
QueryBuilder

See

LucidFrame\Core\QueryBuilder::$having
public object
# orHaving( array $condition = array() )

Create OR HAVING ... condition

Create OR HAVING ... condition

Parameters

$condition
The array of conditions

Returns

object
QueryBuilder

See

LucidFrame\Core\QueryBuilder::$having
public object
# limit( )

Add LIMIT clause

Add LIMIT clause

Returns

object
QueryBuilder
public object
# count( string $field = null, string $alias = null )

Add COUNT(*) or COUNT(field)

Add COUNT(*) or COUNT(field)

Parameters

$field
The field name
$alias
The alias field name to retrieve

Returns

object
QueryBuilder
public object
# max( string $field, string $alias = null )

Add MAX(field)

Add MAX(field)

Parameters

$field
The field name
$alias
The alias field name to retrieve

Returns

object
QueryBuilder
public object
# min( string $field, string $alias = null )

Add MIN(field)

Add MIN(field)

Parameters

$field
The field name
$alias
The alias field name to retrieve

Returns

object
QueryBuilder
public object
# sum( string $field, string $alias = null )

Add SUM(field)

Add SUM(field)

Parameters

$field
The field name
$alias
The alias field name to retrieve

Returns

object
QueryBuilder
public object
# avg( string $field, string $alias = null )

Add AVG(field)

Add AVG(field)

Parameters

$field
The field name
$alias
The alias field name to retrieve

Returns

object
QueryBuilder
protected object
# setAggregate( string $name, string $field = null, string $alias = null )

Aggregation

Aggregation

Parameters

$name
The function name COUNT, MAX, MIN, SUM, AVG, etc.
$field
The field name
$alias
The alias field name to retrieve

Returns

object
QueryBuilder
protected object
# buildSQL( )

Build SQL

Build SQL

Returns

object
QueryBuilder
protected string
# appendExistClauses( array $exists, string $sql )

Append EXISTS clauses to the SQL statement building

Append EXISTS clauses to the SQL statement building

Parameters

$exists
Array of exists clauses
$sql
The original SQL statement to be appended

Returns

string
public boolean|resource
# execute( )

Execute the query

Execute the query

Returns

boolean|resource
The result
public integer
# getNumRows( )

Get the number of rows in the query result

Get the number of rows in the query result

Returns

integer
Returns the number of rows in the result set.
public mixed
# fetchRow( integer $resultType = LC_FETCH_OBJECT )

Fetch a query result row

Fetch a query result row

Parameters

$resultType

The optional constant indicating what type of array should be produced. The possible values for this parameter are the constants LC_FETCH_OBJECT, LC_FETCH_ASSOC, or LC_FETCH_ARRAY. Default to LC_FETCH_OBJECT.

Returns

mixed
public array
# getResult( )

Perform a query on the database and return the array of all results

Perform a query on the database and return the array of all results

Returns

array

The result array of objects. If the result not found, return null.
public object|null
# getSingleResult( )

Perform a query on the database and return the result object

Perform a query on the database and return the result object

Returns

object|null

The result object If the result not found, return null.
public mixed
# fetch( )

Perform a query on the database and fetch one field only

Perform a query on the database and fetch one field only

Returns

mixed

The result If the result not found, return null.
public string
# getSQL( )

Get the built SQL

Get the built SQL

Returns

string
public string
# getReadySQL( )

Get the built SQL with the values replaced

Get the built SQL with the values replaced

Returns

string
public static boolean
# validateName( string $name )

Validate table name or field name

Validate table name or field name

Parameters

$name
The table name or field name to be validated

Returns

boolean
public static string
# quote( string $name )

Quote table name and field name

Quote table name and field name

Parameters

$name
The table name or field name or table.field

Returns

string
public static string
# raw( string $expression, array $values = array() )

Create raw expression string

Create raw expression string

Parameters

$expression
$values
The values to be replaced with specifier in $expression. See vsprintf.

Returns

string
public static array
# buildCondition( array $cond = array(), string $type = 'AND' )

Build the SQL WHERE clause from the various condition arrays

Build the SQL WHERE clause from the various condition arrays

Parameters

$cond

The condition array, for example

array( 'fieldName1' => $value1, 'fieldName2 >=' => $value2, 'fieldName3 => NULL )

$type
The condition type "AND" or "OR"; Default is "AND"

Returns

array

The built condition WHERE AND/OR [0] string The built condition WHERE AND/OR clause [1] array The values to bind in the condition
public static
# clearBindValues( )

Clear bind values

Clear bind values
public static array
# getBindValues( )

Get bind values

Get bind values

Returns

array
Constants summary
string EXP_CONDITION
# '__QueryBuilder::condition__'
string EXP_RAW
# '__QueryBuilder::raw__'
Properties summary
protected string $table

The table name

The table name
#
protected string $alias

The alias for the table

The alias for the table
#
protected array $joins

Collections of tables to join

Collections of tables to join
#
protected array $fields

Collections of fields to select

Collections of fields to select
#
protected array $where

Collection of conditions

Collection of conditions
#
protected array $exist

Collection of EXISTS clauses

Collection of EXISTS clauses
# array()
protected array $notExist

Collection of NOT EXISTS clauses

Collection of NOT EXISTS clauses
# array()
protected array $orderBy

Collection of fields to order

Collection of fields to order
#
protected array $groupBy

Collection of fields to group by

Collection of fields to group by
#
protected array $having

Collection of fields for having conditions

Collection of fields for having conditions
#
protected integer $offset

The offset for LIMIT

The offset for LIMIT
#
protected integer $limit

The row count for LIMIT

The row count for LIMIT
#
protected string $sql

The built SQL

The built SQL
#
protected array $aggregates

Collection of aggregates

Collection of aggregates
# array()
protected static array $bindValues

The values to sql to bind

The values to sql to bind
# array()
PHPLucidFrame 3.2 API documentation generated by ApiGen