Source for file TSqlSelect.php
Documentation is available at TSqlSelect.php
namespace
Adianti \
Database ;
* Provides an Interface to create SELECT statements
* @author Pablo Dall'Oglio
* @copyright Copyright (c) 2006 Adianti Solutions Ltd. (http://www.adianti.com.br)
* @license http://www.adianti.com.br/framework-license
private $columns ; // array with the column names to be returned
* Add a column name to be returned
* @param $column A string containing a column name
// add the column name to the array
$this -> columns [ ] =
$column ;
* Returns the SELECT statement as an string according to the database driver
* @param $prepared Return a prepared Statement
$driver =
$conn -> getAttribute ( PDO :: ATTR_DRIVER_NAME ) ;
if ( isset
( $dbInfo [ 'case' ] ) AND $dbInfo [ 'case' ] ==
'insensitive' )
$this -> criteria -> setCaseInsensitive ( TRUE ) ;
if ( in_array ( $driver , array ( 'mssql' , 'dblib' , 'sqlsrv' )))
else if ( in_array ( $driver , array ( 'oci' , 'oci8' )))
else if ( in_array ( $driver , array ( 'firebird' )))
* Returns the SELECT statement as an string for standard open source drivers
* @param $prepared Return a prepared Statement
// creates the SELECT instruction
// concatenate the column names
// concatenate the entity name
// concatenate the criteria (WHERE)
$expression =
$this -> criteria -> dump ( $prepared ) ;
$this -> sql .=
' WHERE ' .
$expression ;
// get the criteria properties
$order =
$this -> criteria -> getProperty ( 'order' ) ;
$group =
$this -> criteria -> getProperty ( 'group' ) ;
$limit = (int)
$this -> criteria -> getProperty ( 'limit' ) ;
$offset = (int)
$this -> criteria -> getProperty ( 'offset' ) ;
$direction =
in_array ( $this -> criteria -> getProperty ( 'direction' ) , array ( 'asc' , 'desc' )) ?
$this -> criteria -> getProperty ( 'direction' ) :
'' ;
$this -> sql .=
' GROUP BY ' .
$group ;
$this -> sql .=
' ORDER BY ' .
$order .
' ' .
$direction ;
$this -> sql .=
' LIMIT ' .
$limit ;
$this -> sql .=
' OFFSET ' .
$offset ;
// return the SQL statement
* Returns the SELECT statement as an string for standard open source drivers
* @param $prepared Return a prepared Statement
// creates the SELECT instruction
$limit = (int)
$this -> criteria -> getProperty ( 'limit' ) ;
$offset = (int)
$this -> criteria -> getProperty ( 'offset' ) ;
$this -> sql .=
' FIRST ' .
$limit ;
$this -> sql .=
' SKIP ' .
$offset ;
// concatenate the column names
// concatenate the entity name
// concatenate the criteria (WHERE)
$expression =
$this -> criteria -> dump ( $prepared ) ;
$this -> sql .=
' WHERE ' .
$expression ;
// get the criteria properties
$group =
$this -> criteria -> getProperty ( 'group' ) ;
$order =
$this -> criteria -> getProperty ( 'order' ) ;
$direction =
in_array ( $this -> criteria -> getProperty ( 'direction' ) , array ( 'asc' , 'desc' )) ?
$this -> criteria -> getProperty ( 'direction' ) :
'' ;
$this -> sql .=
' GROUP BY ' .
$group ;
$this -> sql .=
' ORDER BY ' .
$order .
' ' .
$direction ;
// return the SQL statement
* Returns the SELECT statement as an string for mssql/dblib drivers
* @param $prepared Return a prepared Statement
// obtém a cláusula WHERE do objeto criteria.
$expression =
$this -> criteria -> dump ( $prepared ) ;
// obtém as propriedades do critério
$group =
$this -> criteria -> getProperty ( 'group' ) ;
$order =
$this -> criteria -> getProperty ( 'order' ) ;
$limit = (int)
$this -> criteria -> getProperty ( 'limit' ) ;
$offset = (int)
$this -> criteria -> getProperty ( 'offset' ) ;
$direction =
in_array ( $this -> criteria -> getProperty ( 'direction' ) , array ( 'asc' , 'desc' )) ?
$this -> criteria -> getProperty ( 'direction' ) :
'' ;
$columns =
implode ( ',' , $this -> columns ) ;
if (( isset
( $limit ) OR isset
( $offset )) AND ( $limit >
0 OR $offset >
0 ))
$order =
'(SELECT NULL)' ;
$this -> sql =
" SELECT {$columns } FROM ( SELECT ROW_NUMBER() OVER (order by {$order } {$direction } ) AS __ROWNUMBER__, {$columns } FROM {$this -> entity } " ;
$this -> sql .=
" WHERE {
$expression } " ;
$this -> sql .=
" ) AS TAB2" ;
if (( isset
( $limit ) OR isset
( $offset )) AND ( $limit >
0 OR $offset >
0 ))
$total = $offset + $limit ;
$this -> sql .=
" __ROWNUMBER__ <= {
$total } " ;
$this -> sql .=
" __ROWNUMBER__ > {
$offset } " ;
$this -> sql .=
' WHERE ' .
$expression ;
if ( isset( $group ) AND ! empty ( $group ))
$this -> sql .=
' GROUP BY ' .
$group ;
if ( isset( $order ) AND ! empty ( $order ))
$this -> sql .=
' ORDER BY ' .
$order .
' ' .
$direction ;
* Returns the SELECT statement as an string for oci8 drivers
* @param $prepared Return a prepared Statement
public function getOracleInstruction ( $prepared )
// obtém a cláusula WHERE do objeto criteria.
$expression = $this -> criteria -> dump ( $prepared ) ;
// obtém as propriedades do critério
$group =
$this -> criteria -> getProperty ( 'group' ) ;
$order =
$this -> criteria -> getProperty ( 'order' ) ;
$limit = (int)
$this -> criteria -> getProperty ( 'limit' ) ;
$offset = (int)
$this -> criteria -> getProperty ( 'offset' ) ;
$direction =
in_array ( $this -> criteria -> getProperty ( 'direction' ) , array ( 'asc' , 'desc' )) ?
$this -> criteria -> getProperty ( 'direction' ) :
'' ;
$columns = implode ( ',' , $this -> columns ) ;
$basicsql .=
' FROM ' .
$this -> entity ;
$basicsql .= ' WHERE ' . $expression ;
if ( isset( $group ) AND ! empty ( $group ))
$basicsql .= ' GROUP BY ' . $group ;
if ( isset( $order ) AND ! empty ( $order ))
$basicsql .= ' ORDER BY ' . $order . ' ' . $direction ;
if (( isset( $limit ) OR isset( $offset )) AND ( $limit >0 OR $offset >0 ))
$this -> sql =
" SELECT {
$columns } " ;
$this -> sql .=
" SELECT rownum \"__ROWNUMBER__\", A.{
$columns } FROM ({
$basicsql } ) A
" ;
$total = $offset + $limit ;
$this -> sql .=
" WHERE rownum <= {
$total } " ;
$this -> sql .=
" WHERE \"__ROWNUMBER__\" > {
$offset } " ;