Query Class Reference

Query provides an object-oriented interface for database queries. More...

Inheritance diagram for Query:

Collaboration diagram for Query:

Public Member Functions
	__construct ($class, $constraints="")
	Creates a new Query object. More...
	page ($page, $size)
	Sets the page number and size for constraining the result set by page. More...
	tableAlias ($alias)
	Sometimes when you are creating abonimable SQL constraints you might need to refer back to the main object table by a specific alias. More...
	exists ()
	Return true if the query matches one or more rows in the database. More...
	execute ()
	Executes the query and returns an array of DataItems containing the results (the class of items returned is as specified in the Query constructor). More...
	executeSingle ()
	Executes the query and returns a singleton result row. More...
	executeValue ($func)
	Query the database to calculate an aggregate value. More...
	count ()
	Return the count of matching results. More...
Public Member Functions inherited from AbstractQuery
	constraints ($constraints)
	Sets the constraint clause for the Query. More...
	filter ($filter)
	Sets a filter to constrain the fields retrieved when the query is executed. More...
	params ($params)
	Sets the bound parameters array. More...
	bind ()
	Binds placeholders to parameter values. More...

Static Public Member Functions
static	create ($class, $constraints="")
	Static factory method to create a new Query. More...

Public Attributes
	$page
	$size
	$tableAlias
Public Attributes inherited from AbstractQuery
	$class
	$constraints
	$filter
	$params

Detailed Description

Query provides an object-oriented interface for database queries.

By instantiating Query objects you can specify a full or partial set of criteria and parameters, and then subsequently execute the query to retrieve the results. You can pass around the constructed Query object and execute it multiple times, allowing for lazy evaluation and other performance enhancing tricks.

Author

andy

Definition at line 125 of file query.inc.

Constructor & Destructor Documentation

__construct()

Query::__construct	(		$class,
			$constraints = ""
	)

Creates a new Query object.

Parameters

string	$class	the DataItem class to be queried
string	$constraints	[optional] constraint clause for the query

Returns

a new Query object

Reimplemented from AbstractQuery.

Definition at line 137 of file query.inc.

     {
          parent::__construct($class, $constraints);
          
          $this->page = -1;
          $this->size = -1;
          
          $this->tableAlias = "";
     }

Member Function Documentation

count()

Query::count

(

)

Return the count of matching results.

Definition at line 347 of file query.inc.

     {
          return $this->executeValue("COUNT(1)");
     }

create()

static Query::create (   $class,   $constraints = ""  )

static

Static factory method to create a new Query.

Parameters

string	$class	the DataItem class to be queried
string	$constraints	[optional] constraint clause for the query

Returns

a new Query object

Definition at line 358 of file query.inc.

     {
          return new Query($class, $constraints);
     }

execute()

Query::execute

(

)

Executes the query and returns an array of DataItems containing the results (the class of items returned is as specified in the Query constructor).

Returns

array an array of DataItems containing the results.

Reimplemented from AbstractQuery.

Definition at line 214 of file query.inc.

     {
          $prototype = new $this->class;
          $prototype->filter = $this->filter;
     
          $order_by_idx = strpos(strtoupper($this->constraints), "ORDER BY");
          $orderBy = ""; 
          
          if ($order_by_idx !== false)
          {
               $orderBy = substr($this->constraints, $order_by_idx);
               
               $this->constraints = substr($this->constraints, 0, $order_by_idx);
          } 
          
          if ($this->constraints == "") $this->constraints = "WHERE 1=1"; //TODO - tidy this up some day
          $this->constraints .= " ".$prototype->getIdentityConstraint();
          
          $query = "SELECT ".$prototype->getFieldList()." FROM {$prototype->table} {$this->tableAlias} {$this->constraints} $orderBy";
     
          trace("$query", 3);
          trace("Page: $this->page Size: $this->size", 3);
          $items = array();
          
          $size = $this->size;
          
          try
          {
               $db = ConnectionManager::getConnection();
               
               $result = $db->prepare($query);
               $result->execute($this->params);
               
               if ($this->page > 0)
               {
                    $count = ($this->page - 1) * $this->size;
                    while($count--)
                    {
                         $result->fetch();
                    }
               }
               
               while($line = $result->fetch())
               {
                    $item = new $this->class;
                    $item->filter = $this->filter;
                    $item->populate($line);
                    $items[] = $item;
                    
                    --$size;
                    if ($size == 0) break;
               }
               
               unset($result);
          }
          catch(PDOException $e)
          {
               $err = "query() failed - " . $e->getMessage();
               trace($err, 2);
               throw new DataItemException($err);
          }
          
          trace(count($items)." items found", 3);
          return $items;
     }

executeSingle()

Query::executeSingle

(

)

Executes the query and returns a singleton result row.

If no results match then a DataNotFoundException is thrown. If more than one row matches, then a DataItemException is thrown.

Returns

DataItem the matching DataItem from the database

Definition at line 286 of file query.inc.

     {
          $results = $this->execute();
          if (count($results) == 1)
          {
               return $results[0];
          }
          
          if (count($results) == 0)
          {
               throw new DataNotFoundException();
          }         
          else
          {
               throw new DataItemException("Ambiguous singleton query");
          }
     }

executeValue()

Query::executeValue

(

$func

)

Query the database to calculate an aggregate value.

The database table associated with the specified class is used as the source for the data.

Parameters

string

$func

the value or function to retrieve

Definition at line 309 of file query.inc.

     {
          $prototype = new $this->class;
          
          if ($this->constraints == "") $this->constraints = "WHERE 1=1"; //TODO - tidy this up some day
          $this->constraints .= " ".$prototype->getIdentityConstraint();
               
          $query = "SELECT $func as result FROM {$prototype->table} {$this->tableAlias} {$this->constraints}";
          
          trace($query, 3);
          
          try
          {
               $db = ConnectionManager::getConnection();
               
               $result = $db->prepare($query);
               $result->execute($this->params);
               
               if ($row = $result->fetch())
               {
                    $value = $row['result'];
               }
     
               unset($result);
          }
          catch(PDOException $e)
          {
               $err = "Query::executeValue() failed - " . $e->getMessage();
               trace($err, 2);
               throw new DataItemException($err);
          }
          
          return $value;
     }

exists()

Query::exists

(

)

Return true if the query matches one or more rows in the database.

Definition at line 180 of file query.inc.

     {
          $prototype = new $this->class;
          $pk = $prototype->getPrimaryKey();
          
          $query = "SELECT $pk FROM {$prototype->table} {$this->tableAlias} {$this->constraints} LIMIT 1";
          
          $exists = false;
          
          try
          {
               $db = ConnectionManager::getConnection();
               
               $result = $db->prepare($query);
               $result->execute($this->params);
               
               if ($result->fetch()) $exists = true;
               
               unset($result);
          }
          catch(PDOException $e)
          {
               throw new DataItemException($e->getMessage());
          }
          
          return $exists;
                     
     }

page()

Query::page	(		$page,
			$size
	)

Sets the page number and size for constraining the result set by page.

By default results are not paged.

Parameters

$page	integer the page number
$size	integer the number of items per page

Returns

Query reference to the Query object, to allow for call chaining.

Definition at line 155 of file query.inc.

     {
          $this->page = $page;
          $this->size = $size;
          return $this;
     }

tableAlias()

Query::tableAlias

(

$alias

)

Sometimes when you are creating abonimable SQL constraints you might need to refer back to the main object table by a specific alias.

In those cases, set the table alias for the main object using this message.

Parameters

$alias

String the table alias name

Returns

Query reference to the Query object, to allow for call chaining.

Definition at line 171 of file query.inc.

     {
          $this->tableAlias = $alias;
          return $this;
     }

Member Data Documentation

$page

Query::$page

Definition at line 127 of file query.inc.

$size

Query::$size

Definition at line 128 of file query.inc.

$tableAlias

Query::$tableAlias

Definition at line 129 of file query.inc.

---

The documentation for this class was generated from the following file:

• C:/code/cms.sonjara.com/framework/query.inc