basic usage

Basic usage

Basic usage of Connection:

$connection->query('INSERT INTO article', array( 
    'title' => 'Blabla',
    'content' => fopen('/some/file', 'r'),
    'published_at' => new DateTime(),
    'user_id' => 1
)); // it is even possible to use multiple inserts (wrapped in array)

$connection->query('UPDATE users SET ? WHERE id=?', $assocArrData, $id);

$connection->query('SELECT * FROM categories WHERE id=?', 123)->fetch();

Usage of Selections

Basics

Selections provide a layer that simplifies work with database and tries to optimize number of performed database queries.

$books = $connection->table('book')->where('title LIKE ?', '%nette%'); // db table name is "book"

//lets iterate over all books that have been found
foreach ($books as $book) { //$book is an instance of Flunorette\ActiveRow
    echo $book->title;

    //we can simply get book's author by
    echo $book->ref('author')->name; // or simplier $book->author->name
    
    //or tags assigned to the book
    foreach ($book->related('book_tag') as $bookTag) {
        echo $bookTag->tag->name . ', ';
    }    
}

The example above will performs only these 4 queries:

SELECT `book`.* FROM `book`
SELECT `author`.* FROM `author` WHERE (`author`.`id` IN (11, 12))
SELECT `book_tag`.* FROM `book_tag` WHERE (`book_tag`.`book_id` IN (1, 4, 2, 3))
SELECT `tag`.* FROM `tag` WHERE (`tag`.`id` IN (21, 22, 23))

Filtering

$selection->where('id', 1); // id = 1
$selection->where('id', [1, 2, 3]); // id IN (1, 2, 3)
$selection->where('name = ?', 'John'); // name = 'John'
$selection->where('born > ?', new DateTime('1986-01-01')); // born > '1986-01-01 00:00:00'
$selection->where('born', null); // born IS NULL
$selection->where('title LIKE ?', '%nette%'); // title LIKE '%nette%'

// id = 1 AND name = 'John'
$selection->where('id', 1)->where('name', $name);
$selection->where('id = ? AND name = ?', 1, $name);

// id = 1 OR name = 'John'
$selection->where('id = ? OR name = ?', 1, $name);

// following entries are equal
$selection->where('id = ? OR id = ?', 1, 2);
$selection->where('id ? OR id ?', 1, 2);

// more examples
$selection->where('NOT id', 1);
$selection->where('id NOT', 1); // the same

$ids = array();
$selection->where('id', $ids);          // id IS NULL AND FALSE
$selection->where('id NOT', $ids);      // id IS NULL OR FALSE
$selection->where('NOT (id ?)', $ids);  // NOT (id IS NULL AND FALSE)

// this will throws an exception, this syntax is not supported
$selection->where('NOT id ?', $ids);

// you can filter even by an another selection
$selection->where('id', $anotherSelection);

// automatically creates a join to table2
$selection->where('table2.name', 'John'); 
// works as well
$selection->where('m_n_table:table3.name', 'John'); // notice the colons - they represent backjoin

API

• API uses fluent interface:* ->where()->where()->select()->...
• You can pass SqlLiteral or SelectQuery as parameters

api	description
$table->where($where[, $param[, ...]])	Set WHERE
$table->order($columns)	Set ORDER BY, can be expression ('column DESC, id DESC')
$table->select($columns)	Set retrieved columns, can be expression ('col, MD5(col) AS hash')
$table->limit($limit[, $offset])	Set LIMIT and OFFSET
$table->group($columns)	Set GROUP BY
$table->having($having)	Set HAVING
$table->join($join)	Set JOIN
$table->leftJoin($join)	Set LEFT JOIN

Possible where() method arguments:

api	description
$table->where("field", $value)	field = $value
$table->where("field", NULL)	field IS NULL
$table->where("field > ?", $val)	field > $val
$table->where("field", array(1, 2))	field IN (1, 2)
$table->where("field", $conn->table($tableName))	field IN (SELECT $primary FROM $tableName)
$table->where("field", $conn->table($tableName)->select('col'))	field IN (SELECT col FROM $tableName)

Aggregate queries can also be created:

api	description
$table->count("*")	Get number of rows
$table->count("DISTINCT $column")	Get number of distinct values
$table->min($column)	Get minimum value
$table->max($column)	Get maximum value
$table->aggregation("GROUP_CONCAT($column)")	Run any aggregation function

Fetching data:

api	description
foreach ($table as $id => $row)	Iterate all rows in result
$row = $table->get($id)	Get single row with ID $id from table
$row = $table->fetch()	Get next row from the result
$array = $table->fetchPairs($key, $value)	Fetch all values to associative array
$array = $table->fetchPairs($key)	Fetch all rows to associative array
count($table)	Get number of rows in result set