README

目标

创建一个具有关系支持和 CRUD 操作的灵活查询构建器。

要求

• php >= 7.1

支持的数据库

• PostgreSQL >=9.5
• MySQL >= 5.7
• MongoDB (计划中)

安装

composer require vivace/db

使用方法

初始化数据库的驱动。在这个例子中，是 postgresql 的驱动。

$pdo = new \PDO('dsn', 'user', 'pass');
$driver = new \vivace\db\sql\PostrgeSQL\Driver($pdo);

初始化存储对象。

$userStorage = new \vivace\db\sql\Storage($driver, 'users');

现在您可以使用创建的存储进行数据操作。

将数据保存到您的存储中。

$ok = $userStorage->save(['name' => 'Zoe Saldana', 'career' => 'actor', 'rating' => 4.95]);

让我们尝试从存储中检索保存的数据。

$user = $userStorage->filter(['name' => 'Zoe Saldana'])->fetch()->one();
// $user is simple assoc array.
var_dump($user);

现在是对数据做出更改的时候了

$user['age'] = 39;

并在存储中保存更改。

$ok = $userStorage->save($user);

更多示例。

过滤

$users = $userStorage
    ->limit(100)
    // equalent SQL condition "career IN ('actor', 'producer') OR age >= 40"
    ->filter(['or', ['in', 'career', ['actor', 'producer'], ['>=', 'age', 40]])
    ->fetch();

插入/更新

插入一行

$ok = $userStorage->save(['name' => 'Mark Rufallo']);

多行

$ok = $userStorage->save([
    ['name' => 'Mark Rufallo'],
    ['name' => 'Chris Evans', 'rating' => 4.95],
]);

如果存在则更新，否则插入

$ok = $userStorage->save([
    ['id' => 6, 'name' => 'Mark Ruffalo'],// This data will be updated, because 'id' is the primary key
    ['name' => 'Chris Hemsworth'] // This data will be inserted as new row
]);

按条件更新

$numberOfupdated = $userStorage
    ->sort(['id' => -1])// Sorting by `id` in descending order
    ->skip(100)// skip first 100 found rows
    ->update(['career' => 'actor']);

按条件删除

$numberOfDeleted = $userStorage
    ->filter(['age' => 18, 'rating' => 5])
    ->and(['in', 'career', ['actor', 'producer'])
    // Delete all users by condition "age = 18 AND rating = 5 AND career IN ('actor', 'producer')"
    ->delete();

关系

$userStorage = $userStorage->projection([
    // OneToOne
    'country' => new \vivace\Relation\Single($countryStorage, ['country_id' => 'id']),
    // OneToMany
    'rewards' => new \vivace\Relation\Many($rewardsStorage, ['id' => 'user_id'])
]);

$users = $userStorage->fetch()->all();

foreach($users as $user){
    if(isset($user['country'])) {
        var_dump($user['country']);
    }
    foreach($user['rewards'] as $reward){
        var_dump($reward);        
    }
}

字段别名

$userStorage = $userStorage->projection([
    'rank' => 'rating'
]);

// Aliases are available for use in the condition.
$user = $userStorage->filter(['between', 'rank', 4, 5])->fetch()->one();

运行测试

对于测试，您需要连接到数据库。如果您使用 docker，则可以使用以下命令启动数据库

docker-compose up -d mysql pgsql

然后运行测试

docker-compose run --rm php codecept run --env pgsql --env mysql