Location: PHPKode > projects > Outlet > classes/outlet/OutletQuery.php
<?php
/**
 * @package outlet
 */
class OutletQuery {
	/**
	 * @var Outlet
	 */
	private $outlet;
	private $from;
	private $with = array();
	private $joins = array();
	private $query;
	private $params = array();
	private $orderby;
	private $limit;
	private $offset;
	private $select;
	private $groupBy;
	private $having;
	
	/**
	 * Constructs a new instance of OutletQuery
	 * @param Outlet $outlet 
	 * @return OutletQuery instance
	 */
	public function __construct (Outlet $outlet) {
		$this->outlet = $outlet;
	}
	
	/**
	 * Entity table to select from
	 * @param string $from
	 * @return OutletQuery for fluid interface
	 */
	function from ($from) {
		$this->from = $from;
		
		return $this;
	}
	
	/**
	 * Where clause to filter results by
	 * @param string $q where clause
	 * @param array $params parameters
	 * @return OutletQuery for fluid interface
	 */
	function where ($q, array $params=array()) {
		$this->query = $q;
		$this->params = $params;
		
		return $this;
	}
	
	/**
	 * Declare an inner join
	 * @param string $join entity table to join on
	 * @return OutletQuery for fluid interface
	 */
	function innerJoin ($join) {
		$this->joins[] = 'INNER JOIN ' . $join . "\n";
		
		return $this;
	}

	/**
	 * Declare a left join
	 * @param string $join entity table to join on
	 * @return OutletQuery for fluid interface
	 */
	function leftJoin ($join) {
		$this->joins[] = 'LEFT JOIN ' . $join . "\n";

		return $this;
	}
	
	/**
	 * Include associated entity tables
	 * @todo review code with respect to comment
	 * @param string... variadiac entity tables to include
	 * @return OutletQuery for fluid interface
	 */
	function with () {
		/*
		 * Because this overwrites the with variable this function behaves in a slightly unexpected way
		 * Take for instance this case:
		 * 
		 * $bugs = $outlet->from("Bug")
		 * 				  ->with("Project")
		 * 				  ->with("User")
		 * 				  ->where("{Bug.StatusID} = ? AND {User.ID} = ?", array(1, $currentUser));
		 * 				  ->limit(10)
		 * 				  ->offset(20)
		 * 				  ->find();
		 * 
		 * There won't be any project information because the second with() overrides the first
		 * 
		 * The code that would work is
		 * 
		 * $bugs = $outlet->from("Bug")
		 * 				  ->with("Project", "User")->....
		 * 
		 * It seems like both should work, we can replace the code below with
		 * 
		 * $this->with = array_merge($this->with, func_get_args());
		 * 
		 * And it will allow both calls to function properly
		 */
		$this->with = func_get_args();
		
		return $this;
	}
	
	/**
	 * Declare an ordering
	 * @param string $v Order clause 
	 * @return OutletQuery for fluid interface
	 */
	function orderBy ($v) {
		$this->orderby = $v;
		
		return $this;
	}
	
	/**
	 * Declare a limit to the result set
	 * @param int $num Number of results to return 
	 * @return OutletQuery for fluid interface
	 */
	function limit ($num) {
		$this->limit = $num;
		
		return $this;
	}
	
	/**
	 * Declare an extra column to select
	 * @param string $s column
	 * @return OutletQuery for fluid interface
	 */
	function select ($s) {
		$this->select = $s;
		
		return $this;
	}
	
	/**
	 * Declare an offset for the result set
	 * @param int $num Offset to begin returning result set at 
	 * @return OutletQuery for fluid interface
	 */
	function offset ($num) {
		$this->offset = $num;
		
		return $this;
	}
	
	/**
	 * Declare a grouping
	 * @param string $s column to group by
	 * @return OutletQuery for fluid interface
	 */
	function groupBy($s) {
		$this->groupBy = $s;
		
		return $this;
	}
	
	/**
	 * Declare a having clause
	 * @param string $s having condition
	 * @return OutletQuery for fluid interface
	 */
	function having($s) {
		$this->having = $s;
		
		return $this;
	}
	
	/**
	 * Execute the query
	 * @return array result set
	 */
	function find () {
		$outlet = $this->outlet;
		
		// get the 'from'
		$tmp = explode(' ', $this->from);

		$from = $tmp[0];
		$from_aliased = (count($tmp)>1 ? $tmp[1] : $tmp[0]);

		$config = $this->outlet->getConfig();
		$entity_config = $config->getEntity($from);
		$props = $entity_config->getProperties();
		
		$from_props = $props;
		
		$select_cols = array();
		foreach ($props as $key=>$p) {
			$select_cols[] = "\n{".$from_aliased.'.'.$key.'} as '.$from_aliased.'_'.$key;
		}
		
		// get the include entities
		$with = array();
		$with_aliased = array();
		
		$join_q = '';
		foreach ($this->with as $with_key=>$j) {
			$tmp = explode(' ', $j);
			
			$with[$with_key] = $tmp[0];
			$with_aliased[$with_key] = (count($tmp)>1 ? $tmp[1] : $tmp[0]);
			
			$assoc = $entity_config->getAssociation($with[$with_key]);

			if (!$assoc) throw new OutletException('No association found with entity or alias ['.$with[$with_key].']');
			
			$props = $config->getEntity($assoc->getForeign())->getProperties();
			foreach ($props as $key=>$p) {
				$select_cols[] = "\n{".$with_aliased[$with_key].'.'.$key.'} as '.$with_aliased[$with_key].'_'.$key;
			}
		
			$aliased_join = $with_aliased[$with_key];
			$join_q .= "LEFT JOIN {".$assoc->getForeign()." ".$aliased_join."} ON {".$from_aliased.'.'.$assoc->getKey()."} = {".$with_aliased[$with_key].'.'.$assoc->getRefKey()."} \n";
		}
		
		$q = "SELECT ".implode(', ', $select_cols)." \n";
		
		if ($this->select) $q .= ", ".$this->select;
		
		$q .= " FROM {".$this->from."} \n";
		$q .= $join_q;
		
		$q .= implode("\n", $this->joins);
		
		if ($this->query) 		$q .= 'WHERE ' . $this->query."\n";
		if ($this->groupBy)		$q .= 'GROUP BY ' . $this->groupBy."\n";
		if ($this->orderby) 	$q .= 'ORDER BY ' . $this->orderby . "\n";
		if ($this->having)		$q .= 'HAVING ' . $this->having . "\n";
		
		// TODO: Make it work on MS SQL
		//	   In SQL Server 2005 http://www.singingeels.com/Articles/Pagination_In_SQL_Server_2005.aspx
		if ($this->limit){
			$q .= 'LIMIT '.$this->limit;
			if ($this->offset)
				$q .= ' OFFSET '.$this->offset;
		}
	
		$stmt = $outlet->query($q, $this->params);
		
		$res = array();
		while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
			$data = array();
			// Postgres returns columns as lowercase
			// TODO: Maybe everything should be converted to lower in query creation / processing to avoid this
			if ($outlet->getConnection()->getDialect() == 'pgsql')
				foreach ($from_props as $key=>$p) {
					$data[$p[0]] = $row[strtolower($from_aliased).'_'.strtolower($key)];
				}
			else
				foreach ($from_props as $key=>$p) {
					$data[$p[0]] = $row[$from_aliased.'_'.$key];
				}

			$obj = $outlet->getEntityForRow($entity_config, $data);
		
			foreach ($with as $with_key=>$w) {
				$a = $entity_config->getAssociation($w);
				
				if ($a) {
					$data = array();					
					$setter = $a->getSetter();
					$foreign = $a->getForeign();
					$with_entity = $config->getEntity($foreign);
					
					if ($a instanceof OutletOneToManyConfig)
					{
						// TODO: Implement...											 
					}
					elseif ($a instanceof OutletManyToManyConfig)
					{
						// TODO: Implement...
					}
					// Many-to-one or one-to-one
					else
					{
						// Postgres returns columns as lowercase
						// TODO: Maybe everything should be converted to lower in query creation / processing to avoid this
						if ($outlet->getConnection()->getDialect() == 'pgsql')
							foreach ($with_entity->getProperties() as $key=>$p) {
								$data[$p[0]] = $row[strtolower($with_aliased[$with_key].'_'.$key)];
							}
						else
							foreach ($with_entity->getProperties() as $key=>$p) {
								$data[$p[0]] = $row[$with_aliased[$with_key].'_'.$key];
							}
							
						$f = $with_entity->getPkColumns();
						
						// check to see if we found any data for the related entity
						// using the pk
						$data_returned = false;
						$pk_values = array();
						foreach ($f as $k) {
							if (isset($data[$k])) {
								$data_returned = true;
								break;
							}
						}
						
						// only fill object if there was data returned
						if ($data_returned) $obj->$setter($outlet->getEntityForRow($with_entity, $data));
					}
				}
			}
			
			$res[] = $obj;
		}
		
		return $res;
	}
	
	/**
	 * Executes query returning the first result out of the result set
	 * @todo review code suggestion below
	 * @return object first result out of the result set
	 */
	public function findOne () {
		/*
		 * It would improve performance to replace the code below with
		 * $res = $this->limit(1)->find();
		 * 
		 * There is no need to do an unbound search if we only want one result
		 */
		$res = $this->find();
		
		if (count($res)) return $res[0];
	}
}
Return current item: Outlet