1<?php defined('SYSPATH') OR die('No direct script access.'); 2/** 3 * Database query builder for SELECT statements. See [Query Builder](/database/query/builder) for usage and examples. 4 * 5 * @package Kohana/Database 6 * @category Query 7 * @author Kohana Team 8 * @copyright (c) 2008-2009 Kohana Team 9 * @license http://kohanaphp.com/license 10 */ 11class Kohana_Database_Query_Builder_Select extends Database_Query_Builder_Where { 12 13 // SELECT ... 14 protected $_select = array(); 15 16 // DISTINCT 17 protected $_distinct = FALSE; 18 19 // FROM ... 20 protected $_from = array(); 21 22 // JOIN ... 23 protected $_join = array(); 24 25 // GROUP BY ... 26 protected $_group_by = array(); 27 28 // HAVING ... 29 protected $_having = array(); 30 31 // OFFSET ... 32 protected $_offset = NULL; 33 34 // UNION ... 35 protected $_union = array(); 36 37 // The last JOIN statement created 38 protected $_last_join; 39 40 /** 41 * Sets the initial columns to select from. 42 * 43 * @param array $columns column list 44 * @return void 45 */ 46 public function __construct(array $columns = NULL) 47 { 48 if ( ! empty($columns)) 49 { 50 // Set the initial columns 51 $this->_select = $columns; 52 } 53 54 // Start the query with no actual SQL statement 55 parent::__construct(Database::SELECT, ''); 56 } 57 58 /** 59 * Enables or disables selecting only unique columns using "SELECT DISTINCT" 60 * 61 * @param boolean $value enable or disable distinct columns 62 * @return $this 63 */ 64 public function distinct($value) 65 { 66 $this->_distinct = (bool) $value; 67 68 return $this; 69 } 70 71 /** 72 * Choose the columns to select from. 73 * 74 * @param mixed $columns column name or array($column, $alias) or object 75 * @return $this 76 */ 77 public function select($columns = NULL) 78 { 79 $columns = func_get_args(); 80 81 $this->_select = array_merge($this->_select, $columns); 82 83 return $this; 84 } 85 86 /** 87 * Choose the columns to select from, using an array. 88 * 89 * @param array $columns list of column names or aliases 90 * @return $this 91 */ 92 public function select_array(array $columns) 93 { 94 $this->_select = array_merge($this->_select, $columns); 95 96 return $this; 97 } 98 99 /** 100 * Choose the tables to select "FROM ..." 101 * 102 * @param mixed $table table name or array($table, $alias) or object 103 * @return $this 104 */ 105 public function from($tables) 106 { 107 $tables = func_get_args(); 108 109 $this->_from = array_merge($this->_from, $tables); 110 111 return $this; 112 } 113 114 /** 115 * Adds addition tables to "JOIN ...". 116 * 117 * @param mixed $table column name or array($column, $alias) or object 118 * @param string $type join type (LEFT, RIGHT, INNER, etc) 119 * @return $this 120 */ 121 public function join($table, $type = NULL) 122 { 123 $this->_join[] = $this->_last_join = new Database_Query_Builder_Join($table, $type); 124 125 return $this; 126 } 127 128 /** 129 * Adds "ON ..." conditions for the last created JOIN statement. 130 * 131 * @param mixed $c1 column name or array($column, $alias) or object 132 * @param string $op logic operator 133 * @param mixed $c2 column name or array($column, $alias) or object 134 * @return $this 135 */ 136 public function on($c1, $op, $c2) 137 { 138 $this->_last_join->on($c1, $op, $c2); 139 140 return $this; 141 } 142 143 /** 144 * Adds "USING ..." conditions for the last created JOIN statement. 145 * 146 * @param string $columns column name 147 * @return $this 148 */ 149 public function using($columns) 150 { 151 $columns = func_get_args(); 152 153 call_user_func_array(array($this->_last_join, 'using'), $columns); 154 155 return $this; 156 } 157 158 /** 159 * Creates a "GROUP BY ..." filter. 160 * 161 * @param mixed $columns column name or array($column, $alias) or object 162 * @return $this 163 */ 164 public function group_by($columns) 165 { 166 $columns = func_get_args(); 167 168 $this->_group_by = array_merge($this->_group_by, $columns); 169 170 return $this; 171 } 172 173 /** 174 * Alias of and_having() 175 * 176 * @param mixed $column column name or array($column, $alias) or object 177 * @param string $op logic operator 178 * @param mixed $value column value 179 * @return $this 180 */ 181 public function having($column, $op, $value = NULL) 182 { 183 return $this->and_having($column, $op, $value); 184 } 185 186 /** 187 * Creates a new "AND HAVING" condition for the query. 188 * 189 * @param mixed $column column name or array($column, $alias) or object 190 * @param string $op logic operator 191 * @param mixed $value column value 192 * @return $this 193 */ 194 public function and_having($column, $op, $value = NULL) 195 { 196 $this->_having[] = array('AND' => array($column, $op, $value)); 197 198 return $this; 199 } 200 201 /** 202 * Creates a new "OR HAVING" condition for the query. 203 * 204 * @param mixed $column column name or array($column, $alias) or object 205 * @param string $op logic operator 206 * @param mixed $value column value 207 * @return $this 208 */ 209 public function or_having($column, $op, $value = NULL) 210 { 211 $this->_having[] = array('OR' => array($column, $op, $value)); 212 213 return $this; 214 } 215 216 /** 217 * Alias of and_having_open() 218 * 219 * @return $this 220 */ 221 public function having_open() 222 { 223 return $this->and_having_open(); 224 } 225 226 /** 227 * Opens a new "AND HAVING (...)" grouping. 228 * 229 * @return $this 230 */ 231 public function and_having_open() 232 { 233 $this->_having[] = array('AND' => '('); 234 235 return $this; 236 } 237 238 /** 239 * Opens a new "OR HAVING (...)" grouping. 240 * 241 * @return $this 242 */ 243 public function or_having_open() 244 { 245 $this->_having[] = array('OR' => '('); 246 247 return $this; 248 } 249 250 /** 251 * Closes an open "AND HAVING (...)" grouping. 252 * 253 * @return $this 254 */ 255 public function having_close() 256 { 257 return $this->and_having_close(); 258 } 259 260 /** 261 * Closes an open "AND HAVING (...)" grouping. 262 * 263 * @return $this 264 */ 265 public function and_having_close() 266 { 267 $this->_having[] = array('AND' => ')'); 268 269 return $this; 270 } 271 272 /** 273 * Closes an open "OR HAVING (...)" grouping. 274 * 275 * @return $this 276 */ 277 public function or_having_close() 278 { 279 $this->_having[] = array('OR' => ')'); 280 281 return $this; 282 } 283 284 /** 285 * Adds an other UNION clause. 286 * 287 * @param mixed $select if string, it must be the name of a table. Else 288 * must be an instance of Database_Query_Builder_Select 289 * @param boolean $all decides if it's an UNION or UNION ALL clause 290 * @return $this 291 */ 292 public function union($select, $all = TRUE) 293 { 294 if (is_string($select)) 295 { 296 $select = DB::select()->from($select); 297 } 298 if ( ! $select instanceof Database_Query_Builder_Select) 299 throw new Kohana_Exception('first parameter must be a string or an instance of Database_Query_Builder_Select'); 300 $this->_union []= array('select' => $select, 'all' => $all); 301 return $this; 302 } 303 304 /** 305 * Start returning results after "OFFSET ..." 306 * 307 * @param integer $number starting result number or NULL to reset 308 * @return $this 309 */ 310 public function offset($number) 311 { 312 $this->_offset = ($number === NULL) ? NULL : (int) $number; 313 314 return $this; 315 } 316 317 /** 318 * Compile the SQL query and return it. 319 * 320 * @param mixed $db Database instance or name of instance 321 * @return string 322 */ 323 public function compile($db = NULL) 324 { 325 if ( ! is_object($db)) 326 { 327 // Get the database instance 328 $db = Database::instance($db); 329 } 330 331 // Callback to quote columns 332 $quote_column = array($db, 'quote_column'); 333 334 // Callback to quote tables 335 $quote_table = array($db, 'quote_table'); 336 337 // Start a selection query 338 $query = 'SELECT '; 339 340 if ($this->_distinct === TRUE) 341 { 342 // Select only unique results 343 $query .= 'DISTINCT '; 344 } 345 346 if (empty($this->_select)) 347 { 348 // Select all columns 349 $query .= '*'; 350 } 351 else 352 { 353 // Select all columns 354 $query .= implode(', ', array_unique(array_map($quote_column, $this->_select))); 355 } 356 357 if ( ! empty($this->_from)) 358 { 359 // Set tables to select from 360 $query .= ' FROM '.implode(', ', array_unique(array_map($quote_table, $this->_from))); 361 } 362 363 if ( ! empty($this->_join)) 364 { 365 // Add tables to join 366 $query .= ' '.$this->_compile_join($db, $this->_join); 367 } 368 369 if ( ! empty($this->_where)) 370 { 371 // Add selection conditions 372 $query .= ' WHERE '.$this->_compile_conditions($db, $this->_where); 373 } 374 375 if ( ! empty($this->_group_by)) 376 { 377 // Add grouping 378 $query .= ' '.$this->_compile_group_by($db, $this->_group_by); 379 } 380 381 if ( ! empty($this->_having)) 382 { 383 // Add filtering conditions 384 $query .= ' HAVING '.$this->_compile_conditions($db, $this->_having); 385 } 386 387 if ( ! empty($this->_order_by)) 388 { 389 // Add sorting 390 $query .= ' '.$this->_compile_order_by($db, $this->_order_by); 391 } 392 393 if ($this->_limit !== NULL) 394 { 395 // Add limiting 396 $query .= ' LIMIT '.$this->_limit; 397 } 398 399 if ($this->_offset !== NULL) 400 { 401 // Add offsets 402 $query .= ' OFFSET '.$this->_offset; 403 } 404 405 if ( ! empty($this->_union)) 406 { 407 $query = '('.$query.')'; 408 foreach ($this->_union as $u) { 409 $query .= ' UNION '; 410 if ($u['all'] === TRUE) 411 { 412 $query .= 'ALL '; 413 } 414 $query .= '('.$u['select']->compile($db).')'; 415 } 416 } 417 418 $this->_sql = $query; 419 420 return parent::compile($db); 421 } 422 423 public function reset() 424 { 425 $this->_select = 426 $this->_from = 427 $this->_join = 428 $this->_where = 429 $this->_group_by = 430 $this->_having = 431 $this->_order_by = 432 $this->_union = array(); 433 434 $this->_distinct = FALSE; 435 436 $this->_limit = 437 $this->_offset = 438 $this->_last_join = NULL; 439 440 $this->_parameters = array(); 441 442 $this->_sql = NULL; 443 444 return $this; 445 } 446 447} // End Database_Query_Select 448