drupal8——数据库API

drupal

$connection= \Drupal::database();

1. 动态查询
   假设我们要创建一个动态查询，它大致相当于以下静态查询：

$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());
    }