README

一个优先考虑可读性和最优性能的基于对象的PHP SQL查询构建器。

安装 ↑

可以使用以下命令使用composer安装此包。

composer require francerz/sql-builder

连接到数据库 ↑

使用URI字符串

$db = DatabaseManager::connect('driver://user:password@host:port/database');

使用环境变量

putenv('DATABASE_SCHOOL_DRIVER', 'driver');
putenv('DATABASE_SCHOOL_HOST', 'host');
putenv('DATABASE_SCHOOL_INST', 'instanceName');
putenv('DATABASE_SCHOOL_PORT', 'port');
putenv('DATABASE_SCHOOL_USER', 'user');
putenv('DATABASE_SCHOOL_PSWD', 'password');
putenv('DATABASE_SCHOOL_NAME', 'database');

// Support to Docker secrets
putenv('DATABASE_SCHOOL_PSWD_FILE', '/run/secrets/db_school_password');

$db = DatabaseManager::connect('school');

基本常用语法 ↑

class Group {
    public $group_id;
    public $subject;
    public $teacher;
}

选择查询 ↑

// SELECT group_id, subject, teacher FROM groups
$query = Query::selectFrom('groups', ['group_id', 'subject', 'teacher']);

$db = DatabaseManager::connect('school');
$result = $db->executeSelect($query);
$groups = $result->toArray(Group::class);

插入查询 ↑

$group = new Group();
$group->subject = 'Database fundamentals';
$group->teacher = 'francerz';

// INSERT INTO groups (subject, teacher) VALUES ('Database fundamentals', 'francerz')
$query = Query::insertInto('groups', $group, ['subject', 'teacher']);

$db = DatabaseManager::connect('school');
$result = $db->executeInsert($query);
$group->group_id = $result->getInsertedId();

更新查询 ↑

$group = new Group();
$group->group_id = 10;
$group->subject = 'Introduction to databases';

// UPDATE groups SET subject = 'Introduction to databases' WHERE group_id = 10
$query = Query::update('groups', $group, ['group_id'], ['subject']);

$db = DatabaseManager::connect('school');
$result = $db->executeUpdate($query);

删除查询 ↑

// DELETE FROM groups WHERE group_id = 10
$query = Query::deleteFrom('groups', ['group_id' => 10]);

$db = DatabaseManager::connect('school');
$result = $db->executeDelete($query);

构建带有WHERE或HAVING子句的SELECT查询 ↑

以下是一些使用WHERE子句的示例，这些子句适用于SELECT、UPDATE和DELETE查询。

当列group_id的值为10时，从groups表中选择所有字段。

SELECT * FROM groups WHERE group_id = 10

// Explicit syntax
$query = Query::selectFrom('groups')->where()->equals('group_id', 10);

// Implicit syntax
$query = Query::selectFrom('groups')->where('group_id', 10);

当列group_id的值为10、20或30时，从groups表中选择所有字段。

SELECT * FROM groups WHERE group_id IN (10, 20, 30)

// Explicit syntax
$query = Query::selectFrom('groups')->where()->in('group_id', [10, 20, 30]);

// Implicit syntax
$query = Query::selectFrom('groups')->where('group_id', [10, 20, 30]);

当列teacher的值为NULL时，从groups表中选择所有字段。

SELECT * FROM groups WHERE teacher IS NULL

// Explicit syntax
$query = Query::selectFrom('groups')->where()->null('teacher');

// Implicit compact syntax
$query = Query::selectFrom('groups')->where('teacher', 'NULL');

当列group_id的值小于或等于10，并且列subject的值包含单词"database"时，从groups表中选择所有字段。

SELECT * FROM groups WHERE group_id <= 10 AND subject LIKE '%database%'

// Explicit syntax
$query = Query::selectFrom('groups');
$query->where()->lessEquals('group_id', 10)->andLike('subject', '%database%');

// Implicit compact syntax
$query = Query::selectFrom('groups');
$query->where('group_id', '<=', 10)->andLike('subject', '%database%');

括号语法

要结合非常具体和复杂的条件，必须覆盖默认的操作符优先级，这通常通过在SQL语法中使用括号来实现。在SQL Builder中，此功能通过使用匿名函数参数巧妙地处理。

括号匿名函数的语法如下

$query->where(function($subwhere) { });
$query->where->not(function($subwhere) { });
$query->where->and(function($subwhere) { });
$query->where->or(function($subwhere) { });
$query->where->andNot(function($subwhere) { });
$query->where->orNot(function($subwhere) { });

当group_id的值为10或介于20到30之间时，从groups表中选择所有字段。

SELECT *
    FROM groups
    WHERE subject LIKE '%database%'
    AND (group_id = 10 OR group_id BETWEEN 20 AND 30)

$query = Query::selectFrom('groups');

// Using an anonymous function to emulate parenthesis
$query->where()
    ->like('subject', '%database%')
    ->and(function(ConditionList $subwhere) {
        $subwhere
            ->equals('group_id', 10)
            ->orBetween('group_id', 20, 30);
    });

运算符列表 ↑

库提供了一系列与各种SQL数据库引擎广泛一致的运算符。为了提高可读性，它还对逻辑运算符and和or进行了前缀。

关于ConditionList类

条件列表、函数和运算符的示例以相同的方式适用于WHERE、HAVING和ON子句。

构建带有JOIN的SELECT查询 ↑

关系数据库中最常见的操作之一是从多个表中合并和组合数据。连接操作允许通过使用 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 CROSS JOIN 语法来合并多个表中的数据。

支持的JOIN类型

查询构建器支持许多类型的 JOIN 语法。

// INNER JOIN
$query->innerJoin($table, $columns = []);

// CROSS JOIN
$query->crossJoin($table, $columns = []);

// LEFT JOIN
$query->leftJoin($table, $columns = []);

// RIGHT JOIN
$query->rightJoin($table, $columns = []);

SQL 连接语法兼容性

连接语法可用于 SELECT、UPDATE 和 DELETE SQL 语法，但是，并非所有数据库引擎都支持它。

示例

SELECT * FROM groups INNER JOIN teachers ON groups.teacher_id = teachers.teacher_id

$query = Query::selectFrom('groups');
$query
    ->innerJoin('teachers')
    ->on('groups.teacher_id', 'teachers.teacher_id');

使用表别名以减少命名长度。

SELECT * FROM groups AS g INNER JOIN teachers AS t ON g.teacher_id = t.teacher_id

// Alias array syntax
$query = Query::selectFrom(['g' => 'groups']);
$query
    ->innerJoin(['t' => 'teachers'])
    ->on('g.teacher_id', 't.teacher_id');

// Alias "AS" string syntax
$query = Query::selectFrom('groups AS g');
$query
    ->innerJoin('teachers AS t')
    ->on('g.teacher_id', 't.teacher_id');

多数据库（同一主机）使用连接选择。

SELECT * FROM school.groups AS g INNER JOIN hr.employees AS e ON g.teacher_id = e.employee_id

$query = Query::selectFrom('school.groups AS g');
$query
    ->innerJoin('hr.employees AS e')
    ->on('g.teacher_id','e.employee_id');

从连接的表中选择字段。

SELECT g.group_id, t.given_name, t.family_name
FROM groups AS g
INNER JOIN teachers AS t ON g.teacher_id = t.teacher_id

$query = Query::selectFrom('groups AS g', ['group_id']);
$query
    ->innerJoin('teachers AS t', ['given_name', 'family_name'])
    ->on('g.teacher_id', 't.teacher_id');

重命名连接表中的字段。

SELECT g.group_id, CONCAT(t.given_name, ' ', t.family_name) AS teacher_name
FROM groups AS g
INNER JOIN teachers AS t ON g.teacher_id = t.teacher_id

$query = Query::selectFrom('groups AS g', ['group_id']);
$query
    ->innerJoin('teachers AS t', [
        'teacher_name' => "CONCAT(t.given_name, ' ', t.family_name)"
    ])->on('g.teacher_id', 't.teacher_id');

将列选择到外部函数中（代码更简洁）。

SELECT g.group_id, CONCAT(t.given_name, ' ', t.family_name) AS teacher_name
FROM groups AS g
INNER JOIN teachers AS t ON g.teacher_id = t.teacher_id

$query = Query::selectFrom('groups AS g');
$query
    ->innerJoin('teachers AS t')
    ->on('g.teacher_id', 't.teacher_id');
$query->columns([
    'g.group_id',
    'teacher_name' => "CONCAT(t.given_name, ' ', t.family_name)"
]);

连接表和子查询。

-- Gets all groups of active teachers
SELECT g.group_id, CONCAT(t.given_name, ' ', t.family_name) AS teacher_name
FROM groups AS g
INNER JOIN (SELECT * FROM teachers WHERE active = 1) AS t
ON g.teacher_id = t.teacher_id

// Creating subquery object
$subquery = Query::selectFrom('teachers');
$subquery->where('active', 1);

$query = Query::selectFrom('groups AS g');
$query
    ->innerJoin(['t' => $subquery])
    ->on('g.teacher_id', 't.teacher_id');
$query->columns([
    'g.group_id',
    'teacher_name' => "CONCAT(t.given_name, ' ', t.family_name)"
]);

SELECT 嵌套 ↑

在某些情况下，简单的数据库表连接不足以满足所有数据需求。通常需要为每个主查询的结果行执行额外的过滤查询。

然而，这种方法通常会导致代码过于复杂，并由于增加的循环和数据库访问往返而出现性能问题。为了解决这些挑战，查询嵌套数据提供了一个更高效、更轻量级的语法。

嵌套结果对象集合

使用 nestMany 方法在每个主查询结果行中嵌套一组结果对象。在提供的示例中，这是用来关联多个学生及其相应的组。当预期每个主记录都有多个相关记录时，这种方法是合适的。

// Primary Query for Groups
$groupsQuery = Query::selectFrom(
    'groups',
    ['group_id', 'subject', 'classroom']
);

// Query for Students
$studentsQuery = Query::selectFrom(
    'students',
    ['student_id', 'group_id', 'first_name', 'last_name']
);

// Nesting students within each group
$groupsQuery
    ->nestMany('Students', $studentsQuery, $groupRow, Student::class)
    ->where('students.group_id', $groupRow->group_id);

// Connecting to the database and executing the query
$db = DatabaseManager::connect('school');
$result = $db->executeSelect($groupsQuery);
$groups = $result->toArray(Group::class);

结果

[
    {
        "group_id": 1,
        "subject": "Programing fundamentals",
        "classroom": "A113",
        "Students": [
            {
                "student_id": 325,
                "first_name": "Charlie",
                "last_name": "Ortega"
            },
            {
                "student_id": 743,
                "first_name": "Beth",
                "last_name": "Wilson"
            }
        ]
    },
    {
        "group_id": 2,
        "subject" : "Object Oriented Programming",
        "classroom": "G7-R5",
        "Students": [
            {
                "student_id": 536,
                "first_name": "Dylan",
                "last_name": "Morrison"
            }
        ]
    }
]

嵌套第一个或最后一个结果对象

另一方面，使用 linkFirst 方法将来自辅助查询的第一个结果对象与主查询结果行中的每一行关联。在给定的代码片段中，这是用来将第一位老师与每个组关联起来。当你想将单个相关记录与每个主记录链接，并优先考虑第一个匹配项时，这种方法是有益的。

此外，还有一个 linkLast 方法，它类似于 linkFirst，但将辅助查询的最后结果对象与主查询结果行中的每一行关联。这在需要为每个主记录优先考虑最新或最新相关记录的场合可能很有用。

// Primary Query for Groups
$groupsQuery = Query::selectFrom(
    'groups',
    ['group_id', 'teacher_id', 'subject', 'classroom']
);

// Query for Teachers
$teachersQuery = Query::selectFrom(
    'teachers',
    ['teacher_id', 'first_name', 'last_name']
);

// Linking the first teacher to each group
$groupsQuery
    ->linkFirst('Teacher', $teachersQuery, $groupRow, Teacher::class)
    ->where('teachers.teacher_id', $groupRow->teacher_id);

// Query for Classes
$classesQuery = Query::selectFrom(
    'groups_classes',
    ['class_id', 'group_id', 'topic', 'date']
)->orderBy('date', 'ASC');

// Linking the last class to each group
$groups
    ->linkLast('LastClass', $classesQuery, $groupRow, GroupClass::class)
    ->where('groups_classes.group_id', $groupRow->group_id);

// Connecting to the database and executing the query
$db = DatabaseManager::connect('school');
$result = $db->executeSelect($groupsQuery);
$groups = $result->toArray(Group::class);

结果

[
    {
        "group_id": 1,
        "teacher_id": 3,
        "subject": "Programming fundamentals",
        "classroom": "A113",
        "Teacher": {
            "teacher_id": 3,
            "first_name": "Rosemary",
            "last_name": "Smith"
        },
        "LastClass": {
            "class_id": 233,
            "group_id": 1,
            "topic": "Algorithms",
            "date": "2024-04-18"
        }
    },
    {
        "group_id": 2,
        "teacher_id": 75,
        "subject" : "Object Oriented Programming",
        "classroom": "G7-R5",
        "Teacher": {
            "teacher_id": 75,
            "first_name": "Steve",
            "last_name": "Johnson"
        },
        "LastClass": null
    }
]

通过选择适当的嵌套模式（nestMany、linkFirst 或 linkLast），可以根据具体的数据结构和需求来调整查询，以高效地处理嵌套数据。

旧式嵌套语法

存在一种旧式的嵌套语法，仍在底层工作。

$groupsQuery->nest(['Students' => $studentsQuery], function (NestedSelect $nest, RowProxy $row) {
    $nest->getSelect()->where('s.group_id', $row->group_id);
}, NestMode::COLLECTION, Student::class);

事务 ↑

数据库中最重要的一项功能是保持多个记录的数据一致性，这些记录可能存储在多个表中。

$db = DatabaseManager::connect('school');
try {
    $db->startTransaction();
    
    // Perform any needed operation inside this block to keep consistency.
    
    $db->commit();
} catch (Exception $ex) {
    $db->rollback();
}

执行存储过程 ↑

// Connecting to database 'school'.
$db = DatabaseManager::connect('school');

// Calls stored procedure with two argments.
/** @var SelectResult[] */
$results = $db->call('procedure_name', 'arg1', 'arg2');

// Shows how many results obtained from procedure.
echo count($results) . ' results.' . PHP_EOL;

// Iterating procedure result sets.
foreach ($results as $i => $selectResult) {
    echo "Fetched " . $selectResult->getNumRows() . PHP_EOL;
}

francerz / sql-builder

维护者

详细信息