Database Class Methods

select() method

Purpose:
The select() method is for choosing the columns you want to return in your query.

Format:

select(column_name | array(column_name1, column_name2, ...))

Examples:

// Grab a single column
$db->select('id')

// OR grab multiple columns
$db->select('id')
   ->select('first_name')

// OR grab multiple columns by passing in an array
$db->select(['id', 'first_name', 'last_name'])

distinct() method

Purpose:
The distinct() method is for limiting the results returned so that there's no duplicates.

Format:

distinct()

Examples:

// The distinct method takes no parameters, you just call it when constructing your query.
$db ->select('first_name')
    ->distinct()
    ->from('users');

from() method

Alias:
This method is an alias of table().

Purpose:
The from() method is for selecting the table(s) you want to return or delete data from.

Format:

from(table_name | array(table_name1, table_name2, ...))

Examples:

// Grab a single table
$db->from('users')

// OR join multiple tables
$db->from('users')
   ->from('user_profiles')

// OR grab multiple tables by passing in an array
$db->from(['users', 'user_profiles'])

join() method

Purpose:
The join() method is for joining multiple tables together.

Format:

// f_table = Foreign Table Name
// f_column = Foreign Column Name
join(f_table, array(array(table.column, operator, f_table.f_column)[, ...]) [, join_type ])

Examples:

// Join a table when a certain single condition is met, using the default INNER join
$db->join('members', [['users.user_id', '=', 'members.user_id']])

// Join a table using a specifc type of join
$db->join('members', [['users.user_id', '=', 'members.user_id']], 'OUTER')

// Join a table when multiple conditions are met
$db->join('members', [
            ['Orders.customerID', '=', 'Customers.customerID'],
            ['User.type', '=', 'Customer.type']
        ], 'OUTER')

// Apply multiple joins together
$db ->join('members', [['users.user_id', '=', 'members.user_id']])
    ->join('locations', [['users.location_id', '=', 'locations.id']])

where() method

Purpose:
The where() method is for limiting the records that get returned to only those that pass certain conditions.

Format:

where(column_name, value [, operator] | array(array(column_name, operator, value [, conjunction])))

Examples:

// Only return results where name = 'bob'
$db->where('name', 'bob')

// Only return results where the date is greater than or equal to january first 2014
$db->where('date', '2014-01-01', '>=')

// If the above format of passing in the operator as an optional 3rd parameter bothers you,
// you can use the array format which uses the [column, operator, value] ordering.
// Note that you need the double brackets though because it supports multiple statements.
$db->where([['date', '>=', '2014-01-01']])

// Only return results where the date is greater than or equal to january first 2014
// and less than january 2016
$db ->where('date', '2014-01-01', '>=')
    ->where('date', '2016-01-01', '<')

// Only return results where name = 'bob' OR name = 'jeff'
$db ->where('name', 'bob')
    ->orWhere('name', 'jeff')

// Pass in an array...
// Only return results where a user's name starts with 's' or was created after Jan 2016,
// and the user is of type 'admin' or 'moderator'
$db ->where([
        ['created_time', '>=', '2016-01-01', 'OR'],
        ['name', 'LIKE', 's%']
    ])
    ->where([
        ['type', '=', 'admin', 'OR'],
        ['type', '=', 'moderator']
    ])  
// Using the MySQL adaptor, the above produces:
// WHERE (created_time >= '2016-01-01' OR name LIKE 's%') AND (type = 'admin' OR type = 'moderator')

orWhere() method

Purpose:
The orWhere() method is for limiting the records that get returned to only those that pass certain conditions.

Similar To:
It works identically to the where() method except any conditions passed to it will be conjoined with 'OR' to any other Where declarations. See the where() section for more examples.

Examples:

// Only return users who are named bob and also admins, or users named jeff
$db ->where([
        ['name', 'bob'],
        ['type', 'admin']
    ])
    ->orWhere('name', 'jeff')
// Using the MySQL adaptor, the above produces:
// WHERE (name = 'bob' AND type = 'admin') OR (name = 'jeff')

in() method

Purpose:
The in() method is for checking if a column's value exists in another set of data.

Format:

in(column, value | string(value1, value2, value3, ...) | array(value1, value2, value3, ...))

Examples:

// Check if column value matches a single value (same as using where statement)
$db ->in('name', 'bob')

// Check if column value is present in a comma separated list
// Note: The list gets translated into an array by the Database class.
$db ->in('name', 'bob, jeff, susan')

// Check if column value is present in an array of values
$db ->in('name', ['bob', 'jeff', 'susan'])

// NOT RECOMMENDED as it breaks cross-database functionality, but possible
$db ->in('name', 'SELECT name FROM users WHERE name LIKE s%')
// The better way of doing the above is to do a query to grab all the names that start with 's',
// then pass the result set as an array to the in() method. This would preserve cross-database
// functionality and not lock you into databases that support your SQL statement.

groupBy() method

Purpose:
The groupBy() method is for combining data together.

Format:

groupBy(column | array(column1, column2, ...))

Examples:

// Group by a single column
$db->groupBy('last_name')

// OR group by multiple columns
$db->groupBy('last_name')
   ->groupBy('location')

// OR group by multiple columns by passing in an array
$db->groupBy(['last_name', 'location'])

having() method

Purpose:
The having() method is for further filtering the results returned. where() performs row level checks, having() performs checks on the result set that gets returned after row level conditions are satisfied. It's for filtering based on aggregates which where() can't do.

Format:

having(column_name, value [, operator] | array(array(column_name, operator, value [, conjunction])))

Examples:
having() follows the same format as where(), so feel free to look at the examples there if you need to. In order to understand more about the SQL Having keyword, see this example from w3schools.com

orHaving() method

Purpose:
Joins the given condition(s) with any previous condition statements joined by 'OR'.

Follows the same format as having(). For examples see the syntactially identical orWhere() method.

orderBy() method

Purpose:
The orderBy() method is for ordering the returned results from your query.

Format:

// sort_direction = DESC (descending) | ASC (ascending)
orderBy(column_name, sort_direction)

Examples:

// Specifying a single column to order by...
$db ->orderBy('name', 'ASC')

// Specifying multiple columns to order by...
$db ->orderBy('users.name', 'DESC')
    ->orderBy('locations.name', 'DESC')

limit() method

Purpose:
The limit() method is for limiting the number of returned results from a query.

Format:

limit(number)

Examples:

// Limit the number of result rows to 10
$db ->limit(10)

offset() method

Purpose:
The offset() method is for specifying how many result rows should be skipped when returning a query. It's most commonly used in conjunction with limit() to perform pagination.

Format:

offset(number)

Examples:

// Skip returning the first 10 results from a query's result set.
$db ->offset(10)

// Return results 11-20 from a large result set
$db ->limit(10)
    ->offset(10)

// Return results 21-30 from a large result set
$db ->limit(10)
    ->offset(20)