README

一个简单且完整的查询构建器，为您的项目增添实用性

索引

• 安装
• 连接
• 选择语句
  • 更多示例
  • 表达式
• 插入语句
• 更新语句
• 删除语句

安装

composer require lucasmialichi/query-builder

连接

// build connection ...
$qb = new QueryBuilder(
    new Connection(
        "127.0.0.1",  // host
        "root",       // user 
        123,          // password
        "teste"       // db name
    )
);

选择语句

$qb->select()->from('users')->where('id', '=', ':Id');
$qb->addParam(':Id', 1);
$result = $qb->execute(); //Instance of QueryBuilder/Connection/QueryResult

上述示例转换为SQL

SELECT * FROM  users where id = :Id

查询结果的获取方法

• fetchAll: 将所有结果作为关联数组获取
• fetchFunction: 实现用户函数的逐行获取
• fetchAssociative: 使用迭代（生成器）获取
• count: 在获取之前计算行数

更多示例

PHP

$qb->select([
    "id" => "identifier",
    "name" => "userName",
    "birth_date" => "birthDate"
])
->from('users', "u")
->join("address", "u.address_id = a.id", "a")
->where('id', '=', ':Id')
->orWhere("birth_date", ">", date("Y-m-d H:i:s"))
->limit(10)
->offset(1);

SQL

SELECT id AS identifier, name AS userName, birth_date AS birthDate FROM users u INNER JOIN address a ON u.address_id = a.id WHERE ( id = :Id ) OR ( birth_date > 2023-12-18 14:30:43 ) LIMIT 10 OFFSET 1

表达式

在qb中实例化表达式有两种方式

静态调用QueryBuilder类

$expression = QueryBuilder::Expr();

如果您已经初始化了queryBuilder，您可以在查询的任何时刻调用它

$qb->select()->from('users')->where(
    $qb->expr("id")->in([1,2,3])->or()->between(1,10)
);

您还可以在select语句中使用表达式，例如

$qb->select(
    $qb->expr()->caseWhen("id = 1", "0")
        ->when("id = 2", "1")
        ->else("2")
        ->end()
)->from('users');

插入语句

PHP

$qb->insert([
    "name" => "alfredo",
    "birth_date" => "1988-06-11"
])->into("users");

值会自动绑定到查询作为参数

SQL

INSERT INTO users (name, birth_date) VALUES ('alfredo', '1988-06-11')

更新语句

PHP

$qb->update([
    "name" => "alfredo",
    "birth_date" => "1988-06-11"
])->from("users")
  ->where("id", "=", ":userId")
  ->addParam(":userId", 2);

值会自动绑定到查询作为参数

SQL

UPDATE users SET name = 'alfredo', birth_date = '1988-06-11' WHERE id = :userId

删除语句

$qb->delete()->from("users")->where($qb->expr("id")->notBetween(1,5))

SQL

DELETE FROM users WHERE (id not between 1 and 5)