Newer
Older
Earl Miles
committed
<?php
// $Id$
/**
* @file query.inc
* Defines the query object which is the underlying layer in a View.
*/
/**
* Object used to create a SELECT query.
*/
class views_query {
/**
* A list of tables in the order they should be added, keyed by alias.
*/
var $table_queue = array();
/**
* Holds an array of tables and counts added so that we can create aliases
*/
var $tables = array();
/**
* Holds an array of relationships, which are aliases of the primary
* table that represent different ways to join the same table in.
*/
var $relationships = array();
/**
* An array of sections of the WHERE query. Each section is in itself
* an array of pieces and a flag as to whether or not it should be AND
* or OR.
*/
var $where = array();
/**
* An array of sections of the HAVING query. Each section is in itself
* an array of pieces and a flag as to whether or not it should be AND
* or OR.
*/
var $having = array();
/**
* The default operator to use when connecting the WHERE groups. May be
* AND or OR.
*/
Earl Miles
committed
var $group_operator = 'AND';
/**
* A simple array of order by clauses.
*/
Earl Miles
committed
var $orderby = array();
/**
* A simple array of group by clauses.
*/
Earl Miles
committed
var $groupby = array();
/**
* The table header to use for tablesort. This matters because tablesort
* needs to modify the query and needs the header.
*/
Earl Miles
committed
var $header = array();
/**
* A flag as to whether or not to make the primary field distinct.
*/
Earl Miles
committed
var $distinct = FALSE;
* Constructor; Create the basic query object and fill with default values.
Earl Miles
committed
*/
function views_query($base_table = 'node', $base_field = 'nid') {
$this->base_table = $base_table; // Predefine these above, for clarity.
$this->base_field = $base_field;
$this->relationships[$base_table] = array(
'link' => NULL,
'table' => $base_table,
'alias' => $base_table,
'base' => $base_table
);
Earl Miles
committed
Earl Miles
committed
// init the table queue with our primary table.
$this->table_queue[$base_table] = array(
'alias' => $base_table,
'table' => $base_table,
'relationship' => $base_table,
Earl Miles
committed
'join' => NULL,
);
Earl Miles
committed
// init the tables with our primary table
$this->tables[$base_table][$base_table] = array(
'alias' => $base_table,
if ($base_field) {
$this->fields[$base_field] = array(
'table' => $base_table,
'field' => $base_field,
'alias' => $base_field,
Earl Miles
committed
);
}
$this->count_field = array(
'table' => $base_table,
'field' => $base_field,
'alias' => $base_field,
Earl Miles
committed
'count' => TRUE,
);
}
// ----------------------------------------------------------------
// Utility methods to set flags and data.
Earl Miles
committed
Earl Miles
committed
* Set the base field to be distinct.
*/
function set_distinct($value = TRUE) {
if (!(isset($this->no_distinct) && $value)) {
$this->distinct = $value;
}
}
Earl Miles
committed
* Set what field the query will count() on for paging.
*/
function set_count_field($table, $field, $alias = NULL) {
if (empty($alias)) {
$alias = $table . '_' . $field;
}
$this->count_field = array(
Earl Miles
committed
'table' => $table,
'field' => $field,
'alias' => $alias,
'count' => TRUE,
);
Earl Miles
committed
}
Earl Miles
committed
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
* Set the table header; used for click-sorting because it's needed
* info to modify the ORDER BY clause.
*/
function set_header($header) {
$this->header = $header;
}
// ----------------------------------------------------------------
// Table/join adding
/**
* A relationship is an alternative endpoint to a series of table
* joins. Relationships must be aliases of the primary table and
* they must join either to the primary table or to a pre-existing
* relationship.
*
* An example of a relationship would be a nodereference table.
* If you have a nodereference named 'book_parent' which links to a
* parent node, you could set up a relationship 'node_book_parent'
* to 'node'. Then, anything that links to 'node' can link to
* 'node_book_parent' instead, thus allowing all properties of
* both nodes to be available in the query.
*
* @param $alias
* What this relationship will be called, and is also the alias
* for the table.
* @param $join
* A views_join object (or derived object) to join the alias in.
* @param $base
* The name of the 'base' table this relationship represents; this
* tells the join search which path to attempt to use when finding
* the path to this relationship.
Earl Miles
committed
* @param $link_point
* If this relationship links to something other than the primary
* table, specify that table here. For example, a 'track' node
* might have a relationship to an 'album' node, which might
* have a relationship to an 'artist' node.
*/
function add_relationship($alias, $join, $base, $link_point = NULL) {
Earl Miles
committed
if (empty($link_point)) {
$link_point = $this->base_table;
Earl Miles
committed
}
else if (!array_key_exists($link_point, $this->relationships)) {
return FALSE;
}
Earl Miles
committed
// Make sure $alias isn't already used; if it, start adding stuff.
$alias_base = $alias;
$count = 1;
while (!empty($this->relationships[$alias])) {
$alias = $alias_base . '_' . $count++;
}
Earl Miles
committed
if ($alias = $this->add_table($join->table, $link_point, $join, $alias)) {
$this->relationships[$alias] = array(
'link' => $link_point,
'table' => $join->table,
'base' => $base,
);
Earl Miles
committed
return $alias;
}
}
/**
* Add a table to the query, ensuring the path exists.
Earl Miles
committed
*
* This function will test to ensure that the path back to the primary
* table is valid and exists; if you do not wish for this testing to
* occur, use $query->queue_table() instead.
*
* @param $table
* The name of the table to add. It needs to exist in the global table
* array.
* @param $relationship
* An alias of a table; if this is set, the path back to this table will
* be tested prior to adding the table, making sure that all intermediary
* tables exist and are properly aliased. If set to NULL the path to
* the primary table will be ensured. If the path cannot be made, the
* table will NOT be added.
* @param $join
* In some join configurations this table may actually join back through
* a different method; this is most likely to be used when tracing
* a hierarchy path. (node->parent->parent2->parent3). This parameter
* will specify how this table joins if it is not the default.
* @param $alias
* A specific alias to use, rather than the default alias.
Earl Miles
committed
*
Earl Miles
committed
* @return $alias
* The alias of the table; this alias can be used to access information
* about the table and should always be used to refer to the table when
* adding parts to the query. Or FALSE if the table was not able to be
* added.
*/
function add_table($table, $relationship = NULL, $join = NULL, $alias = NULL) {
if (!$this->ensure_path($table, $relationship, $join)) {
return FALSE;
}
return $this->queue_table($table, $relationship, $this->adjust_join($join, $relationship), $alias);
Earl Miles
committed
}
/**
* Add a table to the query, without ensuring the path.
Earl Miles
committed
*
* This function will test to ensure that the path back to the primary
* table is valid and exists; if you do not wish for this testing to
* occur, use $query->queue_table() instead.
*
* @param $table
* The name of the table to add. It needs to exist in the global table
* array.
* @param $relationship
* The primary table alias this table is related to. If not set, the
* primary table will be used.
* @param $join
* In some join configurations this table may actually join back through
* a different method; this is most likely to be used when tracing
* a hierarchy path. (node->parent->parent2->parent3). This parameter
* will specify how this table joins if it is not the default.
* @param $alias
* A specific alias to use, rather than the default alias.
Earl Miles
committed
*
Earl Miles
committed
* @return $alias
* The alias of the table; this alias can be used to access information
* about the table and should always be used to refer to the table when
* adding parts to the query. Or FALSE if the table was not able to be
* added.
*/
function queue_table($table, $relationship = NULL, $join = NULL, $alias = NULL) {
// If the alias is set, make sure it doesn't already exist.
if (isset($this->table_queue[$alias])) {
Earl Miles
committed
return $alias;
Earl Miles
committed
}
if (empty($relationship)) {
$relationship = $this->base_table;
Earl Miles
committed
}
if (!array_key_exists($relationship, $this->relationships)) {
return FALSE;
}
if (!$alias && $join && $relationship && !empty($join->adjusted) && $table != $join->table) {
$alias = $table;
Earl Miles
committed
}
// Check this again to make sure we don't blow up existing aliases for already
// adjusted joins.
if (isset($this->table_queue[$alias])) {
return $alias;
$alias = $this->mark_table($table, $relationship, $alias);
Earl Miles
committed
// If no alias is specified, give it the default.
if (!isset($alias)) {
$alias = $this->tables[$relationship][$table]['alias'] . $this->tables[$relationship][$table]['count'];
}
// If this is a relationship based table, add a marker with
// the relationship as a primary table for the alias.
$this->mark_table($alias, $this->base_table, $alias);
}
Earl Miles
committed
// If no join is specified, pull it from the table data.
if (!isset($join)) {
$join = $this->get_join_data($table, $this->relationships[$relationship]['base']);
Earl Miles
committed
if (empty($join)) {
return FALSE;
}
$join = $this->adjust_join($join, $relationship);
}
$this->table_queue[$alias] = array(
Earl Miles
committed
'table' => $table,
Earl Miles
committed
'num' => $this->tables[$relationship][$table]['count'],
'alias' => $alias,
'join' => $join,
'relationship' => $relationship,
);
return $alias;
}
function mark_table($table, $relationship, $alias) {
// Mark that this table has been added.
if (empty($this->tables[$relationship][$table])) {
if (!isset($alias)) {
$alias = '';
if ($relationship != $this->base_table) {
// double underscore will help prevent accidental name
// space collisions.
$alias = $relationship . '__';
}
$alias .= $table;
}
$this->tables[$relationship][$table] = array(
'count' => 1,
'alias' => $alias,
);
}
else {
$this->tables[$relationship][$table]['count']++;
}
return $alias;
}
Earl Miles
committed
* Ensure a table exists in the queue; if it already exists it won't
* do anything, but if it doesn't it will add the table queue. It will ensure
* a path leads back to the relationship table.
*
* @param $table
* The unaliased name of the table to ensure.
* @param $relationship
* The relationship to ensure the table links to. Each relationship will
* get a unique instance of the table being added. If not specified,
* will be the primary table.
* @param $join
* A views_join object (or derived object) to join the alias in.
*
* @return
* The alias used to refer to this specific table, or NULL if the table
* cannot be ensured.
*/
function ensure_table($table, $relationship = NULL, $join = NULL) {
// ensure a relationship
Earl Miles
committed
if (empty($relationship)) {
$relationship = $this->base_table;
Earl Miles
committed
}
// If the relationship is the primary table, this actually be a relationship
// link back from an alias. We store all aliases along with the primary table
// to detect this state, because eventually it'll hit a table we already
// have and that's when we want to stop.
if ($relationship == $this->base_table && !empty($this->tables[$relationship][$table])) {
return $this->tables[$relationship][$table]['alias'];
}
Earl Miles
committed
if (!array_key_exists($relationship, $this->relationships)) {
return FALSE;
}
if ($table == $this->relationships[$relationship]['base']) {
return $relationship;
Earl Miles
committed
}
Earl Miles
committed
// If we do not have join info, fetch it.
if (!isset($join)) {
$join = $this->get_join_data($table, $this->relationships[$relationship]['base']);
Earl Miles
committed
}
Earl Miles
committed
// If it can't be fetched, this won't work.
if (empty($join)) {
return;
}
Earl Miles
committed
Earl Miles
committed
// Adjust this join for the relationship, which will ensure that the 'base'
// table it links to is correct. Tables adjoined to a relationship
// join to a link point, not the base table.
$join = $this->adjust_join($join, $relationship);
Earl Miles
committed
if ($this->ensure_path($table, $relationship, $join)) {
Earl Miles
committed
// Attempt to eliminate redundant joins. If this table's
// relationship and join exactly matches an existing table's
// relationship and join, we do not have to join to it again;
// just return the existing table's alias. See
// http://groups.drupal.org/node/11288 for details.
//
// This can be done safely here but not lower down in
// queue_table(), because queue_table() is also used by
// add_table() which requires the ability to intentionally add
// the same table with the same join multiple times. For
// example, a view that filters on 3 taxonomy terms using AND
// needs to join term_data 3 times with the same join.
Earl Miles
committed
// scan through the table queue to see if a matching join and
// relationship exists. If so, use it instead of this join.
// TODO: Scanning through $this->table_queue results in an
// O(N^2) algorithm, and this code runs every time the view is
// instantiated (Views 2 does not currently cache queries).
// There are a couple possible "improvements" but we should do
// some performance testing before picking one.
foreach ($this->table_queue as $queued_table) {
// In PHP 4 and 5, the == operation returns TRUE for two objects
// if they are instances of the same class and have the same
// attributes and values.
if ($queued_table['relationship'] == $relationship && $queued_table['join'] == $join) {
Earl Miles
committed
return $queued_table['alias'];
Earl Miles
committed
}
}
Earl Miles
committed
return $this->queue_table($table, $relationship, $join);
}
}
Earl Miles
committed
* Make sure that the specified table can be properly linked to the primary
* table in the JOINs. This function uses recursion. If the tables
* needed to complete the path back to the primary table are not in the
* query they will be added, but additional copies will NOT be added
* if the table is already there.
*/
function ensure_path($table, $relationship = NULL, $join = NULL, $traced = array(), $add = array()) {
if (!isset($relationship)) {
$relationship = $this->base_table;
Earl Miles
committed
}
if (!array_key_exists($relationship, $this->relationships)) {
return FALSE;
}
// If we do not have join info, fetch it.
Earl Miles
committed
if (!isset($join)) {
$join = $this->get_join_data($table, $this->relationships[$relationship]['base']);
Earl Miles
committed
}
// If it can't be fetched, this won't work.
if (empty($join)) {
return FALSE;
}
Earl Miles
committed
// Does a table along this path exist?
if (isset($this->tables[$relationship][$table]) ||
($join && $join->left_table == $relationship) ||
($join && $join->left_table == $this->relationships[$relationship]['table'])) {
Earl Miles
committed
// Make sure that we're linking to the correct table for our relationship.
Earl Miles
committed
foreach (array_reverse($add) as $table => $path_join) {
$this->queue_table($table, $relationship, $this->adjust_join($path_join, $relationship));
Earl Miles
committed
}
Earl Miles
committed
}
// Have we been this way?
if (isset($traced[$join->left_table])) {
// we looped. Broked.
Earl Miles
committed
}
// Do we have to add this table?
$left_join = $this->get_join_data($join->left_table, $this->relationships[$relationship]['base']);
Earl Miles
committed
if (!isset($this->tables[$relationship][$join->left_table])) {
$add[$join->left_table] = $left_join;
}
// Keep looking.
$traced[$join->left_table] = TRUE;
return $this->ensure_path($join->left_table, $relationship, $left_join, $traced, $add);
}
/**
* Fix a join to adhere to the proper relationship; the left table can vary
* based upon what relationship items are joined in on.
*/
function adjust_join($join, $relationship) {
Earl Miles
committed
if (!empty($join->adjusted)) {
return $join;
}
if (empty($relationship) || empty($this->relationships[$relationship])) {
return $join;
}
Earl Miles
committed
// Adjusts the left table for our relationship.
if ($relationship != $this->base_table) {
Earl Miles
committed
// If we're linking to the primary table, the relationship to use will
// be the prior relationship. Unless it's a direct link.
Earl Miles
committed
Earl Miles
committed
// Safety! Don't modify an original here.
$join = drupal_clone($join);
Earl Miles
committed
// Do we need to try to ensure a path?
if ($join->left_table != $this->relationships[$relationship]['table'] &&
$join->left_table != $this->relationships[$relationship]['base'] &&
!isset($this->tables[$relationship][$join->left_table]['alias'])) {
$this->ensure_table($join->left_table, $relationship);
}
Earl Miles
committed
// First, if this is our link point/anchor table, just use the relationship
Earl Miles
committed
if ($join->left_table == $this->relationships[$relationship]['table']) {
Earl Miles
committed
$join->left_table = $relationship;
}
// then, try the base alias.
else if (isset($this->tables[$relationship][$join->left_table]['alias'])) {
$join->left_table = $this->tables[$relationship][$join->left_table]['alias'];
}
// But if we're already looking at an alias, use that instead.
else if (isset($this->table_queue[$relationship]['alias'])) {
$join->left_table = $this->table_queue[$relationship]['alias'];
}
}
Earl Miles
committed
$join->adjusted = TRUE;
Earl Miles
committed
return $join;
}
/**
* Retrieve join data from the larger join data cache.
*
* The table to get the join information for.
* The path we're following to get this join.
*
Earl Miles
committed
* @return
* A views_join object or child object, if one exists.
Earl Miles
committed
*/
function get_join_data($table, $base_table) {
Earl Miles
committed
// Check to see if we're linking to a known alias. If so, get the real
// table's data instead.
if (!empty($this->table_queue[$table])) {
$table = $this->table_queue[$table]['table'];
}
return views_get_table_join($table, $base_table);
Earl Miles
committed
}
/**
Earl Miles
committed
* Get the information associated with a table.
Earl Miles
committed
*
* If you need the alias of a table with a particular relationship, use
* ensure_table().
*/
function get_table_info($table) {
if (!empty($this->table_queue[$table])) {
return $this->table_queue[$table];
}
// In rare cases we might *only* have aliased versions of the table.
if (!empty($this->tables[$this->base_table][$table])) {
$alias = $this->tables[$this->base_table][$table]['alias'];
if (!empty($this->table_queue[$alias])) {
return $this->table_queue[$alias];
}
}
Earl Miles
committed
}
Earl Miles
committed
* Add a field to the query table, possibly with an alias. This will
* automatically call ensure_table to make sure the required table
* exists, *unless* $table is unset.
*
* @param $table
* The table this field is attached to. If NULL, it is assumed this will
* be a formula; otherwise, ensure_table is used to make sure the
* table exists.
* @param $field
* The name of the field to add. This may be a real field or a formula.
* @param $alias
* The alias to create. If not specified, the alias will be $table_$field
* unless $table is NULL. When adding formulae, it is recommended that an
* alias be used.
*
* @return $name
* The name that this field can be referred to as. Usually this is the alias.
*/
Earl Miles
committed
function add_field($table, $field, $alias = '', $params = NULL) {
// We check for this specifically because it gets a special alias.
if ($table == $this->base_table && $field == $this->base_field && empty($alias)) {
$alias = $this->base_field;
Earl Miles
committed
}
if ($table && empty($this->table_queue[$table])) {
Earl Miles
committed
$this->ensure_table($table);
}
if (!$alias && $table) {
$alias = $table . '_' . $field;
}
$name = $alias ? $alias : $field;
Earl Miles
committed
// @todo FIXME -- $alias, then $name is inconsistent
Earl Miles
committed
if (empty($this->fields[$alias])) {
$this->fields[$name] = array(
'field' => $field,
'table' => $table,
'alias' => $alias,
);
}
Earl Miles
committed
foreach ((array)$params as $key => $value) {
$this->fields[$name][$key] = $value;
}
Earl Miles
committed
return $name;
}
Earl Miles
committed
* Remove all fields that may've been added; primarily used for summary
* mode where we're changing the query because we didn't get data we needed.
*/
function clear_fields() {
$this->fields = array();
}
/**
* Create a new grouping for the WHERE or HAVING clause.
Earl Miles
committed
*
* @param $type
* Either 'AND' or 'OR'. All items within this group will be added
* to the WHERE clause with this logical operator.
Earl Miles
committed
* @param $group
* An ID to use for this group. If unspecified, an ID will be generated.
* @param $where
* 'where' or 'having'.
Earl Miles
committed
*
* @return $group
* The group ID generated.
*/
function set_where_group($type = 'AND', $group = NULL, $where = 'where') {
// Set an alias.
$groups = &$this->$where;
Earl Miles
committed
if (!isset($group)) {
$group = max(array_keys($groups)) + 1;
Earl Miles
committed
}
// Create an empty group
if (empty($groups[$group])) {
$groups[$group] = array('clauses' => array(), 'args' => array());
Earl Miles
committed
}
$groups[$group]['type'] = strtoupper($type);
Earl Miles
committed
return $group;
}
/**
* Control how all WHERE and HAVING groups are put together.
Earl Miles
committed
*
* @param $type
* Either 'AND' or 'OR'
*/
function set_group_operator($type = 'AND') {
$this->group_operator = strtoupper($type);
}
Earl Miles
committed
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
* Add a simple WHERE clause to the query. The caller is responsible for
* ensuring that all fields are fully qualified (TABLE.FIELD) and that
* the table already exists in the query.
*
* @param $group
* The WHERE group to add these to; groups are used to create AND/OR
* sections. Groups cannot be nested. Use 0 as the default group.
* If the group does not yet exist it will be created as an AND group.
* @param $clause
* The actual clause to add. When adding a where clause it is important
* that all tables are addressed by the alias provided by add_table or
* ensure_table and that all fields are addressed by their alias wehn
* possible. Please use %d and %s for arguments.
* @param ...
* A number of arguments as used in db_query(). May be many args or one
* array full of args.
*/
function add_where($group, $clause) {
$args = func_get_args();
array_shift($args); // ditch $group
array_shift($args); // ditch $clause
// Expand an array of args if it came in.
if (count($args) == 1 && is_array(reset($args))) {
$args = current($args);
}
Earl Miles
committed
// Ensure all variants of 0 are actually 0. Thus '', 0 and NULL are all
// the default group.
if (empty($group)) {
$group = 0;
}
Earl Miles
committed
// Check for a group.
if (!isset($this->where[$group])) {
$this->set_where_group('AND', $group);
}
// Add the clause and the args.
if (is_array($args)) {
$this->where[$group]['clauses'][] = $clause;
// we use array_values() here to prevent array_merge errors as keys from multiple
// sources occasionally collide.
$this->where[$group]['args'] = array_merge($this->where[$group]['args'], array_values($args));
Earl Miles
committed
}
}
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
/**
* Add a simple HAVING clause to the query. The caller is responsible for
* ensuring that all fields are fully qualified (TABLE.FIELD) and that
* the table and an appropriate GROUP BY already exist in the query.
*
* @param $group
* The HAVING group to add these to; groups are used to create AND/OR
* sections. Groups cannot be nested. Use 0 as the default group.
* If the group does not yet exist it will be created as an AND group.
* @param $clause
* The actual clause to add. When adding a having clause it is important
* that all tables are addressed by the alias provided by add_table or
* ensure_table and that all fields are addressed by their alias wehn
* possible. Please use %d and %s for arguments.
* @param ...
* A number of arguments as used in db_query(). May be many args or one
* array full of args.
*/
function add_having($group, $clause) {
$args = func_get_args();
array_shift($args); // ditch $group
array_shift($args); // ditch $clause
// Expand an array of args if it came in.
if (count($args) == 1 && is_array(reset($args))) {
$args = current($args);
}
// Ensure all variants of 0 are actually 0. Thus '', 0 and NULL are all
// the default group.
if (empty($group)) {
$group = 0;
}
// Check for a group.
if (!isset($this->having[$group])) {
$this->set_where_group('AND', $group, 'having');
}
// Add the clause and the args.
if (is_array($args)) {
$this->having[$group]['clauses'][] = $clause;
$this->having[$group]['args'] = array_merge($this->having[$group]['args'], array_values($args));
}
}
Earl Miles
committed
* Add multiple an orderby's. Right now I'm not sure why this function
* is separate from add_orderby above; I don't think it needs to
* be.
*
* @param $table
* The table this field is part of. If a formula, enter NULL.
* @param $field
* The field or formula to sort on. If already a field, enter NULL
* and put in the alias.
Earl Miles
committed
* @param $order
* Either ASC or DESC.
* @param $alias
* The alias to add the field as. In SQL, all fields in the order by
* must also be in the SELECT portion. If an $alias isn't specified
* one will be generated for from the $field; however, if the
* $field is a formula, this alias will likely fail.
*/
function add_orderby($table, $field, $order, $alias = '') {
if ($table) {
$this->ensure_table($table);
}
// Only fill out this aliasing if there is a table;
// otherwise we assume it is a formula.
if (!$alias && $table) {
$as = $table . '_' . $field;
Earl Miles
committed
}
else {
$as = $alias;
}
if ($field) {
$this->add_field($table, $field, $as);
}
Earl Miles
committed
$this->orderby[] = "$as " . strtoupper($order);
Earl Miles
committed
// If grouping, all items in the order by must also be in the
Earl Miles
committed
// group by clause. Check $table to ensure that this is not a
// formula.
if ($this->groupby && $table) {
Earl Miles
committed
$this->add_groupby($as);
}
}
Earl Miles
committed
* Add a simple GROUP BY clause to the query. The caller is responsible
* for ensuring that the fields are fully qualified and the table is properly
* added.
*/
function add_groupby($clause) {
// Only add it if it's already in there.
if (!in_array($clause, $this->groupby)) {
$this->groupby[] = $clause;
}
Earl Miles
committed
}
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
/**
* Construct the "WHERE" or "HAVING" part of the query.
*
* @param $where
* 'where' or 'having'.
*/
function condition_sql($where = 'where') {
$clauses = array();
foreach ($this->$where as $group => $info) {
$clause = implode(") " . $info['type'] . " (", $info['clauses']);
if (count($info['clauses']) > 1) {
$clause = '(' . $clause . ')';
}
$clauses[] = $clause;
}
if ($clauses) {
$keyword = drupal_strtoupper($where);
if (count($clauses) > 1) {
return "$keyword (" . implode(")\n " . $this->group_operator . ' (', $clauses) . ")\n";
}
else {
return "$keyword " . array_shift($clauses) . "\n";
}
}
return "";
}
Earl Miles
committed
Earl Miles
committed
* Generate a query and a countquery from all of the information supplied
* to the object.
*
* @param $get_count
* Provide a countquery if this is true, otherwise provide a normal query.
*/
function query($get_count = FALSE) {
Earl Miles
committed
// Check query distinct value.
if (empty($this->no_distinct) && $this->distinct && !empty($this->fields)) {
if (!empty($this->fields[$this->base_field])) {
$this->fields[$this->base_field]['distinct'] = TRUE;
}
Earl Miles
committed
}
/**
* An optimized count query includes just the base field instead of all the fields.
* Determine of this query qualifies by checking for a groupby or distinct.
*/
$fields_array = $this->fields;
if ($get_count && !$this->groupby) {
foreach ($fields_array as $field) {
if (isset($field['distinct'])) {
$get_count_optimized = FALSE;
break;
}
}
}
else {
$get_count_optimized = FALSE;
}
if (!isset($get_count_optimized)) {
$get_count_optimized = TRUE;
}
Earl Miles
committed
$joins = $fields = $where = $having = $orderby = $groupby = '';
Earl Miles
committed
// Add all the tables to the query via joins. We assume all LEFT joins.
foreach ($this->table_queue as $table) {
if (is_object($table['join'])) {
$joins .= $table['join']->join($table, $this) . "\n";
}
}
$has_aggregate = FALSE;
$non_aggregates = array();
Earl Miles
committed
foreach ($fields_array as $field) {
Earl Miles
committed
}
$string = '';
Earl Miles
committed
$string .= $field['table'] . '.';
}
$string .= $field['field'];
// store for use with non-aggregates below
$fieldname = (!empty($field['alias']) ? $field['alias'] : $string);
Earl Miles
committed
if (isset($field['distinct'])) {
$string = "DISTINCT($string)";
}
if (isset($field['count'])) {
$string = "COUNT($string)";
$has_aggregate = TRUE;
}
else {
$non_aggregates[] = $fieldname;
Earl Miles
committed
}
if ($field['alias']) {
$string .= " AS $field[alias]";
}
$fields .= $string;
if ($get_count_optimized) {
// We only want the first field in this case.
break;
}
Earl Miles
committed
}
if ($has_aggregate || $this->groupby) {
$groupby = "GROUP BY " . implode(', ', array_unique(array_merge($this->groupby, $non_aggregates))) . "\n";
if ($this->having) {
$having = $this->condition_sql('having');
}
Earl Miles
committed
// we only add the groupby if we're not counting.
if ($this->orderby) {
$orderby = "ORDER BY " . implode(', ', $this->orderby) . "\n";
}
}
$where = $this->condition_sql();
Earl Miles
committed
$query = "SELECT $fields\n FROM {" . $this->base_table . "} $this->base_table \n$joins $where $groupby $having $orderby";
Earl Miles
committed
$replace = array('>' => '>', '<' => '<');
$query = strtr($query, $replace);
return $query;
}
/**
* Get the arguments attached to the WHERE and HAVING clauses of this query.
Earl Miles
committed
*/
function get_where_args() {
$args = array();
foreach ($this->where as $group => $where) {
$args = array_merge($args, $where['args']);
}
foreach ($this->having as $group => $having) {
$args = array_merge($args, $having['args']);
}
Earl Miles
committed
return $args;
}
}