liftkit/database

Database library for LiftKit

LiftKit database library

Installation

composer require liftkit/database

Let's jump right in to some examples., _(*1)

Connection

Establish a connection

use LiftKit\Database\Connection\MySql;
use LiftKit\DependencyInjection\Container\Container;
use LiftKit\Database\Cache\Cache;
use PDO;

$connection = new MySql(
  new Container,
    new Cache,
    new PDO('connectionString', 'username', 'password')
);

Run a raw SQL query

$results = $connection->query(
  "
    SELECT *
    FROM tbl
  "
);

Using placeholders

$connection->query(
  "
    SELECT *
    FROM tbl
    WHERE col1 = ?
      AND col2 = ?
  ",
  [
    'val1',
    'val2',
  ]
);

Result objects

Loop through results

// NOTE: 
// Results are not loaded into memory. Instead they are
// wrapped by an object of the class
// \LiftKit\Database\Result\Result

$results = $connection->query(
    "
        SELECT *
        FROM tbl
    "
);

foreach ($results as $row) {
  echo 'column "name" = ' . $row['name'] . PHP_EOL;
  echo 'column "id" = ' . $row['id'] . PHP_EOL;
}

Fetch a single column as an array

foreach ($results->fetchColumn('id') as $id) {
    echo $id . PHP_EOL;
}
// '1'
// '2'
// ...

Fetch all rows as an array of entities

foreach ($results->fetchAll() as $row) {
    // Do something with $row['column']
}

Fetch all rows as an array of associative arrays

foreach ($results->flatten() as $row) {
    // Do something with $row['column']
}

Query builder

New query

use LiftKit\Database\Query\Query;

/**
 * @var Query $query
 */
$query = $connection->createQuery();

Simple select query

// SELECT field1, field2
// FROM tbl
// WHERE field1 = 'val1'

$results = $query->select('field1', 'field2')
  ->from('tbl')
  ->whereEqual('field1', 'val1')
  ->execute();

More complicated select query

Note that the method $connection->quoteIdentifier() is called on the right parameters. That's because the right parameter is expected to be a value. If it is instead a SQL identifier, it must be quoted., _(*2)

This example shows the query with MySQL style identifier quotes to illustrate the point. Note the difference between the JOIN conditions and the WHERE conditions., _(*3)

use LiftKit\Database\Query\Condition\Condition;

// SELECT `field1`, `field2`
// FROM `tbl`
// LEFT JOIN `other_tbl` ON (
//  `tbl`.`field1` = `other_tbl`.`field1`
//  OR `tbl`.`field2` > `other_tbl`.field2`
// )
// WHERE `tbl`.`field1` = 'val1'
//    OR `other_tbl`.`field2` = 'val2'
// GROUP BY `tbl`.`field3`, `tbl`.`field4`
// HAVING `tbl`.`field1` < 1
// ORDER BY `tbl`.`field5` ASC, `tbl`.`field6` DESC

$results = $query->select('field1', 'field2')
  ->from('tbl')
  ->leftJoin(
    'other_tbl',
    $connection->createCondition()
        ->equal(
            'tbl.field1',
            $connection->quoteIdentifier('other_tbl.field1')
        )
        ->orGreaterThan(
            'tbl.field2',
            $connection->quoteIdentifier('other_tbl.field2')
        )
  )
  ->whereEqual('tbl1.field1', 'val1')
  ->orWhereEqual('other_tbl.field2', 'val2')
  ->groupBy('tbl.field3')
  ->groupBy('tbl.field4')
  ->havingLessThan('tbl.field1', 1)
  ->orderBy('tbl.field5', Query::ORDER_ASC)
  ->orderBy('tbl.field6', Query::ORDER_DESC)
  ->execute();

Update query

Note that update queries can utilize conditions the same as select statements., _(*4)

// UPDATE tbl
// SET field2 = 'val2', field3 = 'val3'
// WHERE tbl.id = 2

$query->update()
  ->table('tbl')
  ->set(
    [
      'field2' => 'val2',
      'field3' => 'val3',
    ]
  )
  ->whereEqual('tbl.id', 2)
  ->execute();

Insert query

Insert queries return their insert ID., _(*5)

// INSERT INTO tbl
// SET field2 = 'val2', field3 = 'val3'

$id = $query->insert()
  ->into('tbl')
  ->set(
    [
      'field2' => 'val2',
      'field3' => 'val3',
    ]
  )
  ->execute();

Delete query

Note that delete queries can use conditions the same as select queries., _(*6)

// DELETE tbl.*
// FROM tbl
// WHERE id = 1

$query->delete()
  ->from('tbl')
  ->whereEqual('id', 1)
  ->execute();

Subqueries

Subqueries can be substituted pretty much anywhere a value or identifier can be., _(*7)

Note: This is also an example of how to use raw SQL instead of escaped values in your queries using the method $connection->createRaw(). Select arguments, like the left had side of conditions, will be quoted as an identifier unless otherwise specified., _(*8)

// SELECT *
// FROM tbl1
// WHERE
// ( SELECT COUNT(*)
//   FROM tbl2
//   WHERE tbl1.id = tbl2.tbl1_id
// ) = 1

$results = $query->select('*')
  ->from('tbl1')
  ->whereEqual(
    $connection->createQuery()
      ->select($connection->createRaw('COUNT(*)'))
      ->from('tbl2')
      ->whereEqual(
        'tbl1.id', 
        $connection->quoteIdentifier('tb2.tbl1_id')
      ),
    1
  )
  ->execute();

Composing parts of queries

This comes in hand for extracting away parts of queries you use often, while retaining the ability to combine them with other queries., _(*9)

Let's say you have a function that returns all of the rows from tbl., _(*10)

function getAllTblRows ()
{
  return $connection->createQuery()
    ->select('*')
    ->from('tbl')
    ->execute();
}

// SELECT *
// FROM tbl

$results = getActiveTblRows();

Now you need another query which select only records which are active from tbl. Notice the additions to getAllTblRows., _(*11)

function getAllTblRows (Query $inputQuery = null)
{
  return $connection->createQuery()
    ->select('*')
    ->from('tbl')
    ->composeWith($inputQuery)
    ->execute();
}

function getActiveTblRows ()
{
  $query = $connection->createQuery()
    ->whereEqual('active', 1);

  return getAllTblRows($query);
}

// SELECT *
// FROM tbl
// WHERE active = 1

$results = getActiveTblRows();

Table objects

Table objects are meant to reduce the boilerplate you need to place in your query builder queries., _(*12)

Fetching multiple rows

use LiftKit\Database\Schema\Schema;
use LiftKit\Database\Schema\Table\Table;

// We'll get back to schemas in a moment

$table = new Table(
    $connection,
    new Schema($connection),
    'tbl'
);

// SELECT *
// FROM tbl

$results = $tbl->getRows();

Fetching multiple rows with composed query

// SELECT *
// FROM tbl
// WHERE active = 1

$results = $table->getRows(
    $connection->createQuery()
        ->whereEqual('active', 1)
);

Fetching a single row

// SELECT *
// FROM tbl
// WHERE id = 1
// LIMIT 1

$row = $table->getRow(1);

// 'val1'
echo $row['field1'];

// 'val2'
echo $row['field2'];

Inserting a new row

````php // INSERT INTO tbl // SET field1 = 'val1', field2 = 'val2', <sub>(*13)</sub>

$id = $table->insertRow( [ 'field1' => 'val1', 'field2' => 'val2', ] );, <sub>(*14)</sub>

### Updating a row

NOTE: The library will auto-detect the primary key column and create an equal condition on that column.

```php

// UPDATE tbl
// SET field1 = 'val1', field2 = 'val2'
// WHERE id = 1

$table->updateRow(
    [
        'id'     => 1,
        'field1' => 'val1',
        'field2' => 'val2',
    ]
);

Deleting a row

// DELETE FROM tbl
// WHERE id = 1

$table->deleteRow(1);

More info on table objects, relations, and entities coming soon!, <sub>(*15)</sub>