Statements

Statement

pine3ree\Db\Sql\Statement

The SQL statement classes abstract full sql statements with named placeholder markers in place of the actual parameter values. They are composed of simpler SQL elements such as identifiers, aliases, expressions, predicates and clauses.

Statement are actually sql-statement builders and provides methods for adding the inner elements they are composed of.

Supported statements are Select for DQL, and Insert, Update, Delete for DML, reflecting the previously examined database command classes. The sql-building methods used in a command instance are proxies to corresponding methods of the composed sql-statement instance.

Note: The following examples replicates the exampes previously provided for their corresponding commands. The examples also outputs sql-strings as generated by the default ANSI sql-driver

Sql::select()

Create a pine3ree\Sql\Statement\Select sql statement

use pine3ree\Db\Sql;

/** @var Db $db */

$select = Sql::select(); // create a generic empty sql statement instance

// SELECT * FROM "product"
$select = Sql::select('*', 'product');
$select = Sql::select('*')->from('product');
$select = Sql::select(null, 'product');
$select = Sql::select()->from('product');

// Use table alias: SELECT "p".* FROM "product" "p"
$select = Sql::select('*', 'product', 'p');
$select = Sql::select('*')->from('product', 'p');
$select = Sql::select()->from('product', 'p');

 // SELECT "p"."price", "p"."vat_rate" AS "vatRate" FROM "product" "p"
$select = Sql::select(['price', 'vatRate' => 'vat_rate'])->from('product', 'p');

// Add where condition LessThanEqual and order-by clause
$select->where->lte('price', 1000.0); // WHERE "price" <= :lte1 (named parameter marker)

// ORDER BY "p"."price" ASC
$select->orderBy('p.price', 'ASC');
$select->orderBy('p.price', Sql::ASC);

// ORDER BY "price" ASC, "vat_rate" DESC
$select->orderBy([
    'price' => Sql::ASC, // or just 'price' => 'ASC'
    'vat_rate' => Sql::DESC, // or just 'vat_rate' => 'DESC'
]);

// 1. SQL-string generated via the default singleton ANSI driver
$sql = $select->getSQL();

// 2. Using a different sql-driver, assuming we have an active PDO instance
$driver = new Sql\Driver\MySQL($pdo);
$sql = $select->getSQL($driver); // e.g. SELECT `p`.* FROM `product` `p`

// 2. Using an external accumulator
$select = Sql::select();
$select->from('user')->where->eq('id', 42);
$sql = $select->getSQL(null, $params);
$params->getValues(); // [':eq1' => 42]


// SELECT
//    "category_id" AS "catId",
//    COUNT(*) AS "numProducts"
// FROM "product" WHERE "price" > :gt1
// GROUP BY "category_id"
// HAVING "numProducts" >= :gte1
$select = Sql::select()
    ->column('category_id', 'catId')
    ->count('*', 'numProducts')
    ->from('product');
    ->where->gte('price', 10.0);
// using $select->where or $select->having changes the scope and the fluent interface
// method chain is broken

// Add a GROUP BY
// GROUP BY "category_id" HAVING "numProducts" < :lte1
$select->groupBy('category_id')
    ->having->lte('numProducts', 5);

// SELECT MIN("price") FROM "product" GROUP BY "category_id"
$select = Sql::select()->min('price')->from('product')->groupBy('category_id');

Sql::insert()

Create a pine3ree\Sql\Statement\Insert sql statement

// INSERT INTO "product" ("name", "price") VALUES (:val1, :val2)
$insert = Sql::insert()
    ->into('product')
    ->row([
        'name' => 'product-1',
        'price' => 100.00,
    ]);

// equivalent to
$insert = Sql::insert()
    ->into('product')
    ->columns(['name', 'price'])
    ->values(['product-1', 100.00]);

Insert many rows:

// INSERT INTO "product" ("name", "price") VALUES (:val1, :val2), (:val3, :val4)
Sql::insert()
    ->into('product')
    ->rows([
        [
            'name' => 'product-111',
            'price' => 111.11,
        ],
        [
            'name' => 'product-222',
            'price' => 222.22,
        ],
    ]);

// equivalent to
Sql::insert()
    ->into('product')
    ->row([
        'name' => 'product-111',
        'price' => 111.11,
    ])
    ->row([
        'name' => 'product-222',
        'price' => 222.22,
    ], true); // The TRUE argument add rows to existing insert-rows instead of replacing them

// and to
Sql::insert()
    ->into('product')
    ->columns(['name', 'price'])
    ->values([
        'product-111',
        111.11,
    ])
    ->values([
        'product-222',
        222.22,
    ], true); // The TRUE argument add values to existing values instead of replacing them

// and to
Sql::insert()
    ->into('product')
    ->columns(['name', 'price'])
    ->multipleValues([
        [
            'product-111',
            111.11,
        ],
        [
            'product-222',
            222.22,
        ],
    ];

By default - Insert::values(array $values, bool $add = false) and - Insert::row(array $row, bool $add = false) and - Insert::rows(array $rows, bool $add = false) and - Insert::multipleValues(array $multiple_values, bool $add = false)

will define the insert values removing any previously accumulated set of values.

The opposite happens for - Insert::values(array $values, bool $add = true) and - Insert::row(array $row, bool $add = true) and - Insert::rows(array $rows, bool $add = true) and - Insert::multipleValues(array $multiple_values, bool $add = true)

These methods calls will add the new rows/values provided to the existing ones.

$insert = Sql::insert('product');

$insert->row(['price' => 111.11, 'stock' => 111]); // Adds 1 set of values
$insert->row(['price' => 222.22, 'stock' => 222], true); // Adds 1 set of values
// Columns "price" and "stock" are already specified by previous row() calls
$insert->values([333.33, 333], true); // Adds another set of values

 // Set the insert values after removing the old ones
$insert->row(['price' => 555.55, 'stock' => 555]);

Sql::update()

The pine3ree\Sql\Statement\Update class abstracts a SQL INSERT statement

A non empty condition/predicate is required, otherwise an exception is thrown.

Examples:

// UPDATE "product" SET "published" = :set1 WHERE stock > 0
$update = Sql::update()->table('product')->set('published', true)->where('stock > 0');
$update = Sql::update('product')->set('published', true)->where('stock > 0');

// Update all rows
// UPDATE "articles" SET "published" = :set1 WHERE TRUE, we use the literal "TRUE" to update all records
$update = Sql::update('articles')->set('published', false)->where("TRUE");

Sql::delete()

The pine3ree\Sql\Statement\Delete class abstracts a SQL DELETE statement

A non empty condition/predicate is required, otherwise an exception is thrown.

Examples:

// DELETE FROM "product" WHERE stock <= 0
$delete = Sql::delete()->from('product')->where('stock <= 0');
$delete = Sql::delete('product')->where('stock <= 0');