nortmas
5/11/2014 - 1:04 PM

Dynamic queries

$query_club = db_select('club_members','cm');
$query_club->fields('cm',array('first_name','last_name'));
$query_club->group_by('cm.uid');

$query_network = db_select('network_members','nm');
$query_network->fields('nm',array('first_name','last_name'));
$query_network->group_by('nm.uid');

$query_club->union($query_network, 'UNION');
$result = $query->execute()->fetch();                     // Use the default fetch mode.
$result = $query->execute()->fetchField($column_index);   // To fetch just a single field.
$result = $query->execute()->fetchObject();               // Fetch as a stdClass object.
$result = $query->execute()->fetchAssoc();                // Fetch as an associative array.

// Retrieve all records into an indexed array of stdClass objects.
$result->fetchAll();
// Retrieve all records into an associative array keyed by the field in the result specified.
$result->fetchAllAssoc($field);
// Retrieve a 2-column result set as an associative array of field 1 => field 2.
$result->fetchAllKeyed();
// You can also specify which two fields to use by specifying the column numbers for each field
$result->fetchAllKeyed(0,2); // would be field 0 => field 2
$result->fetchAllKeyed(1,0); // would be field 1 => field 0
// If you need an array where keys and values contain the same field (e.g. for creating a 'checkboxes' form element), the following is a perfectly valid method:
$result->fetchAllKeyed(0,0); // would be field 0 => field 0, e.g. [article] => [article]
// Retrieve a 1-column result set as one single array.
$result->fetchCol();
// Column number can be specified otherwise defaults to first column
$result->fetchCol($column_index);

// To count the number of rows returned from a DELETE, INSERT or UPDATE statement:
$number_of_rows = $query->execute()->rowCount();
// To count the number of rows returned from a SELECT statement use:
$number_of_rows = db_select('node')->countQuery()->execute()->fetchField();
$query->orderBy('f.field_val ', 'DESC');
$query->orderBy("(f.city = '".$city."')", "DESC");

$query->orderRandom();
// Would order first by the "term" field of the query and then, for records that have the same term, order randomly.
$query->orderBy('term')->orderRandom()->execute();
$query->isNull('myfield');
// Results in (myfield IS NULL)
$query->isNotNull('myfield');
// Results in (myfield IS NOT NULL)
$query->groupBy('n.uid');
___save_debug($query->__toString());
___save_debug($query->arguments());

/******** OR ***********/

$str = $query->__toString();
$args = $query->arguments();
$q = str_replace(array_keys($args), array_values($args), $str);
___save_debug($q);
$query = db_select('node', 'n');
$query->join('field', 'f', 'n.nid = f.nid');
$query->leftJoin('url_alias', 'ua', "CONCAT('node/', n.nid) = ua.source");
$query->fields('n', array('status'));
$query->condition('n.nid', $nid, '=');
$result = $query->execute();

foreach ($result as $rec) {
}
$count = $query->countQuery()->execute()->fetchField();
$page = pager_default_initialize($count, $num_per_page);
$offset = $num_per_page * $page;
$result = $query->range($offset, $num_per_page)->execute();
	
foreach ($result as $rec) {
} 
	
$out .= theme('pager');
<?php

// D7
$or = db_or();
$or->condition('status', $val, '=', 'n');
$or->condition('field_val', $val, '=', 'f');
$query->condition($or);

// D8
$group = $query->orConditionGroup()
        ->condition('field_name', [], 'IN')
        ->isNull('field_name');
$query->condition($group);
$query->condition($field, $value = NULL, $operator = '=');
$query->where('WHERE created > :created', array(':created' => $created));

$query->havingCondition($field, $value = NULL, $operator = '=');
$query->having($snippet, $args = array());

$query->condition('myfield', array(1, 2, 3), 'IN');
$query->condition('myfield', array(5, 10), 'BETWEEN');
$query->condition('myfield', array(1, 2, 3), 'NOT IN');

$query->condition('my_field', '%superstring%', 'LIKE');
$query->condition('my_field', '\_foo\%bar\_', 'LIKE');
// Force filtering of duplicate records in the result set.
$query->distinct()
// Чтобы hook_query_alter подхватился, необходимо пометить запрос каким-либо тегом. 
// Пусть будет 'node_access'. В каждый динамический запрос, обращающийся к нашей таблице, необходимо добавить этот тег с помощью addTag, например:

db_select('node', 'n')
  ->addTag('node_access');
  ->execute();
  
// Далее добавим хук. Есть более избирательный hook_query_TAG_alter.
  
// Для добавления поля к запросу выбора используйте метод addField():
$title_field = $query->addField('n', 'title', 'my_title');
$count_alias = $query->addExpression('COUNT(uid)', 'uid_count');
$count_alias = $query->addExpression('created - :offset', 'uid_count', array(':offset' => 3600));