• drupal 用法小结,drupal select ,query ,distinct

    https://api.drupal.org/api/drupal/includes%21actions.inc/function/actions_do/7.x

    addFileds : 

    这个更全点：

    https://www.drupal.org/docs/7/api/database-api/database-api-overview

    这个distinct：

    https://www.drupal.org/node/706264

    //

    SELECT COUNT(*) FROM (SELECT DISTINCT first_field, second_field, third_field FROM the_table)  AS distinct_three
to work more generally.

using DBTNG in Drupal 7 this would look like

db_select($table)
  ->fields($table, array('field1', 'field2'))
  ->distinct()
  ->countQuery();

    //db_count 方法:

    https://www.drupal.org/node/1848376

    Dynamic queries

     
     

    Count queries

     
    Last updated on 
    5 December 2016
     

    Count queries

    Any query may have a corresponding "count query". The count query returns the number of rows in the original query. To obtain a count query, use the countQuery() method.

    $count_query = $query->countQuery();
$count_query is now a new Dynamic Select query with no ordering restrictions that when executed will return a result set with only one value, the number of records that would be matched by the original query. Because PHP supports chaining methods on returned objects, the following idiom is a common approach:

$num_rows = $query->countQuery()->execute()->fetchField(); //输出数量

$detail_r = $detailresult->fetchAssoc();
$detailresult->rowCount()
     

    db_select 方法:

    https://api.drupal.org/api/drupal/includes%21database%21database.inc/group/database/7.x

    https://api.drupal.org/api/drupal/includes%21database%21database.inc/7.x

    https://api.drupal.org/api/drupal/includes%21database%21select.inc/class/SelectQueryExtender/7.x

    简单截图：

    

    // db_select :subQuery :
    

    

    ParisLiakos commented 6 years ago
    

    

    

    

    

    if you need joining a subquery use this:
    

    

      // Create a subquery, which is just a normal query object.
    $subquery = db_select('test_task', 'tt');
    $subquery->addField('tt', 'pid', 'pid');
    $subquery->condition('priority', 1);

    // Create another query that joins against the virtual table resulting
    // from the subquery.
    $select = db_select('test', 't');
    $select->join($subquery, 'tt', 't.id=tt.pid');
    $select->addField('t', 'name');

    // The resulting query should be equivalent to:
    // SELECT t.name
    // FROM test t
    //   INNER JOIN (SELECT tt.pid AS pid FROM test_task tt WHERE priority=1) tt ON t.id=tt.pid
    

    


     
    

    // db_query
    

    

    $result = db_query('SELECT n.nid, n.title, n.created
FROM {node} n WHERE n.uid = :uid AND n.type = :type', array(':uid' => $uid, ':type' => 'page'));
// Result is returned as a iterable object that returns a stdClass object on each iteration
foreach ($result as $record) {
  // Perform operations on $record->title, etc. here.

print($record->title . "");
  
// in this example the available data would be mapped to object properties:
  // $record->nid, $record->title, $record->created
}
    

    


    //date_format:
    

    

     $sql = "SELECT (DATE_FORMAT(FROM_UNIXTIME(co.changed), '%d-%m-%Y')) AS date, ci.type AS type, co.status, COUNT(distinct(co.order_id)) AS count
    FROM commerce_order co
    LEFT JOIN commerce_line_item ci ON co.order_id = ci.order_id
    GROUP BY date, co.status, ci.type
    ORDER BY date";
  $result = db_query($sql);
  foreach ($result as $row) {
    print_r($row);
  }
    

    



    //Left Join SubQuery
    

    

    Query I am executing with db_select:

     $query = db_select('node_view_count', 'n');
     $query->join('users', 'u', 'n.uid = u.uid'); //JOIN node with users

    $query->groupBy('n.nid');//GROUP BY user ID
    $query->groupBy('u.name');//GROUP BY user ID
    
    $query->fields('n',array('nid'))//SELECT the fields from node_view_count
    ->fields('u',array('name'))//SELECT the fields from user
    ->condition('n.uid','0','<>')
    ->orderBy('timestamp', 'DESC');//ORDER BY created
    

    


    2
    

    

    $connection = Database::getConnection();
    $sth = $connection->select('file_managed', 'fm');
    $sth->addField('fm', 'filemime');
    $sth->addExpression('COUNT(fm.filemime)', 'count');
    $sth->groupBy('fm.filemime');
    // Execute the statement
    $data = $sth->execute();
    // Get all the results
    $results = $data->fetchAll(PDO::FETCH_ASSOC);
    

    




    

    //结果集处理：
    

    db_like($prefix);
    

    https://api.drupal.org/api/drupal/includes!database!database.inc/function/db_like/7.x
    

    

      $search_string ="per";
  $result = db_query('SELECT title
                      FROM {node} n
                      WHERE n.title like :title'
                      ,array(':title' => "%".$search_string."%"))
                      ->fetchAll();
  print_r($result);


$result = db_select('person', 'p')
  ->fields('p')
  ->condition('name', db_like($prefix) . '%', 'LIKE')
  ->execute()
  ->fetchAll();

    

     $sql = 'SELECT sid, score FROM {search_index} WHERE word LIKE :term';
 $result = db_query($sql, array(':term' => '%' . db_like($search_term)));
    

    


    


    //结果集处理：
    

    

    <?php
// Using the same query from above...
$uid = 1;
$result = db_query('SELECT n.nid, n.title, n.created
FROM {node} n WHERE n.uid = :uid', array(':uid' => $uid));

// Fetch next row as a stdClass object.
$record = $result->fetchObject();  

// Fetch next row as an associative array.
$record = $result->fetchAssoc();

// Fetch data from specific column from next row
// Defaults to first column if not specified as argument
$data = $result->fetchColumn(1); // Grabs the title from the next row

// Retrieve all records into an indexed array of stdClass objects.
$result->fetchAll();

// Retrieve all records as stdObjects into an associative array 
// keyed by the field in the result specified. 
// (in this example, the title of the node)
$result->fetchAllAssoc('title');

// Retrieve a 2-column result set as an associative array of field 1 => field 2.
$result->fetchAllKeyed();
// Also good to note that you can specify which two fields to use
// by specifying the column numbers for each field
$result->fetchAllKeyed(0,2); // would be nid => created
$result->fetchAllKeyed(1,0); // would be title => nid

// Retrieve a 1-column result set as one single array.
$result->fetchCol();
// Column number can be specified otherwise defaults to first column
$result->fetchCol($db_column_number);

// Count the number of rows
$result->rowCount();
//count just one
$dev_query = "select id from aa where ont > UNIX_TIMESTAMP() - 60  group by id";
$id  = db_query($dev_query)->fetchField(); //print only one id  string 
$a = 1;



?>
    

    



    //DB_insert
    https://www.drupal.org/node/310079

    https://dev.mysql.com/doc/refman/5.7/en/insert-select.html   //db insert 语法
    

    //多条数据的插入
    

    

     $values = array(
    array(
    'title' => 'Example',
    'uid' => 1,
    'created' => REQUEST_TIME,
    ),
    array(
    'title' => 'Example 2',
    'uid' => 1,
    'created' => REQUEST_TIME,
    ),
    array(
    'title' => 'Example 3',
    'uid' => 2,
    'created' => REQUEST_TIME,
    ),
    );
    $query = db_insert('node')->fields(array('title', 'uid', 'created'));
    foreach ($values as $record) {
    $query->values($record);
    }
    $query->execute();
    

    



    //基于select的插入：
    

    

    <?php
// Build the SELECT query.
$query = db_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.
db_insert('mytable')
  ->from($query)
  ->execute();
?>
    

    


     
    db_insert 模拟 insert ignore 参数：
    https://drupal.stackexchange.com/questions/89253/how-to-set-insert-ignore-in-db-insert-without-db-merge  （
    

    How to set 'INSERT IGNORE' in db_insert without db_merge
    

    貌似不可实现）
    解决方案：
    

    （1）
    

    

    try {
    $insertID = db_insert('crawl_data')->fields(array(
        'url' => $url, 
    ))->execute();
} catch (Exception $ex) {
   //这样就不会执行插入，并且不报错...
}
    

    



    （2）：先查一下，再入库
    其他：
    

    

    db_merge('people')
  ->key(array('job' => 'Speaker'))
  ->insertFields(array('age' => 31,'name' => 'Meredith'))
  ->updateFields(array('name' => 'Tiffany'))
  ->execute();
    

    



    //如果存在job为Speaker的一条记录，则更新name为Tiffany，如果不存在，就插入一条age为31，name为Meredith，job为Speaker的记录。
    

    6.对数据库某字段值自动加一或者自增。
    

    复制代码 代码如下:
    

    

    db_update('example_table')
  ->expression('count', 'count + 1')
  ->condition('field1', $some_value)
  ->expression('field2', 'field2 + :inc', array(':inc' => 2))
  ->execute();
    

    


    //通过子sql查询插入
    

    

    <?php
// Build the SELECT query.
$query = db_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.
db_insert('mytable')
  ->from($query)
  ->execute();
?>
    

    


    https://api.drupal.org/api/drupal/includes%21database%21database.inc/7.x

    


    //DB_AND  || DB_OR
    

    

    $and = db_and()->condition('mid', 1)->condition('cache_type', 'year');
$and = db_or()->condition('mid', 1)->condition('cache_type', 'year');
$query->condition($or);
    

    


    //切换数据库
    

    // set external database.
    

    

    db_set_active('panel');
// Start select query.
  $query = db_select('gw_route', 'g');
  $query->fields('g', array('country', 'cost'));

// Create expression (acts the same as any db value at sql) and use it.
  $query->addExpression('MIN(g.cost)', 'min_cost');

  $query->condition('g.country', '','!=');
  $query->groupBy('g.country');
  $query->orderBy('g.country', 'ASC');
  $result = $query->execute();
// Set active default database.
  db_set_active();
  while($record = $result->fetchAssoc()) {
        print_r($record);
    }
    

    


     
    

    //LEFT_JOIN 子查询 sub_query
    原理(code)：
    

    

    public function leftJoin($table, $alias = NULL, $condition = NULL, $arguments = array()) {
  return $this->addJoin('LEFT OUTER', $table, $alias, $condition, $arguments);
}
//add join
public function addJoin($type, $table, $alias = NULL, $condition = NULL, $arguments = array()) {

  if (empty($alias)) {
    if ($table instanceof SelectQueryInterface) {
      $alias = 'subquery';
    }
    else {
      $alias = $table;
    }
  }//对table 做了是否是查询的判断

  $alias_candidate = $alias;
  $count = 2;
  while (!empty($this->tables[$alias_candidate])) {
    $alias_candidate = $alias . '_' . $count++;
  }
  $alias = $alias_candidate;

  if (is_string($condition)) {
    $condition = str_replace('%alias', $alias, $condition);
  }

  $this->tables[$alias] = array(
    'join type' => $type,
    'table' => $table,
    'alias' => $alias,
    'condition' => $condition,
    'arguments' => $arguments,
  );

  return $alias;
}


    QQQQ:
    

    How to set an alias to a fields on a db_select?
    

    

    $query = db_select('super_long_table', 'slt');
$query->addField('slt', 'mys_super_long_field', 'mslf');
    

     
    

    


     
    

     
    

    

    

    

    

    
    
          
    • 

          
  • 

            相关阅读:
    
            
              P3899 [湖南集训]谈笑风生
                
    bzoj3252: 攻略
                
    批量创建用户20个和密码
                
    创建100个目录dir1-dir100一键完成
                
    SVM的优缺点
                
    Python zip() 函数
                
    经典博客4
                
    python的空格和tab混用报错问题
                
    Python的functools.reduce用法
                
    matplotlib显示AttributeError: 'module' object has no attribute 'verbose'
                
                    
          
    • 
          
  • 
            原文地址：https://www.cnblogs.com/cbugs/p/6656273.html
          
    • 

        

      



      
      

        

        

      

    

  


  

  
Copyright © 2020-2023 
  润新知