drupal
$connection= \Drupal::database();
- 动态查询
假设我们要创建一个动态查询,它大致相当于以下静态查询:
$result = $connection->query("SELECT uid, name, status, created, access FROM {users} u WHERE uid <> 0 LIMIT 50 OFFSET 0");
动态等效开始如下:
// Create an object of type Select and directly
// add extra detail to this query object: a condition, fields and a range
$query = $connection->select('users', 'u')
->condition('u.uid', 0, '<>')
->fields('u', ['uid', 'name', 'status', 'created', 'access'])
->range(0, 50);
//执行查询
$result = $query->execute();
foreach ($result as $record) {
// Do something with each $record
}
// 下一个代码块采用前面的每个uid计数节点的示例,并将结果限制为至少有2个记录的uid
$query = $connection->select('node', 'n')
->fields('n',['uid']);
$query->addExpression('count(uid)', 'uid_node_count');
$query->groupBy("n.uid");
$query->having('COUNT(uid) >= :matches', [':matches' => 2]);
$results = $query->execute();
2.结果集
// 将所有记录提取到一个数组中
$result = $query->execute()->fetchAll();
// SELECT语句返回的行数
$number_of_rows = $connection->select('mytable')->countQuery()->execute()->fetchField();
3.插入查询
// An highlighted block
INSERT INTO {mytable} (title, uid, created) VALUES ('Example', 1, 1221717405);
// 相当于以下查询
$result = $connection->insert('mytable')
->fields([
'title' => 'Example',
'uid' => 1,
'created' => REQUEST_TIME,
])
->execute();
// 多值插入
$values = [
[
'title' => 'Example',
'uid' => 1,
'created' => REQUEST_TIME,
],
[
'title' => 'Example 2',
'uid' => 1,
'created' => REQUEST_TIME,
],
[
'title' => 'Example 3',
'uid' => 2,
'created' => REQUEST_TIME,
],
];
$query = $connection->insert('mytable')->fields(['title', 'uid', 'created']);
foreach ($values as $record) {
$query->values($record);
}
$query->execute();
//等效于
INSERT INTO {mytable} (title, uid, created) VALUES ('Example', 1, 1221717405);
INSERT INTO {mytable} (title, uid, created) VALUES ('Example2', 1, 1221717405);
INSERT INTO {mytable} (title, uid, created) VALUES ('Example3', 2, 1221717405);
将联合查询结果保存到mytable表中
<?php
// Build the SELECT query.
$query = $connection->select('node', 'n');
// Join to the users table.
$query->join('users', 'u', 'n.uid = u.uid');
// Add the fields we want.
$query->addField('n','nid');
$query->addField('u','name');
// Add a condition to only get page nodes.
$query->condition('type', 'page');
// Perform the insert.
$connection->insert('mytable')
->from($query)
->execute();
?>
4.更新操作
$num_updated = $connection->update('mytable')
->fields([
'field1' => 5,
'field2' => 1,
])
->condition('created', REQUEST_TIME - 3600, '>=')
->execute();
// 等效于
UPDATE {mytable} SET field1=5, field2=1 WHERE created >= 1221717405;
5.删除查询
$num_deleted = $connection->delete('mytable')
->condition('myfield', 5)
->execute();
//等效于
DELETE FROM {mytable} WHERE myfield=5;
6.事务/错误处理
// The transaction opens here.
$transaction = $connection->startTransaction();
try {
$id = $connection->insert('example')
->fields([
'field1' => 'mystring',
'field2' => 5,
])
->execute();
}
catch (Exception $e) {
$transaction->rollBack();
throw $e;
}
//drupal 中的应用
$transaction = db_transaction();
try {
} catch(\Exception $e) {
$transaction->rollback();
throw $e;
\Drupal::logger('type')->error($e->getMessage());
}