Scope Methods

Scope and group your queries using these methods.

• table(string | array)

• columns(string | array)

• where(array)

• orderBy(string | array)

• groupBy(string | array)

• having()

• limit()

• offset()

table( string | array )

Set the name of the table to query.

$db->table('users');

Include a table alias using an array.

$db->table(['u' => 'users']);

columns( string $name | array $names)

Select which columns to return. Can be a single column or an array of columns.

$db->columns('email');

$db->columns(['id', 'email']);

where( array $conditions )

Filter the query using an array of keys and values. For example WHERE id = 1

$db->where(['id' => 1]);

or WHERE id IN (1,2)

$db->where(['id' => [1,2]]);

or Mysql functions like CURDATE() or DATE_SUB(). For example WHERE created = CURDATE()

$db->where(['created' => 'CURDATE()']);

or using operators in combination with the column key (separated by colon). For example: WHERE id >= 1 or WHERE email LIKE '%.com'.

$db->where(['id : >=' => 1]);

$db->where(['email : like' => '%.com']);

Supported operators =, !=, <, >, <=, >=, LIKE, NOT LIKE, IS, IS NOT. The default operator is =.

orderBy( string $column | array $columns )

Sort the results by one or more columns including sort order ORDER BY created DESC.

# single column sort
$db->orderBy('created DESC');

# Multiple column sort
$db->orderBy(['firstName', 'created DESC'])

groupBy( string $column | array $columns )

Group rows by columns.

Query

SELECT category, count(id) count FROM users GROUP BY category

$users = $db->table('users')->columns(['category', 'count(id) count'])->groupBy('category')->list();

JSON

[
  {
    "category": "client",
    "count": "2"
  },
  {
    "category": "partner",
    "count": "1"
  }
]