Sloth Framework - Databases

Select

Simple Select

    use Sloths\Db;
    use Sloths\Db\Sql\Select;

    $select = new Select('users');
    $select->select('name, email')
        ->where('id = 1')
        ->orderBy('name, created_time DESC')
        ->limit(10)
        ->offset(5)
        ;

    // or
    $select = Db::select('users', 'name, email', 'id = 1')
        ->orderBy('name, created_time DESC')
        ->limit(10)
        ->offset(5)
        ;

    $select->toString();
    // SELECT name, email FROM users WHERE (id = 1) ORDER BY name, created_time DESC LIMIT 10 OFFSET 5

Select With Join

    use Sloths\Db;

    $select = Db::select();
    $select->from('posts')
        ->select('title, content')
        ->join('users', 'users.id = posts.user_id')
        ->where('users.id = 1')
    ;

    $select->toString();
    // SELECT title, content FROM posts INNER JOIN users ON users.id = posts.user_id WHERE users.id = 1

Complex Where Conditions (Having is similar to)

    use Sloths\Db;

    $select = Db::select('users');
    $select->select('name, email')
        ->where('status = ?', 'active')
        ->where(function() {
            $this->where('group_id IN(?)', [1, 2, 3])
            ->orWhere('is_admin = ?', 1)
        })
        ;

    $select->toString();
    /*
    SELECT name, email
    FROM users
    WHERE
        (status = 'active')
        AND (
            (group_id IN(1, 2, 3))
            OR (is_admin = 1)
        )
    */

With SQL Options

    use Sloths\Db;

    $select = Db::select();
    $select->from('users')
        ->distinct()
        ->calcFoundRows()
        ->select('email');

    $select->toString();
    // SELECT DISTINCT, SQL_CALC_FOUND_ROWS email FROM users

Insert

    use Sloths\Db;
    use Sloths\Db\Sql\Insert;

    $insert = new Insert('users');
    $insert->values(['name' => 'User Name', 'user@example.com']);

    // or
    $insert = Db::insert('users', ['name' => 'User Name', 'user@example.com']);

    $insert->toString();
    // INSERT INTO users (name, email) VALUES ('User Name', 'user@example.com')

Update

    use Sloths\Db;
    use Sloths\Db\Sql\Update;

    $update = new Insert('users');
    $update->values(['name' => 'User Name', 'user@example.com'])->where('id = 1');

    // or
    $update = Db::update('users', ['name' => 'User Name', 'user@example.com'], 'id = 1');

    $update->toString();
    // UPDATE users SET name = 'User Name', email = 'user@example.com' WHERE (id = 1)

Delete

    use Sloths\Db;
    use Sloths\Db\Sql\DELETE;

    $delete = new Delete('users');
    $delete->where('id = 1');

    // or
    $delete = Db::delete('users', 'id = 1');

    $delete->toString();
    // DELETE FROM users WHERE (id = 1)

Query Builder

Select

Simple Select

Select With Join

Complex Where Conditions (Having is similar to)

With SQL Options

Insert

Update

Delete