Location: PHPKode > scripts > SQLReactor > SQLReactor-0.6/SQLReactor/engines/default/SQLReactorQuery.php
<?php
/*******************************************************************************
* Copyright 2008 Rafael Marques Martins
*
* This file is part of SQLReactor.
* 
* SQLReactor is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
* 
* SQLReactor is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
* GNU General Public License for more details.
* 
* You should have received a copy of the GNU General Public License
* along with SQLReactor; if not, write to the Free Software
* Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
* 
*******************************************************************************/

	class SQLReactorQuery{
		public $from;
		public $join = array();
		public $where = array();
		public $orderBy = array();
		public $eagerload = array();
		public $_returnClasses = array();
		protected $_joinClauses = array();
		public $sqlStructure;
			
        public function __construct(){
            $this->sqlStructure = new stdClass();
            $this->sqlStructure->join = array();
        }
			
		public function setFilter( $filter ){
			$this->where = $this->parseFilter( $filter );
		}
		
		public function parseFilter( $filter = array() ){
			$clauses = array();
			
            $this->sqlStructure->fields = array();
			
			foreach( get_object_vars( $this->from->_table->columns ) as $columnName => $column ){
				if( !( $column instanceOf ForeignKey || $column instanceOf Backref ) ){
                    $tmpCol = new stdClass();
                    $tmpCol->table = $this->from->_table->name;
                    $tmpCol->name = $column->name;
                    $tmpCol->column = $column;
                    $this->sqlStructure->fields[] = $tmpCol;
				}
			}
            $this->tableList = array();
            $this->tableIndex = array( $this->from->_table->name => 1 );
            $this->orderedTableList = array( $this->from->_table->name );
            $this->tmpClauses = array();
			
            if( is_null( $filter ) ){
                $filter = array();
            }
            
			foreach( $filter as $clauseIndex => $clause ){
				if( count( $clause ) == 2 ){
					$clause = array( $clause[ 0 ], '==', $clause[ 1 ] );
				}
				if( $clause[ 1 ] == 'or' ){
					$clauses[] =  new SQLReactorClause( $this, $this->parseFilter( $clause[ 0 ] ), 'or', $this->parseFilter( $clause[ 2 ] ) );
				}else{
					$clauses[] = new SQLReactorClause( $this, $clause[ 0 ], $clause[ 1 ], $clause[ 2 ] );
					
					$tmpClause = explode( "->", $clause[ 0 ] );
					$tmpInstance = $this->from;
					
					foreach( $tmpClause as $tmpIndex => $tmpName ){
						$column = $tmpInstance->_table->columns->$tmpName;
						if( $column instanceOf Backref || $column instanceOf ForeignKey ){
                            $clauseName = implode( "->", array_slice( $tmpClause, 0, $tmpIndex + 1 ) );
                            
                            
							$className = $column->config[ 'target' ][ 0 ];
							$tmpInstance = new $className();
							
                            $this->addAlias( $clauseName, $tmpInstance->_table->name );                            
                            
                            $tmpTable = new stdClass();
                            $tmpTable->name = $tmpInstance->_table->name;
                            $tmpTable->alias = $this->getAlias( $clauseName );
                            $tmpTable->clauses = array();
                            $tmpTable->class = $className;
                            $this->sqlStructure->join[ $clauseName ] = $tmpTable;
						}
					}
				}
			}
            
			return $clauses;
		}
        
        
        
        public function addAlias( $clauseName, $table ){
            if( !$this->getAlias( $clauseName ) ){
                if( $this->tableIndex[ $table ] ){
                    $this->tableList[ $clauseName ] = "{$table}_{$this->tableIndex[ $table ]}";
                }else{
                    $this->tableList[ $clauseName ] = $table;
                }
                $this->orderedTableList[] = $this->tableList[ $clauseName ];
                
                $this->tableIndex[ $table ]++;
            }
        }
        
        public function addEagerAlias( $clauseName, $table ){
            if( !$this->getEagerAlias( $clauseName ) ){
                if( $this->tableIndex[ $table ] ){
                    $this->eagerList[ $clauseName ] = "{$table}_{$this->tableIndex[ $table ]}";
                }else{
                    $this->eagerList[ $clauseName ] = $table;
                }
                $this->tableIndex[ $table ]++;
            }
        }
        
        public function getEagerAlias( $clauseName, $table = null ){
            if( $this->eagerList[ $clauseName ] ){
                return $this->eagerList[ $clauseName ];
            }
            return $table;
        }
        
        public function getAlias( $clauseName, $table = null ){
            if( $this->tableList[ $clauseName ] ){
                return $this->tableList[ $clauseName ];
            }
            return $table;
        }
        
        public function prepareJoins( $instance, $clauseName ){
            foreach( get_object_vars( $instance->_table->columns ) as $columnName => $column ){
                if( ! ( $column instanceOf ForeignKey || $column instanceOf Backref ) ) continue;
                
                $tmpClauseName = $clauseName ? "{$clauseName}->{$columnName}" : $columnName;
                
                if( $this->sqlStructure->join[ $tmpClauseName ] ){
                    $targetClass = $column->config[ 'target' ][ 0 ];
                    $targetAttr  = $column->config[ 'target' ][ 1 ];
                    $target = new $targetClass();
                    
                    if( !$clauseName ){
                        if( $column instanceOf ForeignKey ){
                            $clausePart1 = array( $this->from->_table->name, $instance->_columnNames->{"{$columnName}Id"} );
                            $clausePart2 = array( $this->getTableName( $this->sqlStructure->join[ $columnName ] ), $target->_columnNames->$targetAttr );
                        }else{
                            $clausePart1 = array( $this->getTableName( $this->sqlStructure->join[ $columnName ] ), $target->_columnNames->{"{$targetAttr}Id"} );
                            $tmpAttrName = $target->_table->columns->{ $column->config['target'][ 1 ] }->config[ 'target' ][ 1 ];
                            $clausePart2 = array( $this->from->_table->name, $this->from->_columnNames->$tmpAttrName );                        }
                            
                        $this->sqlStructure->join[ $columnName ]->clauses[] = array( $clausePart1, $clausePart2 );
                    }else{
                        if( $column instanceOf ForeignKey ){
                            $clausePart1 = array( $this->getTableName( $this->sqlStructure->join[ $clauseName ] ), $instance->_columnNames->{"{$columnName}Id"} );
                            $clausePart2 = array( $this->getTableName( $this->sqlStructure->join[ $tmpClauseName ] ), $target->_columnNames->$targetAttr );
                        }else{
                            $clausePart1 = array( $this->getTableName( $this->sqlStructure->join[ "{$clauseName}->{$columnName}" ] ), $target->_columnNames->{"{$targetAttr}Id"} );
                            $tmpAttrName = $target->_table->columns->{ $column->config['target'][ 1 ] }->config[ 'target' ][ 1 ];
                            $clausePart2 = array( $this->getTableName( $this->sqlStructure->join[ $clauseName ] ), $instance->_columnNames->$tmpAttrName );
                        }
                        
                        $this->sqlStructure->join[ $tmpClauseName ]->clauses[] = array( $clausePart1, $clausePart2 );
                    }
                    $this->prepareJoins( $target, "{$tmpClauseName}" );
                }
            }
		}
        
        public function prepareEagerClauses( $instance, $clauseName ){
            foreach( get_object_vars( $instance->_table->columns ) as $columnName => $column ){
                if( ! ( $column instanceOf ForeignKey || $column instanceOf Backref ) ) continue;
                
                $tmpClauseName = $clauseName ? "{$clauseName}->{$columnName}" : $columnName;
                
                if( $this->sqlStructure->eager[ $tmpClauseName ] ){
                    $targetClass = $column->config[ 'target' ][ 0 ];
                    $targetAttr  = $column->config[ 'target' ][ 1 ];
                    $target = new $targetClass();
                    
                    if( !$clauseName ){
                        if( $column instanceOf ForeignKey ){
                            $clausePart1 = array( $this->from->_table->name, $instance->_columnNames->{"{$columnName}Id"} );
                            $clausePart2 = array( $this->getTableName( $this->sqlStructure->eager[ $columnName ] ), $target->_columnNames->$targetAttr );
                        }else{
                            $clausePart1 = array( $this->getTableName( $this->sqlStructure->eager[ $columnName ] ), $target->_columnNames->{"{$targetAttr}Id"} );
                            $tmpAttrName = $target->_table->columns->{ $column->config['target'][ 1 ] }->config[ 'target' ][ 1 ];
                            $clausePart2 = array( $this->from->_table->name, $this->from->_columnNames->$tmpAttrName );
                        }
                        
                        $this->sqlStructure->eager[ $columnName ]->clauses[] = array( $clausePart1, $clausePart2 );
                    }else{
                        if( $column instanceOf ForeignKey ){
                            $clausePart1 = array( $this->getTableName( $this->sqlStructure->eager[ $clauseName ] ), $instance->_columnNames->{"{$columnName}Id"} );
                            $clausePart2 = array( $this->getTableName( $this->sqlStructure->eager[ $tmpClauseName ] ), $target->_columnNames->$targetAttr );
                        }else{                            
                            $clausePart1 = array( $this->getTableName( $this->sqlStructure->eager[ "{$clauseName}->{$columnName}" ] ), $target->_columnNames->{"{$targetAttr}Id"} );
                            $tmpAttrName = $target->_table->columns->{ $column->config['target'][ 1 ] }->config[ 'target' ][ 1 ];
                            $clausePart2 = array( $this->getTableName( $this->sqlStructure->eager[ $clauseName ] ), $instance->_columnNames->$tmpAttrName );
                        }
                        
                        $this->sqlStructure->eager[ $tmpClauseName ]->clauses[] = array( $clausePart1, $clausePart2 );
                    }
                    $this->prepareEagerClauses( $target, "{$tmpClauseName}" );
                }
            }
		}
        
        public function getTableName( $table ){
            if( $table->alias && $table->alias != $table->name ){
                return $table->alias;
            }
            
            return $table->name;
        }
		
		protected function prepareEagers(){
            $this->sqlStructure->eager = array();

            if( !$this->eagerload ) return;
            
			foreach( $this->eagerload as $name ){				
				$tmpClause = explode( "->", $name );
				$tmpInstance = $this->from;
				
                
				foreach( $tmpClause as $tmpIndex => $tmpName ){
                    $clauseName = implode( "->", array_slice( $tmpClause, 0, $tmpIndex + 1 ) );
                    
					$column = $tmpInstance->_table->columns->$tmpName;
                    
					if( $column instanceOf Backref || $column instanceOf ForeignKey ){
						$className = $column->config[ 'target' ][ 0 ];
						$tmpInstance = new $className();
                        
                        $this->addEagerAlias( $clauseName, $tmpInstance->_table->name );
                        
                        $tmpTable = new stdClass();
                        $tmpTable->name = $tmpInstance->_table->name;
                        $tmpTable->alias = $this->getEagerAlias( $clauseName );
                        $tmpTable->clauses = array();
                        $tmpTable->class = $className;
                        $tmpTable->pk = array();
                        
                        foreach( $tmpInstance->_primaryKey as $pk ){
                            $tmpTable->pk[] = $pk;
                        }
                        
                        $this->sqlStructure->eager[ $clauseName ] = $tmpTable;
                        
                        
                        foreach( get_object_vars( $tmpInstance->_table->columns ) as $tmpColName => $fColumn ){
                            if( $fColumn instanceOf Backref || $fColumn instanceOf ForeignKey ) continue;
                            $tmpCol = new stdClass();
                            $tmpCol->table = $tmpTable->alias;
                            $tmpCol->name = $fColumn->name;
                            $tmpCol->column = $fColumn;
                            $this->sqlStructure->fields[] = $tmpCol;
                        }
					}
				}
			}
		}
		
        public function getSQL(){
            $escapeChar = $this->from->_connection->engine->escapeName;            
            
            $sql = "\nSELECT\n\t";
            $tmp = array();
            foreach( $this->sqlStructure->fields as $field ){
                if( $field->table == $this->from->_table->name ){
                    $tmp []= $field->column->getModifiedSQL( "{$escapeChar}_A_N_O_N_1_{$escapeChar}.{$escapeChar}{$field->name}{$escapeChar}" ) . " AS {$escapeChar}{$field->table}__{$field->name}{$escapeChar}";
                }else{
                    $tmp []= "{$escapeChar}{$field->table}{$escapeChar}.{$escapeChar}{$field->name}{$escapeChar} AS {$escapeChar}{$field->table}__{$field->name}{$escapeChar}";
                }
            }
            $sql .= implode( ",\n\t", $tmp );
            
            $sql .= "\nFROM (\n\t";
            $sql2 = "SELECT\n\t\t";
            
            $sql2 .= "{$escapeChar}{$this->from->_table->name}{$escapeChar}.*";
            
            $sql2 .= "\n\tFROM\n\t{$escapeChar}{$this->from->_table->name}{$escapeChar}\n\t\t";
            
            $tmp = array();
            foreach( $this->sqlStructure->join as $join ){
                if( $join->alias && $join->alias != $join->name ){
                    $tmpStr = "LEFT JOIN {$escapeChar}{$join->name}{$escapeChar} {$escapeChar}{$join->alias}{$escapeChar}";
                }else{
                    $tmpStr = "LEFT JOIN {$escapeChar}{$join->name}{$escapeChar}";
                }
                
                $tmpClauses = array();
                foreach( $join->clauses as $clause ){
                    $tmpClauses[] = "{$escapeChar}{$clause[ 0 ][ 0 ]}{$escapeChar}.{$escapeChar}{$clause[ 0 ][ 1 ]}{$escapeChar} = {$escapeChar}{$clause[ 1 ][ 0 ]}{$escapeChar}.{$escapeChar}{$clause[ 1 ][ 1 ]}{$escapeChar}";
                }
                if( $tmpClauses ){
                    $tmpStr .= "\n\t\t\tON ".implode( "\n\t\t\tAND ", $tmpClauses );
                }
                $tmp[] = $tmpStr;
            }
            $sql2 .= implode( "\n\t\t", $tmp );
            
            
            $where = $this->getFilterSQL();
            if( $where ){
                $sql2 .= "\n\tWHERE\n\t\t" . $where;
            }
            
            $sql2 .= "\n\tORDER BY\n\t\t";
            $tmp = array();
            foreach( $this->from->_primaryKey as $pk ){
                $tmp[] = "{$escapeChar}{$this->from->_table->name}{$escapeChar}.{$escapeChar}{$this->from->_columnNames->$pk}{$escapeChar}";
            }
            $sql2 .= implode( ",", $tmp );
            
            $sql .= $this->from->_connection->engine->addLimitAndOffset( $sql2, $this->limit, $this->offset, $this );
            $sql .= "\n) {$escapeChar}_A_N_O_N_1_{$escapeChar}";
            
            $tmp = array();
            foreach( $this->sqlStructure->eager as $eager ){
                if( $eager->alias && $eager->alias != $eager->name ){
                    $tmpStr = "LEFT OUTER JOIN {$escapeChar}{$eager->name}{$escapeChar} {$escapeChar}{$eager->alias}{$escapeChar}";
                }else{
                    $tmpStr = "LEFT OUTER JOIN {$escapeChar}{$eager->name}{$escapeChar}";
                }
                
                $tmpClauses = array();
                foreach( $eager->clauses as $clause ){
                    $clause[ 0 ][ 0 ] = $clause[ 0 ][ 0 ] == $this->from->_table->name ? '_A_N_O_N_1_' : $clause[ 0 ][ 0 ];
                    $clause[ 0 ][ 1 ] = $clause[ 0 ][ 1 ] == $this->from->_table->name ? '_A_N_O_N_1_' : $clause[ 0 ][ 1 ];
                    $clause[ 1 ][ 0 ] = $clause[ 1 ][ 0 ] == $this->from->_table->name ? '_A_N_O_N_1_' : $clause[ 1 ][ 0 ];
                    $clause[ 1 ][ 1 ] = $clause[ 1 ][ 1 ] == $this->from->_table->name ? '_A_N_O_N_1_' : $clause[ 1 ][ 1 ];
                    $tmpClauses[] = "{$escapeChar}{$clause[ 0 ][ 0 ]}{$escapeChar}.{$escapeChar}{$clause[ 0 ][ 1 ]}{$escapeChar} = {$escapeChar}{$clause[ 1 ][ 0 ]}{$escapeChar}.{$escapeChar}{$clause[ 1 ][ 1 ]}{$escapeChar}";
                }
                if( $tmpClauses ){
                    $tmpStr .= "\n\tON ".implode( "\n\tAND ", $tmpClauses );
                }
                $tmp[] = $tmpStr;
            }
            if( $tmp ){
                $sql .= "\n\n";
                $sql .= implode( "\n", $tmp );
            }
            
            $sql .= "\nORDER BY\n\t";
            $tmp = array();
            foreach( $this->from->_primaryKey as $pk ){
                $tmp[] = "{$escapeChar}{$this->from->_table->name}__{$this->from->_columnNames->$pk}{$escapeChar}";
            }
            
            foreach( $this->sqlStructure->eager as $eager ){
                $tmpClassName = $eager->class;
                $tmpInstance = new $tmpClassName();
                foreach( $eager->pk as $pk ){
                    $tmp[] = "{$escapeChar}{$eager->alias}__{$tmpInstance->_columnNames->$pk}{$escapeChar}";
                }
            }
            
            if( $this->orderBy ){
                $sql .= "{$escapeChar}{$this->from->_table->name}__{$this->from->_columnNames->{$this->orderBy}}{$escapeChar}";
                $sql .= $this->direction ? " {$this->direction}" : ' ASC';
            }else{
                $sql .= implode( ",", $tmp );
            }
            
            
            $sql .= "\n";
            return $sql;
        }
        
        
        public function getSQLCount(){
            $escapeChar = $this->from->_connection->engine->escapeName;            
            
            $tmp = array();
            foreach( $this->from->_primaryKey as $pk ){
                $tmp[] = "{$escapeChar}_A_N_O_N_1_{$escapeChar}.{$escapeChar}{$this->from->_columnNames->$pk}{$escapeChar}";
            }
            
            $sql = "\nSELECT COUNT( ". implode( "||'_'||", $tmp ) ." ) AS {$escapeChar}count{$escapeChar} FROM (\n";
            
            $sql2 = "SELECT\n\t\t";
            
            $sql2 .= "{$escapeChar}{$this->from->_table->name}{$escapeChar}.*";
            
            $sql2 .= "\n\tFROM\n\t{$escapeChar}{$this->from->_table->name}{$escapeChar}\n\t\t";
            
            $tmp = array();
            foreach( $this->sqlStructure->join as $join ){
                if( $join->alias && $join->alias != $join->name ){
                    $tmpStr = "LEFT JOIN {$escapeChar}{$join->name}{$escapeChar} {$escapeChar}{$join->alias}{$escapeChar}";
                }else{
                    $tmpStr = "LEFT JOIN {$escapeChar}{$join->name}{$escapeChar}";
                }
                
                $tmpClauses = array();
                foreach( $join->clauses as $clause ){
                    $tmpClauses[] = "{$escapeChar}{$clause[ 0 ][ 0 ]}{$escapeChar}.{$escapeChar}{$clause[ 0 ][ 1 ]}{$escapeChar} = {$escapeChar}{$clause[ 1 ][ 0 ]}{$escapeChar}.{$escapeChar}{$clause[ 1 ][ 1 ]}{$escapeChar}";
                }
                if( $tmpClauses ){
                    $tmpStr .= "\n\t\t\tON ".implode( "\n\t\t\tAND ", $tmpClauses );
                }
                $tmp[] = $tmpStr;
            }
            $sql2 .= implode( "\n\t\t", $tmp );
            
            
            $where = $this->getFilterSQL();
            if( $where ){
                $sql2 .= "\n\tWHERE\n\t\t" . $where;
            }
            
            $sql .= $this->from->_connection->engine->addLimitAndOffset( $sql2, $this->limit, $this->offset, $this );
            $sql .= "\n) {$escapeChar}_A_N_O_N_1_{$escapeChar}";
            
            $sql .= "\n";
            
            return $sql;
        }
        
        
        public function getEagerName( $tableName ){
            if( $tableName == $this->from->_table->name ){
                return '';
            }
            
            foreach( $this->sqlStructure->eager as $eagerName => $eager ){
                if( $eager->alias == $tableName ){
                    return $eagerName;
                }
            }
            
            return null;
        }
        
        public function getFilterSQL(){
            $tmp = array();
            foreach( $this->where as $clause ){
                $tmp[] = $clause->parse();
            }
            $sql = implode( " AND ", $tmp );
            
            return $sql;
        }
        
		public function parse(){
            $this->prepareEagers();
            $this->prepareJoins( $this->from, '' );
            $this->prepareEagerClauses( $this->from, '' );
            return $this->getSQL();
		}
        
        public function parseCount(){
            $this->prepareEagers();
            $this->prepareJoins( $this->from, '' );
            $this->prepareEagerClauses( $this->from, '' );
            return $this->getSQLCount();
		}
	}

?>
Return current item: SQLReactor