PHP code example of phpvv / db

1. Go to this page and download the library: Download phpvv/db library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php.
    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

phpvv / db example snippets


use App\Db\MainDb;
use VV\Db\Param;
use VV\Db\Sql;

$db = MainDb::instance();

$categoryId = 102;
$stateParam = Param::int(value: 1, name: 'state');

$query = $db->tbl->product
    ->select('title', 'b.title brand', 'price', 'c.title as color') // SELECT p.title, ... FROM tbl_product as p
    ->join($db->tbl->brand)                                         // INNER JOIN tbl_brand as b ON b.brand_id = p.brand_id
    ->left($db->tbl->color, 'p')                                    // LEFT JOIN tbl_color as c ON c.color_id = p.color_id
    // ->left($db->tbl->color, on: 'c.color_id = p.color_id', alias: 'c') // same as above
    ->where( // WHERE ...
        Sql::condition()
            ->exists(                                               // WHERE EXISTS (
                $db->tbl->productsCategories->select('1')               // SELECT 1 FROM tbl_products_categories pc
                ->where('`pc`.`product_id`=`p`.`product_id`')           // WHERE ("pc"."product_id"="p"."product_id")
                ->where([
                    'category_id' => $categoryId,                       // AND pc.category_id = :p1
                    'state' => $stateParam                              // AND pc.state = :state
                ])
            )                                                       // )
            ->and('state')->eq($stateParam)                         // AND p.state = :state
            // ->and('title')->like('Comp%')
            ->and('title')->startsWith('Comp')                      // AND p.title LIKE :p2 -- 'Comp%'
            ->and('price')
                ->gte(1000)                                         // AND p.price >= :p3
                ->and
                ->lte(2000)                                         // AND p.price <= :p4
            ->and('weight')->between(7000, 15000)                   // AND p.weight BETWEEN :p5 AND :p6
            ->and(                                                  // AND (
                Sql::condition('width') // nested condition
                    ->lt(500)                                           // p.width > :p7
                    ->or                                                // OR
                    ->isNull()                                          // p.width IS NULL
            )                                                       // )
            ->and('c.color_id')->in(1, 5, null)                     // AND (c.color_id IN (:p8, :p9) OR c.color_id IS NULL)
            ->and('brand_id')->not->in(3, 4)                        // AND (p.brand_id NOT IN (:p10, :p11) OR p.brand_id IS NULL)
            ->and('height')->isNotNull()                            // AND p.height IS NOT NULL
    )
    // ->groupBy(...)
    // ->having(...)
    ->orderBy(                                                      // ORDER BY
        Sql::case('p.color_id')
            ->when(5)->then(1)                                      // color_id = 5 - first
            ->when(1)->then(2)                                      // color_id = 1 - second
            ->else(3),                                              // other colors at the end - first
        // Sql::case()
        //     ->when(Sql::condition('p.color_id')->eq(5))->then(1)    // same CASE as above
        //     ->when(['p.color_id' => 1])->then(2)
        //     ->else(3),
        '-price',                                                   // price DESC,
        'title'                                                     // title ASC
    )
    ->limit(10);                                                    // LIMIT 10


echo "SQL:\n", $query->toString(), "\n\n";

// $rows = $query->rows;
// $rows = $query->rows(\VV\Db::FETCH_NUM);
// $rows = $query->rows(null, keyColumn: 'product_id', decorator: function (&$row, &$key) => { /*...*/ });
// $row = $query->row;
// $row = $query->row(\VV\Db::FETCH_NUM | \VV\Db::FETCH_LOB_NOT_LOAD);
// $title = $query->column;
// $brand = $query->column(1);

/*
$statement = $query->prepare();
print_r($statement->result()->rows);
$stateParam->setValue(0);
print_r($statement->result()->rows);
$statement->close();
*/

echo "rows:\n";
foreach ($query->result as $row) {
    print_r($row);
}

use App\Db\MainDb;
use VV\Db\Param;

$db = MainDb::instance();

$userId = 1;
$cart = [
    // productId => quantity
    10 => rand(1, 2),
    20 => rand(1, 3),
    40 => rand(1, 5),
];


$productIterator = $db->tbl->product->select('product_id', 'price')
    ->whereIdIn(...array_keys($cart))
    ->result(\VV\Db::FETCH_NUM);

$transaction = $db->startTransaction();
try {
    $orderId = $db->tbl->order->insert()
        ->set([
            'user_id' => $userId,
            'date_created' => new \DateTime(),
        ])
        ->insertedId($transaction);

    // variants:
    switch (3) {
        case 1:
            // build and execute queries for each item
            foreach ($productIterator as [$productId, $price]) {
                $db->tbl->orderItem->insert()
                    ->set([
                        'order_id' => $orderId,
                        'product_id' => $productId,
                        'price' => $price,
                        'quantity' => $cart[$productId],
                    ])
                    ->exec($transaction);
            }
            break;
        case 2:
            // build and execute one query for all items
            $insertItemQuery = $db->tbl->orderItem->insert()
                ->columns('order_id', 'product_id', 'price', 'quantity');

            foreach ($productIterator as [$productId, $price]) {
                $insertItemQuery->values($orderId, $productId, $price, $cart[$productId]);
            }
            $insertItemQuery->exec($transaction);
            break;
        case 3:
            // prepare query and execute it for each item
            $prepared = $db->tbl->orderItem->insert()
                ->set([
                    'order_id' => Param::int($orderId),
                    'product_id' => $productIdParam = Param::str(size: 16),
                    'price' => $priceParam = Param::str(size: 16),
                    'quantity' => $quantityParam = Param::str(size: 16),
                ]);

            foreach ($productIterator as [$productId, $price]) {
                $productIdParam->setValue($productId);
                $priceParam->setValue($price);
                $quantityParam->setValue($cart[$productId]);

                $prepared->exec($transaction);
            }
            break;
    }

    $db->tbl->order->update()
        ->set(
            'amount',
            $db->tbl->orderItem->select('SUM(price * quantity)')->where('order_id=o.order_id')
        )
        ->whereId($orderId)
        // ->exec() // throws an exception that you are trying to execute statement
                    // outside of transaction started for current connection
        ->exec($transaction);

    // you can execute important statement in transaction free connection
    $db->tbl->log->insert()
        ->set(['title' => "new order #$orderId"])
        ->exec($db->getFreeConnection());

    // throw new \RuntimeException('Test transactionFreeConnection()');

    $transaction->commit();
} catch (\Throwable $e) {
    $transaction->rollback();
    /** @noinspection PhpUnhandledExceptionInspection */
    throw $e;
}

use APP\DB\MAIN as CONF;
use VV\Db\Connection;
use VV\Db\Pdo\Driver;

// $driver = new \VV\Db\Oci\Driver;
// $driver = new \VV\Db\Mysqli\Driver;
$driver = new Driver(Driver::DBMS_POSTGRES);

$connection = new Connection($driver, CONF\HOST, CONF\USER, CONF\PASSWD, CONF\DBNAME);
// $connection->connect(); // auto connect on first query is enabled by default

// all variants do same job:
$queryString = 'SELECT product_id, title FROM tbl_product WHERE price > :price';
$result = $connection->query($queryString, ['price' => 100]);
// or
$result = $connection->prepare($queryString)->bind(['price' => 100])->result();
// or
$result = $connection->select('product_id', 'title')->from('tbl_product')->where('price > ', 100)->result();

print_r($result->rows);

namespace App\Db;

use APP\DB\MAIN as CONF;
use VV\Db\Connection;
use VV\Db\Pdo\Driver;

/**
 * @method MainDb\TableList tables()
 * @method MainDb\ViewList views()
 * @property-read MainDb\TableList $tbl
 * @property-read MainDb\TableList $vw
 */
class MainDb extends \VV\Db {

    public function createConnection(): Connection {
        $driver = new Driver(Driver::DBMS_POSTGRES);

        return new Connection($driver, CONF\HOST, CONF\USER, CONF\PASSWD, CONF\DBNAME);
    }
}

use App\Db\MainDb;
use VV\Db\Model\Generator\ModelGenerator;

(new ModelGenerator(MainDb::instance()))->build();

use App\Db\MainDb;

$db = MainDb::instance();

$products = $db->tbl->product
    ->select('product_id', 'b.title brand', 'title', 'price')
    ->join($db->tbl->brand)
    ->where('brand_id', 1)
    ->where('price >', 100)
    ->rows;

print_r($products);

use App\Db\MainDb;

$db = MainDb::instance();
$connection = $db->getConnection(); // or $db->getFreeConnection();
// $connection = new \VV\Db\Connection(...);

$columns = ['product_id', 'b.title brand', 'title', 'price'];

// from `Connection` directly:
$selectQuery = $connection->select(...$columns)->from('tbl_product');
// from `Db`:
$selectQuery = $db->select(...$columns)->from('tbl_product');
// from `Table` (recommended):
$selectQuery = $db->tbl->product->select(...$columns);

$query = $db->tbl->product->select('product_id', 'title')->whereId(10);

$row = $query->result->row/*($flags)*/);
$productId = $query->result->cell/*($columnIndex[, $flags])*/);

$query = $db->tbl->product->select('product_id', 'title', 'b.title brand')->join($db->tbl->brand)->limit(3);

$result = $query->result;
while (['product_id' => $productId, 'title' => $title] = $result->fetch()) {
    echo "$productId: $title\n";
}
// or
$rowIterator = $query->result(Db::FETCH_NUM);
foreach ($rowIterator as [$productId, $title, $brand]) {
    echo "$productId: $brand $title\n";
}
// or
$rows = $query->result->rows/*($flags)*/;
// or
$assoc = $query->result()->assoc/*(keyColumn: 'product_id'[, valueColumn: 'title'])*/;
//

use VV\Db;

$rows = $query->rows(
    Db::FETCH_ASSOC   // column name as key; default fetch mode (if $flags === null)
    | Db::FETCH_NUM   // column index as key
    | Db::FETCH_LOB_OBJECT // return LOB object instead of LOB content (only for Oracle yet)
);

$query = $db->tbl->product->select('product_id', 'title', 'brand_id')->limit(3);

$assocRows = $query->rows;
$numRows = $query->rows(Db::FETCH_NUM);

$decoratedRows = $query->rows(decorator: function (&$row, &$key) {
    $key = $row['product_id'] . '-' . $row['brand_id'];
    $row = array_values($row);
});

$assoc = $query->rows(keyColumn: 'product_id', decorator: 'title');
$assoc = $query->assoc;

$assocRow = $query->row;
$bothRow = $query->row(Db::FETCH_NUM | Db::FETCH_ASSOC);

$productIdList = $query->column;
$titleList = $query->column(1);

$productId = $query->cell;
$title = $query->cell(1);

$query = $db->tbl->product->select()
    ->columns('product_id', 'brand_id')
    ->addColumns('title', 'price');

$query = $db->tbl->product->select(
    'product_id',
    'title product',
    $db->tbl->brand
        ->select('title')
        ->where('b.brand_id = p.brand_id')
        ->as('brand'),
    'price',
    Sql::case()
        ->when(['price >= ' => 1000])->then(1)
        ->else(0)
        ->as('is_expensive'),
);

$query = $db->tbl->product->select(/*...*/);
// or
$query = $db->select(/*...*/)->from($db->tbl->product); // same as above
// or
$query = $db->select(/*...*/)->from('tbl_product'); // not same as above regarding JOIN clause

$query = $db->tbl->product->select(/*...*/)->mainTableAs('prod');

$query = $db->tbl->orderItem->select(/*...*/)  // SELECT ... FROM "tbl_order_item" "oi"
    ->join($db->tbl->order)                    // JOIN "tbl_order" "o" ON "o"."order_id" = "oi"."order_id"
    ->left($db->tbl->orderState)               // LEFT JOIN "tbl_order_state" "os" ON "os"."state_id" = "o"."state_id"
    ->join($db->tbl->product, 'oi')            // JOIN "tbl_product" "p" ON "p"."product_id" = "oi"."product_id"
    ->where('o.state_id', 1);                  // WHERE "o"."state_id" = ?

$query = $db->tbl->orderItem->select(/*...*/)
    ->join(
        $db->tbl->order,
        'order.order_id=oi.order_id', // string
        'order'
    )
    ->left(
        $db->tbl->orderState,
        Sql::condition()              // Condition object
            ->and('os.state_id')->eq(Sql::expression('o.state_id'))
            ->and('os.state_id')->eq(1)
    );

$query = $db->tbl->orderItem->select(/*...*/)
    ->join($db->tbl->order)
    ->join($db->tbl->product, 'oi'); // join to tbl_order_item (not tbl_order) by product_id field

$query = $db->tbl->orderItem->select(/*...*/)
    ->join($db->tbl->order, '.foo_id'); // "o"."order_id" = "oi"."foo_id"

$query = $db->tbl->orderItem->select(/*...*/)
    ->join($db->tbl->order)
    ->join($db->tbl->product, 'oi.foo_id'); // "p"."product_id" = "oi"."foo_id"

$query = $db->tbl->productCategory->select(/*...*/)
    //->joinParent('pc2', 'pc', 'parent_id') // same as below
    ->joinParent('pc2'); // JOIN "tbl_product_category" "pc2" ON ("pc2"."category_id" = "pc"."parent_id")

$query = $db->tbl->order->select(/*...*/)
    ->joinBack($db->tbl->orderItem); // JOIN "tbl_order_item" "oi" ON ("oi"."item_id" = "o"."order_id")

$query = $db->tbl->product->select('product_id', 'price', 'weight')
    ->addNestedColumns('brand', 'b.brand_id', 'b.title') // first argument is nesting path: string|string[]
    ->addNestedColumns(['nested', 'color'], 'c.color_id', 'c.title')
    ->addNestedColumns(['nested', 'size'], 'width', 'height', 'depth')
    ->join($db->tbl->brand)
    ->join($db->tbl->color, 'p');

print_r($query->row);

$query = $db->tbl->orderItem->select('item_id', 'price', 'quantity')
    ->joinNestedColumns(
        $db->tbl->order->select('order_id', 'amount', 'comment'), // sub query
        'oi.order_id',                                            // ON condition (see above)
        ['my', 'nested', 'order']                                 // nesting path
    )
    ->joinNestedColumns(
        $db->tbl->product->select('product_id', 'title')          // sub query
            ->joinNestedColumns(
                $db->tbl->brand->select('brand_id', 'title'),     // sub sub query
                'p.brand_id'
            )
            ->joinNestedColumns($db->tbl->color, 'p', 'color'),   // just join table - select all its columns
        'oi.product_id',
        'product'
    );

print_r($query->row);

$query = $db->tbl->product->select(/*...*/)
    ->where(                                // WHERE
        Sql::condition()
            ->and('color_id')->eq(5)            // ("color_id" = ?
            ->and('price')->lte(2000)           // AND "price" <= ?
            ->and('title')->isNotNull()         // AND "title" IS NOT NULL
            ->and('brand_id')->in(2, 3)         // AND "brand_id" IN (?, ?)
            ->and('width')->between(250, 350)   // AND "width" BETWEEN ? AND ?
            ->and('state=1')->custom()          // AND state=1) -- w/o quotes: custom query not changed
    );


$query = $db->tbl->product->select(/*...*/)
    ->where('color_id', 5)      // same: `->where('color_id =', 5)`
    ->where(
        'price <=', // supported operators: = | != | <> | < | > | <= | >=
        $db->tbl->product->select('AVG(price)') // HEAVING clause described below
    )
    ->where('title !=', null);

$query = $db->tbl->product->select(/*...*/)
    ->where('`width` BETWEEN ? AND ?', [250, 350])  // custom sql with binding parameters
    ->where('state=1');                             // custom sql w/o binding parameters

$query = $db->tbl->product->select(/*...*/)
    ->where([
        'color_id' => 5,
        'price <=' => 2000,
        'title !=' => null,
        Sql::condition('brand_id')->eq(2)->or->eq(3),   // AND ("brand_id" = ? OR "brand_id" = ?)
        '`width` BETWEEN ? AND ?' => [250, 350],
        'state=1',
    ]);

$query = $db->tbl->product->select('b.title brand', 'COUNT(*) cnt')
    ->join($db->tbl->brand)
    ->groupBy('b.title')
    ->having('COUNT(*) > ', 1);

$query = $db->tbl->product->select('b.title brand', 'p.title product', 'price')
    ->left($db->tbl->brand)
    ->orderBy(      // ORDER BY
        'b.title',  //     "b"."title" NULLS FIRST,
        '-price'    //     "price" DESC NULLS LAST
    );

$query = $db->tbl->product->select('p.title product', 'color_id')
    ->orderBy(                  // ORDER BY
        Sql::case('color_id')   //     CASE "color_id"
            ->when(5)->then(1)  //         WHEN 5 THEN 1
            ->when(1)->then(2)  //         WHEN 1 THEN 2
            ->else(100)         //         ELSE 100 END
    );

$query = $db->tbl->product->select()->orderBy('product_id')->limit(3, 2);

$query = $db->select()
    ->unionAll(
        $db->tbl->product->select('product_id', 'title')->orderBy('product_id')->limit(2),
        $db->tbl->product->select('product_id', 'title')->orderBy('-product_id')->limit(2),
    )
    ->except(
        $db->tbl->product->select('product_id', 'title')->orderBy('product_id')->limit(5, 1),
    )
    ->orderBy('product_id');

use App\Db\MainDb;

$db = MainDb::instance();
$connection = $db->getConnection(); // or $db->getFreeConnection();
// $connection = new \VV\Db\Connection(...);

// from Connection directly:
$insertQuery = $connection->insert()->into('tbl_order');
// from Db:
$insertQuery = $db->insert()->into('tbl_order');
// from Table (recommended):
$insertQuery = $db->tbl->order->insert();
// $insertQuery = $connection->insert()->into($db->tbl->order);

$db->tbl->foo->insert([
    'int_column' => true, // inserts `1` (or `0` for `false`)
    'bool_column' => 1, // inserts `true` for `1` and `false` for `0` (exception for other numbers)
    'date_column' => new \DateTimeImmutable(), // inserts date only: `Y-m-d`
    'time_column' => new \DateTimeImmutable(), // inserts time only: `H:i:s`
    'timestamp_column' => new \DateTimeImmutable(), // inserts date and time: `Y-m-d H:i:s`
    'timestamp_tz_column' => new \DateTimeImmutable(), // inserts date and time with time zone: `Y-m-d H:i:s P`
]);

$result = $query->exec();

$result = $query->initInsertedId()->exec();
$id = $result->insertedId();
$affectedRows = $result->affectedRows();

$id = $query->insertedId();             // executes Insert
$affectedRows = $query->affectedRows(); // executes Insert too

$query = $db->tbl->order->insert()
    ->columns('user_id', 'comment')
    ->values(1, 'my comment');

$query = $db->tbl->order->insert()
    // ->set([
    //     'user_id' => 1,
    //     'comment' => 'my comment',
    // ])
    ->set('user_id', 1)
    ->set('comment', 'my comment');

$insertedId = $db->tbl->order->insert([
    'user_id' => 1,
    'date_created' => new \DateTime(),
]);

$query = $db->tbl->orderItem->insert()->columns('order_id', 'product_id', 'price', 'quantity');
foreach ($valuesList as $values) {
    $query->values(...$values);
}

// copy order
$newOrderId = $db->tbl->order->insert([
    'user_id' => $userId,
    'date_created' => new \DateTime(),
]);

$query = $db->tbl->orderItem->insert()
    ->columns('order_id', 'product_id', 'price', 'quantity')
    ->values(
        $db->tbl->orderItem
            ->select((string)$newOrderId, 'product_id', 'price', 'quantity')
            ->where('order_id', $orderId)
    );

$query = $db->tbl->orderItem->insert()
    ->columns('order_id', 'product_id', 'price', 'quantity')
    ->execPer(1000);
foreach ($valuesList as $values) {
    $query->values(...$values);
}
$query->execPerFinish(); // exec last

use App\Db\MainDb;

$db = MainDb::instance();
$connection = $db->getConnection(); // or $db->getFreeConnection();
// $connection = new \VV\Db\Connection(...);

// from Connection directly:
$updateQuery = $connection->update()->table('tbl_order');
// from Db:
$updateQuery = $db->update()->table('tbl_order');
// from Table (recommended):
$updateQuery = $db->tbl->order->update();
// $updateQuery = $connection->update()->table($db->tbl->order);

$db->tbl->foo->update([
    'int_column' => true, // sets `1` (or `0` for `false`)
    'bool_column' => 1, // sets `true` for `1` and `false` for `0` (exception for other numbers)
    'date_column' => new \DateTimeImmutable(), // sets date only: `Y-m-d`
    'time_column' => new \DateTimeImmutable(), // sets time only: `H:i:s`
    'timestamp_column' => new \DateTimeImmutable(), // sets date and time: `Y-m-d H:i:s`
    'timestamp_tz_column' => new \DateTimeImmutable(), // sets date and time with time zone: `Y-m-d H:i:s P`
]);

$result = $query->exec();

$affectedRows = $result->affectedRows();

$affectedRows = $query->affectedRows();

$query = $db->tbl->order->update()
    // ->set([
    //     'amount' => 1000,
    //     'state_id' => 1,
    // ])
    ->set('amount', rand(10_00, 10000_00) * 0.01)
    ->set('state_id', rand(1, 3))
    ->whereId(2);

$affectedRows = $db->tbl->order->update(
    [
        'amount' => rand(1_00, 10_000_00) * 0.01,
        'state_id' => rand(1, 3),
    ],
    3
    // ['order_id' => 3]
    // Sql::condition()->and('order_id')->eq(3)
);

$query = $db->tbl->order->update()
    ->set(
        'amount',
        $db->tbl->orderItem
            ->select('SUM(price * quantity)')
            ->where('order_id=o.order_id')
    )
    ->where('amount', null);

use App\Db\MainDb;

$db = MainDb::instance();
$connection = $db->getConnection(); // or $db->getFreeConnection();
// $connection = new \VV\Db\Connection(...);

// from Connection directly:
$deleteQuery = $connection->delete()->into('tbl_order');
// from Db:
$deleteQuery = $db->delete()->into('tbl_order');
// from Table (recommended):
$deleteQuery = $db->tbl->order->delete();
// $deleteQuery = $connection->delete()->from($db->tbl->order);

$result = $query->exec();
$affectedRows = $result->affectedRows();
// or
$affectedRows = $query->affectedRows();

$query = $db->tbl->orderItem->delete()
    ->where(
        'price > ',
        $db->tbl->orderItem
            ->select('AVG(price)')
            ->mainTableAs('oi2')
            ->where('oi2.order_id=oi.order_id')
    );

$transaction = $connection->startTransaction();
// or
$transaction = $db->startTransaction();

try {
    // ...
    $transaction->commit();
} catch (\Throwable $e) {
    $transaction->rollback();
    // ...
}

$transaction = $db->startTransaction();
try {
    $newOrderId = $db->tbl->order->insert()
        ->set([
            'user_id' => $userId,
            'date_created' => new \DateTime(),
        ])
        ->insertedId($transaction);

    echo "new Order ID: $newOrderId\n";

    $affectedRows = $db->tbl->orderItem->insert()
        ->columns('order_id', 'product_id', 'price', 'quantity')
        ->values(
            $db->tbl->orderItem
                ->select((string)$newOrderId, 'product_id', 'price', 'quantity')
                ->where('order_id', $orderId)
        )
        ->affectedRows($transaction);

    echo "copied Order items: $affectedRows\n";

    $db->tbl->order->update()
        ->set(
            'amount',
            $db->tbl->orderItem->select('SUM(price * quantity)')->where('order_id=o.order_id')
        )
        ->whereId($newOrderId)
        ->exec($transaction);

    // you can execute important statement in transaction free connection
    $db->tbl->log->insert()
        ->set(['title' => "new order copy #$newOrderId"])
        ->exec($db->getFreeConnection());

    // throw new \RuntimeException('Test transactionFreeConnection()');

    $transaction->commit();
} catch (\Throwable $e) {
    $transaction->rollback();
    /** @noinspection PhpUnhandledExceptionInspection */
    throw $e;
}

use VV\Db\Sql;
use VV\Db\Sql\Condition;

$condition = new Condition();
// or
$condition = Sql::condition();

$condition = Sql::condition()
    ->and('foo')->gte(100)
    ->and/*('foo')*/->lte(1000) // if target expression of next predicate is same as previous one
                                // you may omit argument for "connector" method     
    ->and(
        (new Condition())
            ->or('bar')->eq(1)
            ->or('bar')->isNull()
        // Sql::condition('bar')->eq(1)->or->isNull()
    )

$db->select(
        Sql::case('foo')
            ->when(1)->then('first')
            ->when(2)->then('second')
            ->when(3)->then('third')
            ->else('N-th')
            ->as('placement'),
   )
   ->from('bar');

Sql::case()
    ->when(['foo <' => 10])
        ->then('low')
    ->when(Sql::condition('foo')->between(10, 100))
        ->then('middle')
    ->else('high');