Skip to content
Damien edited this page Apr 23, 2020 · 5 revisions

Detailed Shortcut Methods

The detailed shortcut methods outlined below are used in the SQL methods within the library.

All examples below will be based off an example table as so

| id | name        | email             | phone  | country | active |
|----|-------------|-------------------|--------|---------|--------|
| 1  | john john   | [email protected]  | 123456 | US      | 1      |
| 2  | john doe    | [email protected] | 345678 | US      | 1      |
| 3  | scott brown | [email protected] | 123654 | NZ      | 1      |
| 4  | jane smith  | [email protected]  | 345654 | UK      | 0      |
| 5  | sarah davis | [email protected] | 123678 | AU      | 1      |
| 6  | david brown | [email protected]  | 987456 | UK      | 0      |

For more information on connecting, look at the example connections in the installation section of the wiki

All the examples below were run with prepare statement support on

SQL Methods

groupBy


groupBy -- A standard function that can be used inside the selecting() method

Description

array groupBy (string columns)

groupBy() adds a GROUP BY clause into your SELECT statement. The value(s) entered in here are a string value column name.

Example

// SQL Select with `groupBy()`
$db->selecting(
  'profile',
  'COUNT(ID), country',
  groupBy('country')
);

// Will result in
"SELECT COUNT(ID), country FROM profile GROUP BY country"

// Results
| COUNT(ID) | country |
|-----------|---------|
| 1         | AU      |
| 1         | NZ      |
| 2         | UK      |
| 2         | US      |

having


having -- A standard function that can be used inside the selecting() method

Description

array having (array conditions)

having() adds a HAVING clause into your SELECT statement. The value(s) entered in here are the same comparisons entered into a where() function.

Example

// SQL Select with `having()`
$db->selecting(
  'profile',
  'COUNT(ID), country',
  groupBy('country'),
  having(
    gt('COUNT(ID)', 1)
  )
);

// Will result in
"SELECT COUNT(ID), country FROM profile GROUP BY country HAVING COUNT(ID) > '1'"

// Results
| COUNT(ID) | country |
|-----------|---------|
| 1         | AU      |
| 1         | NZ      |
| 2         | UK      |
| 2         | US      |

Combine Operators

Combine operators are used to join comparison expressions together. The default value for most of these will be _AND

  • _AND will result in AND
  • _OR will result in OR
  • _NOT will result in NOT
  • _andNOT will result in AND NOT

Conditions / Comparison Expressions

The variadic ...$whereConditions, and ...$conditions parameters, represent the following global functions. They are comparison expressions returning an array with the given arguments, the last arguments of _AND, _OR, _NOT, _andNOT will combine expressions

Clone this wiki locally