+ Error on line 280
$stmt->execute($param);
<?php
/**
* DooSqlMagic class file.
*
* @author Leng Sheng Hong <darkredz@gmail.com>
* @link http://www.doophp.com/
* @copyright Copyright © 2009 Leng Sheng Hong
* @license http://www.doophp.com/license
*/
/**
* DooSqlMagic is a fast ORM tool which handles relational data.
*
* <p>It generates SQL query on the fly and escaping/quoting record values to prevent sql injection.
* You can do CRUD operations in an easier way with DooSqlMagic instead of writing queries manually.</p>
*
* <p>Database models does not need to extend any classes with DooSqlMagic. Database table relationships are defined in a
* centralized way in <i>SITE_PATH/protected/config/db.conf.php</i> instead of defining them in each Model class.</p>
*
* <p>Unlike most active records, DooSqlMagic class does not use any php Magic methods in order to gain higher performance.</p>
*
* <p>It does not implements the active record design pattern, a popular Object-Relational Mapping (ORM) technique.
* However, it can still be used with some flavours of active record features.</p>
*
* <p>DooSqlMagic is more like a blend between Active record and DataMapper design pattern.</p>
*
* <p>Creating an instance of an DooSqlMagic class does not immediately connect to the RDBMS server.
* Use connect() or reconnect() to make the actual connection on demand.</p>
*
* Please check {@link http://www.doophp.com/doc/guide/database the Guide} for more details
* about this class.
*
* @author Leng Sheng Hong <darkredz@gmail.com>
* @version $Id: DooSqlMagic.php 1000 2009-07-7 18:27:22
* @package doo.db
* @since 1.0
*/
class DooSqlMagic {
/**
* Enable/disable SQL tracking, to view SQL which has been queried, use showSQL()
* @var bool
*/
public $sql_tracking=false;
/**
* Determined whether the database connection is made.
* @var bool
*/
public $connected = false;
/**
* Path to the folder where the model class files are located. Ends with a slash. eg. /var/www/modelfiles/
* @var string
*/
public $modelPath;
protected $map;
protected $dbconfig;
protected $dbconfig_list;
protected $pdo;
protected $transactionLevel = 0;
protected $sql_list;
const JOIN_LEFT = 'LEFT';
const JOIN_RIGHT = 'RIGHT';
const JOIN_LEFT_OUTER = 'LEFT OUTER';
const JOIN_RIGHT_OUTER = 'RIGHT OUTER';
const JOIN_INNER = 'INNER';
const JOIN = '';
public function loadModel($className, $createObj=false){
if( class_exists($className, false)===true ){
if($createObj===true){
return new $className;
}
return;
}
if(empty($this->modelPath)===true) {
return Doo::loadModel($className, $createObj);
}
else {
require_once $this->modelPath . $className . '.php';
if($createObj===true){
return new $className;
}
}
}
/**
* Set the database configuration
* @param array $dbconfig Associative array of the configurations
* @param string $default_name Default configuration to be used
*/
public function setDb($dbconfig, $default_name){
$this->dbconfig_list = $dbconfig;
$this->dbconfig = $dbconfig[$default_name];
}
/**
* Get the default database configuration
* @return array array(db_host, db_name, db_user, db_pwd, db_driver, db_connection_cache)
*/
public function getDefaultDbConfig(){
return $this->dbconfig;
}
/**
* Set the database relationship mapping
* @param array $dbmap Database relationship mapping
*/
public function setMap($dbmap){
$this->map = $dbmap;
}
/**
* Append to DB relationship mapping
* @param array $dbmap Database relationship mapping
*/
public function appendMap( $dbmap ){
if(is_array($this->map)===True)
$this->map = array_merge( $this->map, $dbmap );
else
$this->map = $dbmap;
}
/**
* Get DB relationship mapping
* @return array Database relationship mapping
*/
public function getMap(){
return $this->map;
}
/**
* Connects to the database with the default database connection configuration
*/
public function connect(){
if($this->dbconfig==NULL)return;
try{
if ($this->dbconfig[4]=='sqlite')
$this->pdo = new PDO("{$this->dbconfig[4]}:{$this->dbconfig[0]}");
else
$this->pdo = new PDO("{$this->dbconfig[4]}:host={$this->dbconfig[0]};dbname={$this->dbconfig[1]}", $this->dbconfig[2], $this->dbconfig[3],array(PDO::ATTR_PERSISTENT => $this->dbconfig[5]));
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->connected = true;
if(isset($this->dbconfig['charset']) && isset($this->dbconfig['collate'])){
$this->pdo->exec("SET NAMES '". $this->dbconfig['charset']. "' COLLATE '". $this->dbconfig['collate'] ."'");
}
else if(isset($this->dbconfig['charset']) ){
$this->pdo->exec("SET NAMES '". $this->dbconfig['charset']. "'");
}
}catch(PDOException $e){
throw new SqlMagicException('Failed to open the DB connection', SqlMagicException::DBConnectionError);
}
}
/**
* Reconnect to a database which has been defined in the database connection configurations
* @param string $db_config_name Name/key of the database configuration
*/
public function reconnect($db_config_name=Null){
//$host='localhost', $db='', $user='', $password='', $driver='mysql', $persist=true
if(isset($db_config_name)){
$this->dbconfig = $dbconfig = $this->dbconfig_list[$db_config_name];
}else{
$dbconfig = $this->dbconfig;
}
try{
if ($dbconfig[4]=='sqlite')
$this->pdo = new PDO("{$dbconfig[4]}:{$dbconfig[0]}");
else
$this->pdo = new PDO("{$dbconfig[4]}:host={$dbconfig[0]};dbname={$dbconfig[1]}", $dbconfig[2], $dbconfig[3],array(PDO::ATTR_PERSISTENT => $dbconfig[5]));
$this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$this->connected = true;
if(isset($dbconfig['charset']) && isset($dbconfig['collate'])){
$this->pdo->exec("SET NAMES '". $dbconfig['charset']. "' COLLATE '". $dbconfig['collate'] ."'");
}
else if(isset($dbconfig['charset']) ){
$this->pdo->exec("SET NAMES '". $dbconfig['charset']. "'");
}
}catch(PDOException $e){
throw new SqlMagicException('Failed to open the DB connection', SqlMagicException::DBConnectionError);
}
}
/**
* Close a database connection
*/
public function disconnect(){
$this->pdo = null;
$this->connected = false;
}
/**
* Initiates a transaction. Transactions can be nestable.
*/
public function beginTransaction() {
if($this->transactionLevel === 0){
$this->pdo->beginTransaction();
}
else{
$this->pdo->exec("SAVEPOINT LEVEL{$this->transactionLevel}");
}
$this->transactionLevel++;
}
/**
* Commits a transaction. Transactions can be nestable.
*/
public function commit() {
$this->transactionLevel--;
if($this->transactionLevel === 0){
$this->pdo->commit();
}
else{
$this->pdo->exec("RELEASE SAVEPOINT LEVEL{$this->transactionLevel}");
}
}
/**
* Rolls back a transaction. Transactions can be nestable.
*/
public function rollBack() {
$this->transactionLevel--;
if($this->transactionLevel === 0){
$this->pdo->rollBack();
}
else{
$this->pdo->exec("ROLLBACK TO SAVEPOINT LEVEL{$this->transactionLevel}");
}
}
/**
* Execute a query to the connected database
* @param string $query SQL query prepared statement
* @param array $param Values used in the prepared SQL
* @return PDOStatement
*/
public function query($query, $param=null){
if($this->sql_tracking===true){
$querytrack = $query;
//if params used in sql, replace them into the sql string for logging
if($param!=null){
if(isset($param[0])){
$querytrack = explode('?',$querytrack);
$q = $querytrack[0];
foreach($querytrack as $k=>$v){
if($k===0)continue;
if (isset($param[$k-1])) {
$q .= "'".$param[$k-1]."'" . $querytrack[$k];
} else {
throw new Exception('Insufficent paramters provided for query');
}
}
$querytrack = $q;
}else{
//named param used
foreach($param as $k=>$v)
$querytrack = str_replace($k, "'$v'", $querytrack);
}
}
$this->sql_list[] = $querytrack;
//echo '<pre>' . print_r($querytrack, true) . '</pre>';
}
$stmt = $this->pdo->prepare($query);
$stmt->setFetchMode(PDO::FETCH_ASSOC);
if($param==null)
$stmt->execute();
else
return $stmt;
}
/*
* Execute a query and Fetch single row
* @param string $query SQL query prepared statement
* @param array $param Values used in the prepared SQL
* @param int $fetchMode PDO Fetch mode for the statement
* @param mixed $fetchModeOptionalParam1 First Optional Param for PDOStatement::setFetchMode
* @param mixed $fetchModeOptionalParam2 Second Optional Param for PDOStatement::setFetchMode
* @return Returns an array a row from the result set
*/
public function fetchRow($query, $param = null, $fetchMode = null, $fetchModeOptionalParam1=null, $fetchModeOptionalParam2=array()) {
$stmt = $this->query($query, $param);
if($fetchMode!==null) {
switch($fetchMode) {
case PDO::FETCH_COLUMN:
$stmt->setFetchMode($fetchMode, ($fetchModeOptionalParam1 !== null) ? $fetchModeOptionalParam1 : 0);
break;
case PDO::FETCH_CLASS:
$stmt->setFetchMode($fetchMode, $fetchModeOptionalParam1, $fetchModeOptionalParam2);
break;
case PDO::FETCH_INTO:
$stmt->setFetchMode($fetchMode, $fetchModeOptionalParam1);
break;
default:
$stmt->setFetchMode($fetchMode);
}
}
return $stmt->fetch();
}
/*
* Execute a query and Fetch multiple rows
* @param string $query SQL query prepared statement
* @param array $param Values used in the prepared SQL
* @param int $fetchMode PDO Fetch mode for the statement
* @param mixed $fetchModeOptionalParam1 First Optional Param for PDOStatement::setFetchMode
* @param mixed $fetchModeOptionalParam2 Second Optional Param for PDOStatement::setFetchMode
* @return Returns an array containing all of the result set rows
*/
public function fetchAll($query, $param = null, $fetchMode = null, $fetchModeOptionalParam1=null, $fetchModeOptionalParam2=array()) {
$stmt = $this->query($query, $param);
if($fetchMode!==null) {
switch($fetchMode) {
case PDO::FETCH_COLUMN:
$stmt->setFetchMode($fetchMode, ($fetchModeOptionalParam1 !== null) ? $fetchModeOptionalParam1 : 0);
break;
case PDO::FETCH_CLASS:
$stmt->setFetchMode($fetchMode, $fetchModeOptionalParam1, $fetchModeOptionalParam2);
break;
case PDO::FETCH_INTO:
$stmt->setFetchMode($fetchMode, $fetchModeOptionalParam1);
break;
default:
$stmt->setFetchMode($fetchMode);
}
}
return $stmt->fetchAll();
}
/**
* Use showSQL() instead.
* @deprecated deprecated since version 1.3
* @return array
*/
public function show_sql(){
return $this->showSQL();
}
/**
* Retrieve a list of executed SQL queries
* @return array
*/
public function showSQL(){
return $this->sql_list;
}
/**
* Use getQueryCount() instead
* @deprecated deprecated since version 1.3
*/
public function get_query_count(){
return $this->getQueryCount();
}
/**
* Get the number of queries executed
* @return int
*/
public function getQueryCount(){
return sizeof($this->sql_list);
}
/**
* Quotes a string for use in a query.
*
* Places quotes around the input string and escapes and single quotes within the input string, using a quoting style appropriate to the underlying driver.
* @param string $string The string to be quoted.
* @param int $type Provides a data type hint for drivers that have alternate quoting styles. The default value is PDO::PARAM_STR.
* @return string Returns a quoted string that is theoretically safe to pass into an SQL statement. Returns FALSE if the driver does not support quoting in this way.
*/
public function quote($string, $type=null) {
return $this->pdo->quote($string, $type);
}
public function quoteArray($array, $type=null) {
if (!is_array($array)) {
return $this->quote($array, $type);
}
$numItems = count($array);
for($i=0; $i<$numItems; $i++) {
if (is_array($array[$i])) {
$array[$i] = $this->quoteArray($array[$i], $type);
} else {
$array[$i] = $this->quote($array[$i]);
}
}
return $array;
}
/**
* Returns the last inserted record's id
* @return int
*/
public function lastInsertId() {
return $this->pdo->lastInsertId();
}
/**
* Returns the underlying PDO object used in DooSqlMagic
* @return PDO
*/
public function getDbObject(){
return $this->pdo;
}
//---------------------------------- SQL generator functions ------------------------
/**
* Retrieve model by one record.
*
* @param array $opt Options for the query. Available options see @see find()
* @return mixed A model object or associateve array of the queried result
*/
public function getOne($model, $opt=null){
if($opt!==null){
$opt['limit'] = 1;
return $this->find($model, $opt);
}
return $this->find($model, array('limit'=>1));
}
/**
* Find a record. (Prepares and execute the SELECT statements)
* @param mixed $model The model class name or object to be select
* @param array $opt Associative array of options to generate the SELECT statement. Supported: <i>where, limit, select, groupby, having, param, asc, desc, custom, asArray, filters</i>
* @return mixed A model object or associateve array of the queried result
*/
public function find($model, $opt=null){
if(is_object($model)){
$class_name = get_class($model);
//auto search(add conditions to WHERE) if the model propertie(s) are/is set
$obj = get_object_vars($model);
$wheresql ='';
$where_values = array();
foreach($obj as $o=>$v){
if(isset($v) && in_array($o, $model->_fields)){
if( is_object($v) ){
$firstChr = substr($v, 0, 1);
$andOrStr = ($v->useOrStatement) ? 'OR ' : 'AND';
if(ctype_punct($firstChr)){
if(ctype_punct(substr($v, 1, 1))){
$firstChr .= substr($v, 1, 1);
$wvalue = substr($v, 2);
}else{
$wvalue = substr($v, 1);
}
if($v->skipBinding === TRUE){
$wheresql .= " {$andOrStr} {$obj['_table']}.$o $firstChr " . $wvalue;
}else{
$wheresql .= " {$andOrStr} {$obj['_table']}.$o $firstChr ?";
$where_values[] = substr($v, 1);
}
}else{
if($v->skipBinding === TRUE){
$wheresql .= " {$andOrStr} {$obj['_table']}.$o $v";
}else{
if(strpos(strtoupper($v), 'LIKE')===0){
preg_match('/^LIKE[ ]{1,}[\'\"]{1}(.+)[\'\"]{1}[ ]{1,}$/i', $v, $matches);
$wheresql .= " {$andOrStr} {$obj['_table']}.$o LIKE ?";
$where_values[] = $matches[1];
}
else if(strpos(strtoupper($v), 'IS')===0){
$wheresql .= " {$andOrStr} {$obj['_table']}.$o $v";
}
else{
$wheresql .= " {$andOrStr} {$obj['_table']}.$o=?";
$where_values[] = $v;
}
}
}
}else{
$wheresql .= " AND {$obj['_table']}.$o=?";
$where_values[] = $v;
}
}
}
if($wheresql!=''){
if(isset($opt['where'])){
$opt['where'] .= $wheresql;
}else{
$opt['where'] = substr($wheresql, 5);
}
}
}else{
//require file only when String is passed in
$this->loadModel($model);
$class_name = $model;
$model = new $model;
}
#$sqladd = array();
//select
if(isset($opt['select'])){
$sqladd['select']=$opt['select'];
}else{
$sqladd['select'] = "{$model->_table}.*";
}
//limit
if(isset($opt['limit'])){
$sqladd['limit'] = 'LIMIT ' . $opt['limit'];
}else{
$opt['limit']='';
$sqladd['limit']='';
}
//conditions WHERE
if(isset($opt['where'])){
$sqladd['where']= 'WHERE ' . $opt['where'];
}else{
$sqladd['where'] ='';
}
if (isset($opt['filters']) && is_array($opt['filters'])) {
$sqladd['filter'] = '';
foreach ($opt['filters'] as $filter) {
$fmodel = null;
if(is_object($filter['model'])){
$fmodel = $filter['model'];
$fTableName = $fmodel->_table;
$fmodel_class = get_class($fmodel);
}else{
$fmodel = $this->loadModel($filter['model'], true);
$fTableName = $fmodel->_table;
$fmodel_class = $filter['model'];
}
list($fmodel_rtype, $fparams ) = self::relationType($this->map, $class_name, $fmodel_class);
if(isset($filter['joinType'])){
$joinType = $filter['joinType'] . ' JOIN';
}else{
$joinType = 'JOIN';
}
switch($fmodel_rtype) {
case 'has_one':
$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$fmodel->_table}.{$fparams['foreign_key']} = {$model->_table}.{$model->_primarykey} ";
break;
case 'belongs_to':
list($frtype, $ffkey ) = self::relationType($this->map, $fmodel_class, $class_name);
$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$fmodel->_table}.{$fparams['foreign_key']} = {$model->_table}.{$ffkey['foreign_key']} ";
break;
case 'has_many':
list($fmtype, $fmparams) = self::relationType($this->map, $fmodel_class, $class_name);
if ($fmtype == 'has_many') {
$sqladd['filter'] .= "{$joinType} {$fparams['through']} ON {$model->_table}.{$model->_primarykey} = {$fparams['through']}.{$fparams['foreign_key']}\n";
$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$fmodel->_table}.{$fmodel->_primarykey} = {$fparams['through']}.{$fmparams['foreign_key']} ";
} else {
$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$model->_table}.{$fmparams['foreign_key']} = {$fTableName}.{$fparams['foreign_key']} ";
}
break;
default:
throw new Exception('Table Relationship not defined for ' . $class_name . ' to ' . $fmodel_class);
}
if(isset($filter['where'])){
if($sqladd['where']==''){
$sqladd['where'] .= ' WHERE '.$filter['where'];
}else{
$sqladd['where'] .= ' AND '.$filter['where'].' ';
}
//merge the include param with the Where params, at the end
if(isset($filter['param'])){
if(isset($opt['param']) && isset($where_values))
$where_values = array_merge( $where_values, $filter['param']);
else if(isset($opt['param']))
$opt['param'] = array_merge( $opt['param'], $filter['param']);
else if(isset($where_values))
$where_values = array_merge( $where_values, $filter['param']);
else
$where_values = $filter['param'];
}
}
}
} else {
$sqladd['filter'] = '';
}
//GROUP BY
if (isset($opt['groupby'])) {
$sqladd['groupby'] = 'GROUP BY ' . $opt['groupby'];
} else {
$sqladd['groupby'] = '';
}
//HAVINGs
if (isset($opt['having'])) {
$sqladd['having'] = 'HAVING ' . $opt['having'];
} else {
$sqladd['having'] = '';
}
//if asc is defined first then ORDER BY xxx ASC, xxx DESC
//else Order by xxx DESC, xxx ASC
if(isset($opt['asc']) && isset($opt['desc']) && $opt['asc']!='' && $opt['desc']!=''){
$optkeys = array_keys($opt);
$posDesc = array_search('desc', $optkeys);
$posAsc = array_search('asc', $optkeys);
if($posDesc < $posAsc)
$sqladd['order']= 'ORDER BY '. $opt['desc'] .' DESC, '. $opt['asc'] . ' ASC';
else
$sqladd['order']= 'ORDER BY '. $opt['asc'] .' ASC, '. $opt['desc'] . ' DESC';
}
//ASC ORDER
else if(isset($opt['asc'])){
$sqladd['order']= 'ORDER BY ' . $opt['asc'] . ' ASC';
}
//DESC ORDER
else if(isset($opt['desc'])){
$sqladd['order']= 'ORDER BY ' . $opt['desc'] . ' DESC';
}
else{
$sqladd['order'] ='';
}
//Custom ending
if(isset($opt['custom'])){
$sqladd['custom']= $opt['custom'];
}else{
$sqladd['custom'] ='';
}
$sql ="SELECT {$sqladd['select']} FROM {$model->_table} {$sqladd['filter']} {$sqladd['where']} {$sqladd['groupby']} {$sqladd['having']} {$sqladd['order']} {$sqladd['custom']} {$sqladd['limit']}";
//conditions WHERE param
if(isset($opt['param']) && isset($where_values))
$rs = $this->query($sql, array_merge( $opt['param'], $where_values));
else if(isset($opt['param']))
$rs = $this->query($sql, $opt['param']);
else if(isset($where_values))
$rs = $this->query($sql, $where_values);
else
$rs = $this->query($sql);
//if limit is only one/first, then just return the single result, we don't need an array
if($opt['limit']===1){
if(isset($opt['asArray']) && $opt['asArray']===true)
return $rs->fetch();
else
return $rs->fetchObject($class_name);
}else{
//return as Array of objects / assoc arrays
if(isset($opt['asArray']) && $opt['asArray']===true){
return $rs->fetchAll();
}else{
return $rs->fetchAll(PDO::FETCH_CLASS, $class_name);
}
}
}
/**
* Find a record and its associated model. Relational search. (Prepares and execute the SELECT statements)
* @param mixed $model The model class name or object to be select.
* @param string $rmodel The related model class name.
* @param array $opt Associative array of options to generate the SELECT statement. Supported: <i>where, limit, select, param, groupby, having, joinType, match, asc, desc, custom, asArray, include, includeWhere, includeParam, filters</i>
* @return mixed A list of model object(s) or associateve array of the queried result
*/
public function relate($model, $rmodel, $opt=null){
if(is_object($model)){
$class_name = get_class($model);
//auto search(add conditions to WHERE) if the model propertie(s) are/is set
$obj = get_object_vars($model);
$wheresql ='';
$where_values = array();
foreach($obj as $o=>$v){
if(isset($v) && in_array($o, $model->_fields)){
if( is_object($v) ){
$firstChr = substr($v, 0, 1);
$andOrStr = ($v->useOrStatement) ? 'OR ' : 'AND';
if(ctype_punct($firstChr)){
if(ctype_punct(substr($v, 1, 1))){
$firstChr .= substr($v, 1, 1);
$wvalue = substr($v, 2);
}else{
$wvalue = substr($v, 1);
}
if($v->skipBinding === TRUE){
$wheresql .= " {$andOrStr} {$obj['_table']}.$o $firstChr " . $wvalue;
}else{
$wheresql .= " {$andOrStr} {$obj['_table']}.$o $firstChr ?";
$where_values[] = substr($v, 1);
}
}else{
if($v->skipBinding === TRUE){
$wheresql .= " {$andOrStr} {$obj['_table']}.$o $v";
}else{
if(strpos(strtoupper($v), 'LIKE')===0){
preg_match('/^LIKE[ ]{1,}[\'\"]{1}(.+)[\'\"]{1}[ ]{1,}$/i', $v, $matches);
$wheresql .= " {$andOrStr} {$obj['_table']}.$o LIKE ?";
$where_values[] = $matches[1];
}
else if(strpos(strtoupper($v), 'IS')===0){
$wheresql .= " {$andOrStr} {$obj['_table']}.$o $v";
}
else{
$wheresql .= " {$andOrStr} {$obj['_table']}.$o=?";
$where_values[] = $v;
}
}
}
}else{
$wheresql .= " AND {$obj['_table']}.$o=?";
$where_values[] = $v;
}
}
}
if($wheresql!=''){
if(isset($opt['where'])){
$opt['where'] .= $wheresql;
}else{
$opt['where'] = substr($wheresql, 5);
}
}
}else{
//require file only when String is passed in
$this->loadModel($model);
$class_name = $model;
$model = new $model;
}
//select
if(isset($opt['select'])){
$sqladd['select']=$opt['select'];
}else{
$sqladd['select'] ='*';
}
//joinType
if(isset($opt['joinType'])){
$sqladd['joinType']=$opt['joinType'] . ' JOIN';
}else{
$sqladd['joinType'] = self::JOIN_LEFT_OUTER . ' JOIN';
}
//limit, value 'first' does not add LIMIT/TOP to the SQL, but will return a single result object instead of array
if(isset($opt['limit'])){
if($opt['limit']!='first')
$sqladd['limit'] = 'LIMIT ' . $opt['limit'];
else
$sqladd['limit'] = '';
}else{
$opt['limit']='';
$sqladd['limit']='';
}
//conditions WHERE
if(isset($opt['where'])){
$sqladd['where']= 'WHERE ' . $opt['where'];
}else{
$sqladd['where'] ='';
}
//GROUP BY
if (isset($opt['groupby'])) {
$sqladd['groupby'] = 'GROUP BY ' . $opt['groupby'];
} else {
$sqladd['groupby'] = '';
}
//HAVINGs
if (isset($opt['having'])) {
$sqladd['having'] = 'HAVING ' . $opt['having'];
} else {
$sqladd['having'] = '';
}
//ASC ORDER
if(isset($opt['asc'])){
$sqladd['order']= 'ORDER BY ' . $opt['asc'] . ' ASC';
}else{
$sqladd['order'] ='';
}
//DESC ORDER
if(isset($opt['desc'])){
$sqladd['order']= 'ORDER BY ' . $opt['desc'] . ' DESC';
}
//Custom ending
if(isset($opt['custom'])){
$sqladd['custom']= $opt['custom'];
}else{
$sqladd['custom'] ='';
}
//get define relation, what type, which model
#list($rtype,$rparams) = $model->relationType($rmodel);
if (is_object($rmodel)) {
$relatedmodel = $rmodel;
$rmodel = get_class($relatedmodel);
$rtable = $relatedmodel->_table;
list($rtype,$rparams) = self::relationType($this->map, $class_name, $rmodel);
if($rtype==NULL)
throw new SqlMagicException("Model $class_name does not relate to $rmodel", SqlMagicException::RelationNotFound);
// As we have an object we will check to see what query params are set
// This will be used to run a nested select
$obj = get_object_vars($relatedmodel);
$nestedwheresql ='';
$nested_where_values = array();
foreach($obj as $o=>$v){
if(isset($v) && in_array($o, $relatedmodel->_fields)){
if( is_object($v) ){
$firstChr = substr($v, 0, 1);
$andOrStr = ($v->useOrStatement) ? 'OR ' : 'AND';
if(ctype_punct($firstChr)){
if(ctype_punct(substr($v, 1, 1))){
$firstChr .= substr($v, 1, 1);
$wvalue = substr($v, 2);
}else{
$wvalue = substr($v, 1);
}
if($v->skipBinding === TRUE){
$nestedwheresql .= " {$andOrStr} {$obj['_table']}.$o $firstChr " . $wvalue;
}else{
$nestedwheresql .= " {$andOrStr} {$obj['_table']}.$o $firstChr ?";
$nestedwheresql[] = substr($v, 1);
}
}else{
if($v->skipBinding === TRUE){
$nestedwheresql .= " {$andOrStr} {$obj['_table']}.$o $v";
}else{
if(strpos(strtoupper($v), 'LIKE')===0){
preg_match('/^LIKE[ ]{1,}[\'\"]{1}(.+)[\'\"]{1}[ ]{1,}$/i', $v, $matches);
$nestedwheresql .= " {$andOrStr} {$obj['_table']}.$o LIKE ?";
$nested_where_values[] = $matches[1];
}
else if(strpos(strtoupper($v), 'IS')===0){
$nestedwheresql .= " {$andOrStr} {$obj['_table']}.$o $v";
}
else{
$nestedwheresql .= " {$andOrStr} {$obj['_table']}.$o=?";
$nested_where_values[] = $v;
}
}
}
}else{
$nestedwheresql .= " AND {$obj['_table']}.$o=?";
$nested_where_values[] = $v;
}
}
}
if($nestedwheresql!=''){
$nestedwheresql = substr($nestedwheresql, 5);
$sqladd['rNestedQuery'] = " (SELECT * FROM {$relatedmodel->_table} WHERE {$nestedwheresql}) AS ";
} else {
$sqladd['rNestedQuery'] = '';
}
if (isset($where_values) && isset($nested_where_values)) {
$where_values = array_merge($nested_where_values, $where_values);
} elseif (isset($nested_where_values)) {
$where_values = $nested_where_values;
}
} else {
list($rtype,$rparams) = self::relationType($this->map, $class_name, $rmodel);
if($rtype==NULL)
throw new SqlMagicException("Model $class_name does not relate to $rmodel", SqlMagicException::RelationNotFound);
$this->loadModel($rmodel);
$relatedmodel = new $rmodel;
$rtable = $relatedmodel->_table;
#echo "$class_name $rtype $rmodel( $rtable )";
$sqladd['rNestedQuery'] = '';
}
//reverse relation (belongs_to), checking params such as foreign_key
#list($mtype,$mparams) = $relatedmodel->relationType($class_name);
list($mtype,$mparams) = self::relationType($this->map, $rmodel, $class_name);
if($mtype==NULL)
throw new SqlMagicException("Model $rmodel does not relate to $class_name. This is a reverse check.", SqlMagicException::RelationNotFound);
#echo "\n$rmodel $mtype $class_name( {$model->_table} )";
//Matched true, related reference keys cannot be NULL in relationship, add Check if not null to WHERE condition
//No need to match if it's a m to m relationship
if(isset($opt['match']) && !($mtype=='has_many' && $rtype=='has_many'))
if($opt['match']==true)
self::addWhereNotNull($sqladd['where'], "{$relatedmodel->_table}.{$rparams['foreign_key']}", "{$model->_table}.{$mparams['foreign_key']}");
elseif($opt['match']==false)
self::addWhereIsNull($sqladd['where'], "{$relatedmodel->_table}.{$rparams['foreign_key']}", "{$model->_table}.{$mparams['foreign_key']}");
//need to seperate the related model vars from the caller model, and add it as a Caller model's property
//all properties of the caller model class
$model_vars = array_keys(self::toArray($model));
$rmodel_vars = array_keys(self::toArray($relatedmodel));
$retrieved_pk_key = "_{$model->_table}__{$mparams['foreign_key']}";
$model_vars[] = $retrieved_pk_key;
$rretrieved_pk_key = "_{$relatedmodel->_table}__{$rparams['foreign_key']}";
$rmodel_vars[] = $rretrieved_pk_key;
//get a list of repeated vars in the 2 models, _table _relation is NOT a model property
$repeated_vars = array_intersect($rmodel_vars, $model_vars);
$repeated_vars = array_diff($repeated_vars, array('_table','_primarykey','_fields'));
//if there's repeat vars(field names) in the 2 models, SELECT the 2nd model as an Alias, _table__fieldname
//keep a set of records of the Alias names, to be replaced back later when mapping to the model object
#$alias_vars = array();
#$ralias_vars = array();
$defined_class_vars = array_merge($model->_fields, $relatedmodel->_fields);
if(isset($opt['select']))
$oriSel = $opt['select'];
else
$oriSel = '*';
foreach($repeated_vars as $r){
//dun add user defined class properties that are not used in database
if(!in_array($r, $defined_class_vars))continue;
if($r==$relatedmodel->_primarykey){
$alias = "_{$model->_table}__$r";
$ralias = "_{$relatedmodel->_table}__$r";
$sqladd['select'] .= ", {$model->_table}.$r AS $alias, {$relatedmodel->_table}.$r AS $ralias";
$alias_vars[$alias] = $r;
$ralias_vars[$ralias] = $r;
$model_vars[] = $alias;
$rmodel_vars[] = $ralias;
if(isset($opt['select'])){
if($opt['select'][0]==='*'){
$sqladd['select'] .= ", {$relatedmodel->_table}.$r";
$opt['select'] .= ", {$relatedmodel->_table}.$r";
}
else if(preg_match('/,(\s+)?'.$relatedmodel->_table .'\.\*/', ','.$opt['select'])){
$sqladd['select'] .= ", {$relatedmodel->_table}.$r";
$opt['select'] .= ", {$relatedmodel->_table}.$r";
}
}else{
$sqladd['select'] .= ", {$relatedmodel->_table}.$r";
$opt['select'] = "{$relatedmodel->_table}.$r";
}
}
else {
if(isset($opt['select']) && strpos($opt['select'], ',')!==false){
$modelHasSelectField = strpos($opt['select'], "{$model->_table}.$r")!==false;
$rmodelHasSelectField = strpos($opt['select'], "{$relatedmodel->_table}.$r")!==false;
if($opt['select'][0]==='*'){
$modelHasSelectField = $rmodelHasSelectField = true;
}else{
if($modelHasSelectField===false && preg_match('/,(\s+)?'.$model->_table .'\.\*/', ',' . $opt['select'])){
$modelHasSelectField = true;
}
if($rmodelHasSelectField===false && preg_match('/,(\s+)?'.$relatedmodel->_table .'\.\*/', ',' . $opt['select'])){
$rmodelHasSelectField = true;
}
}
}
else{
//select *
$modelHasSelectField = $rmodelHasSelectField = true;
}
if($modelHasSelectField===true && $rmodelHasSelectField===true){
// echo "\n+{$model->_table}.$r\n";
// echo "+{$relatedmodel->_table}.$r\n\n";
$alias = "_{$model->_table}__$r";
$ralias = "_{$relatedmodel->_table}__$r";
$sqladd['select'] .= ", {$model->_table}.$r AS $alias, {$relatedmodel->_table}.$r AS $ralias";
$alias_vars[$alias] = $r;
$ralias_vars[$ralias] = $r;
$model_vars[] = $alias;
$rmodel_vars[] = $ralias;
}
else if($modelHasSelectField===true){
// echo "+{$model->_table}.$r\n\n";
$alias = "_{$model->_table}__$r";
$sqladd['select'] .= ", {$model->_table}.$r AS $alias";
$alias_vars[$alias] = $r;
$model_vars[] = $alias;
$ralias_vars[$r] = false;
}
else if($rmodelHasSelectField===true){
// echo "+{$relatedmodel->_table}.$r\n\n";
$ralias = "_{$relatedmodel->_table}__$r";
$sqladd['select'] .= ", {$relatedmodel->_table}.$r AS $ralias";
$ralias_vars[$ralias] = $r;
$rmodel_vars[] = $ralias;
$alias_vars[$r] = false;
}
}
}
#print_r($model_vars);
#print_r($rmodel_vars);
#print_r($repeated_vars);
//include model, reduce a lot of queries, merge 3 related models together, eg. Post, PostCategory, PostComment
if(isset($opt['include'])){
$tmodel = null;
if(is_object($opt['include'])){
$tmodel = $opt['include'];
$sqladd['include'] = $tmodel->_table;
$tmodel_class = get_class($tmodel);
}else{
$tmodel = $this->loadModel($opt['include'], true);
$sqladd['include'] = $tmodel->_table;
$tmodel_class = $opt['include'];
}
list($tmodel_rtype, $tmodel_fk ) = self::relationType($this->map, $class_name, $tmodel_class);
$tmodel_fk = $tmodel_fk['foreign_key'];
if ($rtype == 'has_many' && $mtype == 'has_many' && $tmodel->_table == $rparams['through']) { // Is the include for the joining table?
$sqladd['include'] = '';
} else {
//echo $tmodel_rtype.'<br/>';
//include Join Type
if(isset($opt['includeType'])){
$sqladd['include'] = $opt['includeType'] . ' JOIN '. $sqladd['include'];
}else{
$sqladd['include'] = ' JOIN ' . $sqladd['include'];
}
if($rtype=='belongs_to'){
list($trtype, $tfkey ) = self::relationType($this->map, $tmodel_class, $class_name);
//echo '<h1>TMODEL '.$tfkey['foreign_key'].'</h1>';
$sqladd['include'] .= " ON {$tmodel->_table}.$tmodel_fk = {$model->_table}.{$tfkey['foreign_key']} ";
}else{
$sqladd['include'] .= " ON {$tmodel->_table}.$tmodel_fk = {$model->_table}.{$model->_primarykey} ";
}
#print_r($sqladd['where']);
if(isset($opt['includeWhere'])){
if($sqladd['where']==''){
$sqladd['where'] .= ' WHERE '.$opt['includeWhere'];
}else{
$sqladd['where'] .= ' AND '.$opt['includeWhere'].' ';
}
//merge the include param with the Where params, at the end
if(isset($opt['includeParam'])){
if(isset($opt['param']) && isset($where_values))
$where_values = array_merge( $where_values, $opt['includeParam']);
else if(isset($opt['param']))
$opt['param'] = array_merge( $opt['param'], $opt['includeParam']);
else if(isset($where_values))
$where_values = array_merge( $where_values, $opt['includeParam']);
else
$where_values = $opt['includeParam'];
}
}
}
//edit the select part since now 3 tables are involved, might have 3 repeating field names in all tables.
//SELECT model.*, related_model.*, includemodel.id AS _t_includemodel_id, includemodel.title AS _t_includemodel_title, ...
$tselect_field = '';
foreach($tmodel->_fields as $tfname){
$tselect_field .= $tmodel->_table.'.'.$tfname . ' AS _t_' . $tmodel->_table . '_' . $tfname . ', ';
}
if(strpos($sqladd['select'], '*,')===0){
$sqladd['select'] = substr($sqladd['select'], 2);
$sqladd['select'] = "{$model->_table}.*, {$relatedmodel->_table}.*, $tselect_field" . $sqladd['select'];
}
}else{
$sqladd['include']='';
}
if (isset($opt['filters']) && is_array($opt['filters'])) {
$sqladd['filter'] = '';
foreach ($opt['filters'] as $filter) {
$fmodel = null;
if(is_object($filter['model'])){
$fmodel = $filter['model'];
$fTableName = $fmodel->_table;
$fmodel_class = get_class($fmodel);
}else{
$fmodel = $this->loadModel($filter['model'], true);
$fTableName = $fmodel->_table;
$fmodel_class = $filter['model'];
}
list($fmodel_rtype, $fparams ) = self::relationType($this->map, $class_name, $fmodel_class);
if(isset($filter['joinType'])){
$joinType = $filter['joinType'] . ' JOIN';
}else{
$joinType = 'JOIN';
}
switch($fmodel_rtype) {
case 'has_one':
$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$fmodel->_table}.{$fparams['foreign_key']} = {$model->_table}.{$model->_primarykey} ";
break;
case 'belongs_to':
list($frtype, $ffkey ) = self::relationType($this->map, $fmodel_class, $class_name);
$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$fmodel->_table}.{$fparams['foreign_key']} = {$model->_table}.{$ffkey['foreign_key']} ";
break;
case 'has_many':
list($fmtype, $fmparams) = self::relationType($this->map, $fmodel_class, $class_name);
if ($fmtype == 'has_many') {
$sqladd['filter'] .= "{$joinType} {$fparams['through']} ON {$model->_table}.{$model->_primarykey} = {$fparams['through']}.{$fparams['foreign_key']}\n";
$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$fmodel->_table}.{$fmodel->_primarykey} = {$fparams['through']}.{$fmparams['foreign_key']} ";
} else {
$sqladd['filter'] .= "{$joinType} {$fTableName} ON {$model->_table}.{$fmparams['foreign_key']} = {$fTableName}.{$fparams['foreign_key']} ";
}
}
#print_r($sqladd['where']);
if(isset($filter['where'])){
if($sqladd['where']==''){
$sqladd['where'] .= ' WHERE '.$filter['where'];
}else{
$sqladd['where'] .= ' AND '.$filter['where'].' ';
}
//merge the include param with the Where params, at the end
if(isset($filter['param'])){
if(isset($opt['param']) && isset($where_values))
$where_values = array_merge( $where_values, $filter['param']);
else if(isset($opt['param']))
$opt['param'] = array_merge( $opt['param'], $filter['param']);
else if(isset($where_values))
$where_values = array_merge( $where_values, $filter['param']);
else
$where_values = $filter['param'];
}
}
//edit the select part since now we do not want the filter table fields. Not needed if using include as its adjusted select already
//SELECT model.*, related_model.*, includemodel.id AS _t_includemodel_id, includemodel.title AS _t_includemodel_title, ...
if(strpos($sqladd['select'], '*')===0){
$sqladd['select'] = substr($sqladd['select'], 1);
$sqladd['select'] = "{$model->_table}.*, {$relatedmodel->_table}.* " . $sqladd['select'];
}
}
} else {
$sqladd['filter'] = '';
}
//generate SQL based on $rtype (has_one, has_many, belongs_to)
switch($rtype){
case 'has_one':
$sql = "SELECT {$sqladd['select']}, {$relatedmodel->_table}.{$rparams['foreign_key']} AS _{$relatedmodel->_table}__{$rparams['foreign_key']}
FROM {$model->_table}
{$sqladd['include']}
{$sqladd['filter']}
{$sqladd['joinType']} {$sqladd['rNestedQuery']} {$relatedmodel->_table}
ON {$model->_table}.{$mparams['foreign_key']} = {$relatedmodel->_table}.{$rparams['foreign_key']}
{$sqladd['where']}
{$sqladd['groupby']}
{$sqladd['having']}
{$sqladd['order']} {$sqladd['custom']} {$sqladd['limit']}";
break;
case 'belongs_to':
//for belongs_to Object reverse relate()
$sql = "SELECT {$sqladd['select']}, {$relatedmodel->_table}.{$rparams['foreign_key']} AS _{$relatedmodel->_table}__{$rparams['foreign_key']}
FROM {$model->_table}
{$sqladd['include']}
{$sqladd['filter']}
{$sqladd['joinType']} {$sqladd['rNestedQuery']} {$relatedmodel->_table}
ON {$model->_table}.{$mparams['foreign_key']} = {$relatedmodel->_table}.{$rparams['foreign_key']}
{$sqladd['where']}
{$sqladd['groupby']}
{$sqladd['having']}
{$sqladd['order']} {$sqladd['custom']} {$sqladd['limit']}";
break;
case 'has_many':
if($mtype=='has_many'){
//automatically matched for M:M relationship
if(!isset($opt['match']) || $opt['match']==true){
if($sqladd['where']===''){
$sqladd['where'] = "WHERE {$relatedmodel->_table}.{$relatedmodel->_primarykey} IS NOT NULL AND {$model->_table}.{$model->_primarykey} IS NOT NULL";
}else{
$sqladd['where'] .= " AND {$relatedmodel->_table}.{$relatedmodel->_primarykey} IS NOT NULL AND {$model->_table}.{$model->_primarykey} IS NOT NULL";
}
}
//remove the extra vars which is only exist in the link table. A_has_B
$model_vars = array_diff($model_vars, array("_{$model->_table}__{$mparams['foreign_key']}"));
$rmodel_vars = array_diff($rmodel_vars, array("_{$relatedmodel->_table}__{$rparams['foreign_key']}"));
if(isset($opt['asc'])){
$opt['asc'] = $opt['asc'] . ' ASC, ';
}else{
$opt['asc'] ='';
}
//DESC ORDER
if(isset($opt['desc'])){
$opt['desc'] = $opt['desc'] . ' DESC, ';
}else{
$opt['desc'] = '';
}
//if asc is defined first then ORDER BY xxx ASC, xxx DESC
//else Order by xxx DESC, xxx ASC
if($opt['asc']!='' && $opt['desc']!=''){
$optkeys = array_keys($opt);
$posDesc = array_search('desc', $optkeys);
$posAsc = array_search('asc', $optkeys);
if($posDesc < $posAsc)
$addonOrder = $opt['desc'].' '.$opt['asc'];
else
$addonOrder = $opt['asc'].' '.$opt['desc'];
}else{
$addonOrder = $opt['desc'].' '.$opt['asc'];
}
if($opt['limit']!=''){
if(!empty ($addonOrder)){
if(substr($addonOrder, strlen($addonOrder)-4)=='SC, '){
//remove ', ' at the end for the order
$orderLimit = 'ORDER BY '.substr($addonOrder, 0, strlen($addonOrder)-2);
//remove related Model field names from the limit for the main Model
}else{
$orderLimit = 'ORDER BY '.$addonOrder;
}
$orderLimit = preg_replace("/[, ]*$relatedmodel->_table\.[a-z0-9_-]{1,64}/i", '', $orderLimit);
$orderLimit = str_replace('DESC ASC', 'DESC', $orderLimit);
$orderLimit = str_replace('ASC DESC', 'ASC', $orderLimit);
if($orderLimit=='ORDER BY ASC' || $orderLimit=='ORDER BY DESC'){
$orderLimit = '';
}
if(substr(trim($orderLimit), strlen(trim($orderLimit))-1)==',')
$orderLimit = substr(trim($orderLimit), 0, strlen(trim($orderLimit))-1). ' ';
}
if(isset($opt['where']) && $opt['where']!=''){
//remove Rmodel field names from the WHERE statement
$whrLimit = preg_replace("/[,|AND|OR ]*$relatedmodel->_table\.[a-z0-9_-]{1,64}[^{$model->_table}\.]*/i", '', $opt['where']);
if($whrLimit!=$opt['where']){
if(strpos(strtoupper($whrLimit), 'AND')===0){
$whrLimit = substr($whrLimit, 3);
}
else{
$tmpAnd = strpos($whrLimit, 'D ');
if($tmpAnd!==False){
$whrLimit = substr($whrLimit, $tmpAnd+2);
}
}
//check for relatedModel WHERE statement
if(preg_match_all("/[,|AND|OR ]*($relatedmodel->_table\.[a-z0-9_-]{1,64}[^{$model->_table}\.]*)/i", $opt['where'], $rlimitMatch)>0){
$rlimitMatch = $rlimitMatch[0];
$rlimitMatch = implode(' ', $rlimitMatch);
if( substr($rlimitMatch, strlen($rlimitMatch)-4)=='AND '){
$rlimitMatch = substr($rlimitMatch, 0, strlen($rlimitMatch)-4);
}
else if($rlimitMatch[strlen($rlimitMatch)-1]=='A'){
$rlimitMatch = substr($rlimitMatch, 0, strlen($rlimitMatch)-1);
}
if(isset($opt['param'])){
$rStmtLimit = $this->query("SELECT {$relatedmodel->_table}.{$relatedmodel->_primarykey} FROM {$relatedmodel->_table} WHERE {$rlimitMatch} LIMIT 1", $opt['param']);
$rStmtId = $rStmtLimit->fetch();
$rStmtId = $rStmtId[$relatedmodel->_primarykey];
}else{
$rStmtLimit = $this->query("SELECT {$relatedmodel->_table}.{$relatedmodel->_primarykey} FROM {$relatedmodel->_table} WHERE {$rlimitMatch} LIMIT 1");
$rStmtId = $rStmtLimit->fetch();
$rStmtId = $rStmtId[$relatedmodel->_primarykey];
}
$mIdStmt = $this->query("SELECT {$mparams['through']}.{$rparams['foreign_key']} FROM {$mparams['through']} WHERE {$rparams['through']}.{$mparams['foreign_key']}=? ORDER BY {$mparams['through']}.{$rparams['foreign_key']} DESC", array($rStmtId));
//need to combine these ids with the model limit ids
$m_in_id = array();
foreach($mIdStmt as $m){
$m_in_id[] = $m[$rparams['foreign_key']];
}
}
}
}
if($opt['limit']==1 || $opt['limit']=='first'){
$opt['limit'] = 'first';
$limitstr = 1;
}else{
$limitstr = $opt['limit'];
}
//conditions WHERE param for the Limit
if(isset($opt['param']) && isset($where_values) && !empty($whrLimit)){
$countQ = 0;
str_replace('?', '', $whrLimit, $countQ);
if($countQ==sizeof($where_values)){
$varsLimit = $where_values;
}
else{
$varsLimit = array_merge( $opt['param'], $where_values);
}
$stmtLimit = $this->query("SELECT {$model->_table}.{$model->_primarykey} FROM {$model->_table} WHERE $whrLimit $orderLimit LIMIT {$limitstr}", $varsLimit);
}else if(isset($opt['param']) && !empty($opt['param']) && !empty($whrLimit)){
$stmtLimit = $this->query("SELECT {$model->_table}.{$model->_primarykey} FROM {$model->_table} WHERE $whrLimit $orderLimit LIMIT {$limitstr}", $opt['param']);
}else if(isset($where_values) && !empty($where_values) && !empty($whrLimit)){
$stmtLimit = $this->query("SELECT {$model->_table}.{$model->_primarykey} FROM {$model->_table} WHERE $whrLimit $orderLimit LIMIT {$limitstr}", $where_values);
}else{
if($orderLimit=='ORDER BY ') $orderLimit = '';
$stmtLimit = $this->query("SELECT {$model->_table}.{$model->_primarykey} FROM {$model->_table} $orderLimit LIMIT {$limitstr}");
}
$limitModelStr = array();
foreach($stmtLimit as $rlimit){
$limitModelStr[] = $rlimit[$model->_primarykey];
}
//combine if exists
if(isset($m_in_id)){
$removeIds = array_diff($limitModelStr, $m_in_id);
foreach($removeIds as $k=>$rid){
unset($limitModelStr[$k]);
}
$limitModelStr = array_unique(array_merge($limitModelStr, $m_in_id));
$limitLength = explode(',', $opt['limit']);
if(isset($limitLength[1])){
array_splice($limitModelStr, $limitLength[1]);
}else{
array_splice($limitModelStr, $limitLength[0]);
}
}
$limitModelStr = implode(',', $limitModelStr);
if ($limitModelStr !== ''){
if($sqladd['where']===''){
$sqladd['where'] = "WHERE {$model->_table}.{$model->_primarykey} IN ($limitModelStr)";
}else{
$sqladd['where'] .= " AND {$model->_table}.{$model->_primarykey} IN ($limitModelStr)";
}
}
}
$sql = "SELECT {$sqladd['select']}, {$mparams['through']}.{$mparams['foreign_key']}, {$mparams['through']}.{$rparams['foreign_key']}
,{$rparams['through']}.{$mparams['foreign_key']} AS _{$relatedmodel->_table}__{$rparams['foreign_key']}
,{$rparams['through']}.{$rparams['foreign_key']} AS _{$model->_table}__{$mparams['foreign_key']}
FROM {$model->_table}
{$sqladd['include']}
{$sqladd['filter']}
{$sqladd['joinType']} {$mparams['through']}
ON {$model->_table}.{$model->_primarykey} = {$mparams['through']}.{$rparams['foreign_key']}
{$sqladd['joinType']} {$relatedmodel->_table}
ON {$relatedmodel->_table}.{$relatedmodel->_primarykey} = {$rparams['through']}.{$mparams['foreign_key']}
{$sqladd['where']}
{$sqladd['groupby']}
{$sqladd['having']}
ORDER BY {$addonOrder} {$rparams['through']}.{$mparams['foreign_key']},{$rparams['through']}.{$rparams['foreign_key']} ASC
{$sqladd['custom']}";
}
else if($mtype=='belongs_to'){
$sql = "SELECT {$sqladd['select']},
{$model->_table}.{$mparams['foreign_key']} AS _{$model->_table}__{$mparams['foreign_key']},
{$relatedmodel->_table}.{$rparams['foreign_key']} AS _{$relatedmodel->_table}__{$rparams['foreign_key']}
FROM {$model->_table}
{$sqladd['include']}
{$sqladd['filter']}
{$sqladd['joinType']} {$sqladd['rNestedQuery']} {$relatedmodel->_table}
ON {$model->_table}.{$mparams['foreign_key']} = {$relatedmodel->_table}.{$rparams['foreign_key']}
{$sqladd['where']}
{$sqladd['groupby']}
{$sqladd['having']}
{$sqladd['order']} {$sqladd['custom']} {$sqladd['limit']}";
}
break;
}
//conditions WHERE param
if(isset($opt['param']) && isset($where_values))
$rs = $this->query($sql, array_merge( $opt['param'], $where_values));
else if(isset($opt['param']))
$rs = $this->query($sql, $opt['param']);
else if(isset($where_values))
$rs = $this->query($sql, $where_values);
else
$rs = $this->query($sql);
//if limit is only one, then just return the single result, we don't need an array
if($opt['limit']===1){
if(isset($opt['asArray']) && $opt['asArray']===true)
return $rs->fetch();
else
return $rs->fetchObject($class_name);
}else{
//return as Array of objects / assoc arrays
if(isset($opt['asArray']) && $opt['asArray']===true){
return $rs->fetchAll();
}else{
#$arr = array();
if(isset($tmodel_class) && isset($record->{$tmodel_class}) ){
$tmodelArray=null;
}
switch($rtype){
case 'has_one':
case 'belongs_to':
foreach($rs as $k=>$v){
if(isset($v[$retrieved_pk_key]))
$fk = $v[$retrieved_pk_key];
//related model foreign key, if is Null means no relation!
$rfk = $v[$rretrieved_pk_key];
$record = new $class_name;
$record->{$rmodel} = ($rfk!=NULL)? new $rmodel : NULL;
if(isset($tmodel_class) && !isset($record->{$tmodel_class}) ){
if($tmodel_rtype=='has_many'){
#echo 'Has Many 3rd';
if(!isset($tmodelArray)){
$tmodelArray = array();
#echo '+++++ Create new 3rd Model Array';
}
$newtmodel = new $tmodel_class;
}else{
#echo 'Has One 3rd';
$newtmodel = new $tmodel_class;
}
}
foreach($v as $k2=>$v2){
//check and add to 3rd included Model
if(isset($newtmodel)){
//found then add to 3rd Model, create the Model object if not yet created
$tpart1 = '_t_'.$tmodel->_table.'_';
if( strpos($k2, $tpart1)===0 ){
$tmodel_var = str_replace($tpart1, '', $k2);
$newtmodel->{$tmodel_var} = $v2;
continue;
}
}
$gotoRelateSect = true;
$inAsSelect = preg_match('/[\s]+(as|AS|aS|As)[\s]+'. $k2 .',/', $oriSel.',');
if($inAsSelect || in_array($k2, $model_vars)){
if( !$inAsSelect && $oriSel[0]!=='*' &&
!preg_match("/,(\s+)?({$model->_table}\.)?$k2(\s+)?,/", ','.$oriSel.',') &&
!preg_match('/,(\s+)?'.$model->_table .'\.\*/', ','. str_replace($model->_table .'.'. str_replace('_'.$model->_table.'__', '', $k2), $model->_table.'.*', $oriSel)) ){
$gotoRelateSect = true;
}else{
$gotoRelateSect = false;
}
if($gotoRelateSect===false){
if($k2===$retrieved_pk_key){
$record->{$mparams['foreign_key']} = $v2;
}else{
//if it's a repeated var which is rename earlier(alias _table__fieldname), replace the original var with its value
if(isset($alias_vars[$k2])){
$record->{$alias_vars[$k2]} = $v2;
}else{
$record->{$k2} = $v2;
}
}
}
}
if($gotoRelateSect===true){
if( isset($ralias_vars[$k2]) ){
$k2 = $ralias_vars[$k2];
}
if($oriSel[0]!=='*'){
if( in_array($k2, $rmodel_vars)===false &&
!preg_match('/,(\s+)?'.$relatedmodel->_table .'\.\*/', ','. str_replace($relatedmodel->_table .'.'. $k2, $relatedmodel->_table .'.*', $oriSel)) ){
continue;
} else if( !preg_match("/,(\s+)?({$relatedmodel->_table}\.)?($k2|\*)(\s+)?,/", ','.$oriSel.',') ){
continue;
}
else if( isset($alias_vars[$k2]) ){
continue;
}
}
if($rfk!=NULL){
if($k2===$rretrieved_pk_key){
$record->{$rmodel}->{$rparams['foreign_key']} = $v2;
#echo "<h3>{$rparams['foreign_key']}</h3>";
}else{
//if it's a repeated var which is rename earlier, replace it to its original var by spilting it _table__field '__'
if(isset($ralias_vars[$k2])){
#echo "<h1>{$ralias_vars[$k2]}</h1>";
$record->{$rmodel}->{$ralias_vars[$k2]} = $v2;
}else{
#echo "<h2>$rmodel $k2</h2>";
$record->{$rmodel}->{$k2} = $v2;
}
}
}
}
}
//add in the 3rd Model to the 3rdModel key if created
if(isset($newtmodel)){
if($tmodel_rtype=='has_many'){
$tmodelArray[]= $newtmodel;
#echo '----Added 3rd Model<br/>';
$record->{$tmodel_class} = $tmodelArray;
}else{
$record->{$tmodel_class} = $newtmodel;
}
}
$arr[] = $record;
}
break;
case 'has_many':
$mnull=-1;
$model_pk_arr = array();
foreach($rs as $k=>$v){
$fk = $v[$retrieved_pk_key];
if($v[$retrieved_pk_key]==Null){
//for many to many usage, if unmatched
$fk = $mnull--;
}
//related model foreign key, if is Null means no relation!
$rfk = $v[$rretrieved_pk_key];
if($rfk!=NULL)
$assoc_model = new $rmodel;
//if not a repeated record, add it on to the result array
if(!in_array($fk, $model_pk_arr)){
$model_pk_arr[]=$fk;
$record = new $class_name;
$record->{$rmodel} = ($rfk!=NULL)? array() : NULL;
if(isset($tmodel_class) && !isset($record->{$tmodel_class}) ){
if($tmodel_rtype=='has_many'){
#echo 'Has Many 3rd';
if(!isset($tmodelArray)){
$tmodelArray = array();
#echo '+++++ Create new 3rd Model Array';
}
$newtmodel = new $tmodel_class;
}else{
#echo 'Has One 3rd';
$newtmodel = new $tmodel_class;
}
}
foreach($v as $k2=>$v2){
//check and add to 3rd included Model
if(isset($newtmodel)){
//found then add to 3rd Model, create the Model object if not yet created
$tpart1 = '_t_'.$tmodel->_table.'_';
if( strpos($k2, $tpart1)===0 ){
$tmodel_var = str_replace($tpart1, '', $k2);
$newtmodel->{$tmodel_var} = $v2;
continue;
}
}
$gotoRelateSect = true;
$inAsSelect = preg_match('/[\s]+(as|AS|aS|As)[\s]+'. $k2 .',/', $oriSel.',');
if($inAsSelect || in_array($k2, $model_vars)){
if( !$inAsSelect && $oriSel[0]!=='*' &&
!preg_match("/,(\s+)?({$model->_table}\.)?$k2(\s+)?,/", ','.$oriSel.',') &&
!preg_match('/,(\s+)?'.$model->_table .'\.\*/', ','. str_replace($model->_table .'.'. str_replace('_'.$model->_table.'__', '', $k2), $model->_table .'.*', $oriSel)) ){
$gotoRelateSect = true;
}else{
$gotoRelateSect = false;
}
if($gotoRelateSect===false){
if($k2===$retrieved_pk_key)
$record->{$mparams['foreign_key']} = $v2;
else{
//if it's a repeated var which is rename earlier(alias _table__fieldname), replace the original var with its value
if(isset($alias_vars[$k2])){
$record->{$alias_vars[$k2]} = $v2;
}else{
$record->{$k2} = $v2;
}
}
}
}
if($gotoRelateSect===true){
if( isset($ralias_vars[$k2]) ){
$k2 = $ralias_vars[$k2];
}
if($oriSel[0]!=='*'){
if( in_array($k2, $rmodel_vars)===false &&
!preg_match('/,(\s+)?'.$relatedmodel->_table .'\.\*/', ','. str_replace($relatedmodel->_table .'.'. $k2, $relatedmodel->_table .'.*', $oriSel)) ){
continue;
}
else if(!preg_match("/,(\s+)?({$relatedmodel->_table}\.)?($k2|\*)(\s+)?,/", ','.$oriSel.',') ){
continue;
}
}
if($rfk!=NULL){
if($k2===$rretrieved_pk_key){
if(in_array($rparams['foreign_key'], $assoc_model->_fields)){
$assoc_model->{$rparams['foreign_key']} = $v2;
}
}else{
//if it's a repeated var which is rename earlier, replace it to its original var by spilting it _table__field '__'
if(isset($ralias_vars[$k2])){
$assoc_model->{$ralias_vars[$k2]} = $v2;
}else if( in_array($k2, $assoc_model->_fields) ){
$assoc_model->{$k2} = $v2;
}
}
}
}
}
//add in the 3rd Model to the 3rdModel key if created
if(isset($newtmodel)){
if($tmodel_rtype=='has_many'){
$tmodelArray[]= $newtmodel;
#echo '----Added 3rd Model<br/>';
$record->{$tmodel_class} = $tmodelArray;
}else{
$record->{$tmodel_class} = $newtmodel;
}
}
//do not add to the associated object Array if, relation not found, means empty Array, no record! if not it will create an Array with an empty Model Object
if($rfk!=NULL)
array_push($record->{$rmodel}, $assoc_model);
else{
$record->{$rmodel} = array();
}
$arr[] = $record;
}
//if already exist, then modify the model record by appending the a related object to the model class properties
else{
$indexToChg = array_search($fk, $model_pk_arr);
$record = $arr[ $indexToChg ];
foreach($v as $k2=>$v2){
//add only vars of the second(related) model
if(in_array($k2, $rmodel_vars)){
if( isset($ralias_vars[$k2]) ){
$k2 = $ralias_vars[$k2];
}
if($oriSel[0]!=='*'){
if( in_array($k2, $rmodel_vars)===false &&
!preg_match('/,(\s+)?'.$relatedmodel->_table .'\.\*/', ','. str_replace($relatedmodel->_table .'.'. $k2, $relatedmodel->_table .'.*', $oriSel)) ){
continue;
}
else if( !preg_match("/,(\s+)?({$relatedmodel->_table}\.)?($k2|\*)(\s+)?,/", ','.$oriSel.',') ){
continue;
}
}
if($rfk!=NULL){
if($k2===$rretrieved_pk_key)
$assoc_model->{$rparams['foreign_key']} = $v2;
else{
//if it's a repeated var which is rename earlier, replace it to its original var by spilting it _table__field '__'
if(isset($ralias_vars[$k2])){
// echo "<h3>$k2 = $v2</h3>";
$assoc_model->{$ralias_vars[$k2]} = $v2;
}else{
// echo "<h3>$k2 = $v2</h3>";
$assoc_model->{$k2} = $v2;
}
}
}
}
}
array_push($record->{$rmodel}, $assoc_model);
$arr[ $indexToChg ] = $record;
}
}
break;
}
//return the first object instead of an array of objects
if(isset($opt['limit']) && $opt['limit']=='first'){
if(isset($newtmodel)){
$last = sizeof($arr)-1;
return (isset($arr[$last]))?$arr[$last] : NULL;
}else{
return (isset($arr[0]))?$arr[0] : NULL;
}
}
return (isset($arr))?$arr : NULL;
}
}
}
/**
* Combine relational search results (combine multiple relates).
*
* Example:
* <code>
* Doo::db()->relateMany('Food',array('Recipe','Article','FoodType'))
* </code>
*
* @param mixed $model The model class name or object to be select.
* @param array $rmodel The related models class names.
* @param array $opt Array of options for each related model to generate the SELECT statement. Supported: <i>where, limit, select, param, joinType, match, asc, desc, custom, asArray, include, includeWhere, includeParam</i>
* @return mixed A list of model objects of the queried result
*/
public function relateMany($model, $rmodel, $opt=null){
//---------------------Model has many other rmodels (has_many, has_one & belongs_to relationship only) ----------------
//add main model primary key to select list to link the related model results
$modelObj = $model;
if (is_string($modelObj)) {
$this->loadModel($modelObj);
$modelObj = new $modelObj();
}
$mdl_pk = $modelObj->_primarykey;
$mdl_tbl = $modelObj->_table;
$rOpt = null;
if( isset($opt[$rmodel[0]]) ){
$rOpt = $opt[$rmodel[0]];
if(isset($rOpt['select'])){
$rOpt['select'] = "$mdl_tbl.$mdl_pk, " . $rOpt['select'];
}
}
$mainR = $this->relate($model, $rmodel[0], $rOpt);
if($mainR===null)
return;
$r=array();
foreach($rmodel as $rm){
if($rm==$rmodel[0])
continue;
$rOpt = (isset($opt[$rm])) ? $opt[$rm] : null;
if(isset($rOpt['select'])){
$rOpt['select'] = "$mdl_tbl.$mdl_pk, " . $rOpt['select'];
}else{
$this->loadModel($rm);
$newrm = new $rm;
$rOpt['select'] = "$mdl_tbl.$mdl_pk, {$newrm->_table}.*";
}
$r[] = $this->relate($model, $rm, $rOpt);
}
$relatedClass = $rmodel;
foreach($mainR as $k1=>$v1){
foreach($r as $k2=>$v2){
$cls = $relatedClass[$k2+1];
foreach((array)$v2 as $k3=>$v3){
if( $v3->{$mdl_pk} == $v1->{$mdl_pk}){
$mainR[$k1]->{$cls} = $v3->{$cls};
break;
}
}
if (!isset($mainR[$k1]->{$cls})) {
if( is_string($model) ) {
$relationType = self::relationType($this->map, $model, $cls);
} else {
$relationType = self::relationType($this->map, get_class($model), $cls);
}
$relationType = $relationType[0];
if( $relationType == 'has_one' || $relationType == 'belongs_to' ) {
$mainR[$k1]->{$cls} = null;
} else {
$mainR[$k1]->{$cls} = array();
}
}
}
}
return $mainR;
}
/**
* Expand related models (Tree Relationships).
*
* Example:
* <code>
* Doo::db()->relateExpand('Recipe', array('Food','Article'))
* </code>
*
* @param mixed $model The model class name or object to be select.
* @param array $rmodel The related models class names.
* @param array $opt Array of options for each related model to generate the SELECT statement. Supported: <i>where, limit, select, param, joinType, match, asc, desc, custom, asArray, include, includeWhere, includeParam</i>
* @return mixed A list of model objects of the queried result
*/
public function relateExpand($model, $rmodel, $opt=null){
$rm = $rmodel[0];
$mainR = $this->relate($model, $rm, (isset($opt[$rm])) ? $opt[$rm] : null );
$id = array();
if($mainR===NULL) return;
foreach($mainR as $mr){
if($m = $mr->{$rm}){
if(is_array($m)){
foreach($m as $m2){
$id[] = $m2->{$m2->_primarykey};
}
}else{
$id[] = $m->{$m->_primarykey};
}
}
}
$this->loadModel($rm);
$newrm = new $rm;
$rm2 = $rmodel[1];
$this->loadModel($rm2);
$newrm2 = new $rm2;
$rOpt = (isset($opt[$rm2])) ? $opt[$rm2] : null;
if(isset($rOpt['select'])){
$rOpt['select'] = $newrm->_table .".". $newrm->_primarykey .", " . $rOpt['select'];
}else{
$rOpt['select'] = $newrm->_table .".". $newrm->_primarykey .", {$newrm2->_table}.*";
}
if(isset($rOpt['where'])){
$rOpt['where'] = $newrm->_table.'.'. $newrm->_primarykey .' IN ('. implode(',', $id) .') AND ( ' . $rOpt['where'] .' )';
}else{
$rOpt['where'] = $newrm->_table.'.'. $newrm->_primarykey .' IN ('. implode(',', $id) .') ';
}
$r = $this->relate($rm, $rm2, $rOpt);
if (!empty($r)) {
foreach($mainR as $k=>$v){
foreach($r as $k2=>$v2){
$rml = $v->{$rm};
if( is_array($rml) ){
foreach($rml as $k3=>$v3){
if($v3->{$v3->_primarykey} == $v2->{$v2->_primarykey}){
$mainR[$k]->{$rm}[$k3]->{$rm2} = $v2->{$rm2};
}
}
}else{
if($v->{$rm}->{$v2->_primarykey} == $v2->{$v2->_primarykey}){
$mainR[$k]->{$rm}->{$rm2} = $v2->{$rm2};
}
}
}
}
}
return $mainR;
}
/**
* Adds a new record. (Prepares and execute the INSERT statements)
* @param object $model The model object to be insert.
* @return int The inserted record's Id
*/
public function insert($model){
$class_name = get_class($model);
//add values to fields where the model propertie(s) are/is set
$obj = get_object_vars($model);
$values = array();
$valuestr = '';
$fieldstr = '';
foreach($obj as $o=>$v){
if(isset($v) && in_array($o, $model->_fields)){
if(is_object($v)){
$valuestr .= "$v,";
}else{
$values[] = $v;
$valuestr .= '?,';
}
if($this->dbconfig[4]=='mysql'){
$fieldstr .= '`'.$o .'`,';
}else{
$fieldstr .= $o .',';
}
}
}
$valuestr = substr($valuestr, 0, strlen($valuestr)-1);
$fieldstr = substr($fieldstr, 0, strlen($fieldstr)-1);
$sql ="INSERT INTO {$obj['_table']} ($fieldstr) VALUES ($valuestr)";
$this->query($sql, $values);
return $this->pdo->lastInsertId();
}
/**
* Use insertAttributes() instead
* @deprecated deprecated since version 1.3
* @param string|object $model The model object to be insert.
* @param array Array of data (keys and values) to be insert
* @return int The inserted record's Id
*/
public function insert_attributes($model, $data){
return $this->insertAttributes($model, $data);
}
/**
* Adds a new record with a list of keys & values (assoc array) (Prepares and execute the INSERT statements)
* @param string|object $model The model object to be insert.
* @param array Array of data (keys and values) to be insert
* @return int The inserted record's Id
*/
public function insertAttributes($model, $data){
if(is_string($model)){
$model = $this->loadModel($model,true);
$table = $model->_table;
}else{
$table = $model->_table;
}
$values = array();
$valuestr = '';
$fieldstr = '';
foreach($data as $o=>$v){
if(isset($v) && in_array($o, $model->_fields)){
if(is_object($v)){
$valuestr .= "$v,";
}else{
$values[] = $v;
$valuestr .= '?,';
}
if($this->dbconfig[4]=='mysql'){
$fieldstr .= '`'.$o .'`,';
}else{
$fieldstr .= $o .',';
}
}
}
$valuestr = substr($valuestr, 0, strlen($valuestr)-1);
$fieldstr = substr($fieldstr, 0, strlen($fieldstr)-1);
$sql ="INSERT INTO {$model->_table} ($fieldstr) VALUES ($valuestr)";
$this->query($sql, $values);
return $this->pdo->lastInsertId();
}
//all rmodel belong to the first model, only 1 level relationship supported,
//all type except BELONGS_TO (no use in INSERT)
//if many-to-many, insert into 3 tables, the model's table, related model's table and the 'through' table defined in db.config
//many to many need to check if the related model already exist, if so,use back that r model record's ID, add the model record and add both id to the 'through' table
//if related model not exist, add to all 3 tables
/**
* Adds a new record with its associated models. Relational insert. (Prepares and execute the INSERT statements)
* @param object $model The model object to be insert.
* @param array $rmodels A list of associated model objects to be insert along with the main model.
* @return int The inserted record's Id
*/
public function relatedInsert($model, $rmodels){
//insert the main model first and save its id
$main_id = $this->insert($model);
$model->{$model->_primarykey} = $main_id; //for later use in many-to-many relationship, need the primary key
//loop and get their relationship, set the foreign key to $main_id
foreach($rmodels as $rmodel){
$class_name = get_class($model);
$rclass_name = get_class($rmodel);
//get how the related model relates to the main model object
list($rtype,$rparams) = self::relationType($this->map, $rclass_name, $class_name);
if($rtype==NULL)
throw new SqlMagicException("Model $class_name does not relate to $rclass_name", SqlMagicException::RelationNotFound);
#print_r(array($rtype,$rparams));
if($rtype=='has_many' && isset($rparams['foreign_key']) && isset($rparams['through'])){
//echo '<h2>Insert MAny to many</h2>';
//select only the primary key (id) and the set properties
$obj = get_object_vars($rmodel);
$fieldstr ='';
foreach($obj as $o=>$v){
if(isset($v) && in_array($o, $model->_fields)){
$fieldstr .= ','.$o;
}
}
$fieldstr = "{$rmodel->_primarykey}$fieldstr";
//get the linked key(Model's foreign key) for the $model from the relationship defined. It's not always primary key of the Model
$reversed_relation = self::relationType($this->map, $class_name, $rclass_name);
$model_linked_key = $reversed_relation[1]['foreign_key'];
//$mId = $model->{$rparams['foreign_key']};
$mId = $model->{$model->_primarykey};
//check if the related model already exist it true than insert to the 'through' table with the 2 ids.
$chk_rmodel = $this->find($rmodel, array('select'=>$fieldstr, 'limit'=>1));
if($chk_rmodel!=NULL){
//echo '<h1>'.$chk_rmodel->{$rparams['foreign_key']}.'</h1>';
$rId = $chk_rmodel->{$chk_rmodel->_primarykey};
}else{
//echo '<h2>Not found this related model in many-to-many. Insert it!</h2>';
$rId = $this->insert($rmodel);
}
//insert into the 'through' Table, faster with parameterized prepared statements
$this->query( "INSERT INTO {$rparams['through']} ({$model_linked_key},{$rparams['foreign_key']}) VALUES (?,?)", array($mId,$rId));
}
else if(isset($rparams['foreign_key'])){
list($rtype,$rparams) = self::relationType($this->map, $class_name, $rclass_name);
$rmodel->{$rparams['foreign_key']} = $main_id;
$this->insert($rmodel);
}
}
return $main_id;
}
/**
* Update an existing record. (Prepares and execute the UPDATE statements)
* If you want to set null values during the update use the setnulls option
* @param mixed $model The model object to be updated.
* @param array $opt Associative array of options to generate the UPDATE statement. Supported: <i>where, limit, field, param, setnulls</i
* @return int Number of rows affected
*/
public function update($model, $opt=NULL){
//add values to fields where the model propertie(s) are/is set
$obj = get_object_vars($model);
$values = array();
$field_and_value = '';
$opt['setnulls'] = isset($opt['setnulls']) ? $opt['setnulls'] : false;
if(isset($opt['field'])){
$opt['field'] = explode(',', str_replace(' ', '', $opt['field']));
foreach($obj as $o=>$v) {
if(in_array($o, $opt['field'])) {
if (($opt['setnulls'] === true || isset($v)) && in_array($o, $model->_fields)) {
if (is_object($v)){
$field_and_value .= "$o=$v,";
} elseif ($v === null) {
$field_and_value .= "$o=null,";
} else {
$values[] = $v;
$field_and_value .= $o .'=?,';
}
}
}
}
}else{
foreach($obj as $o=>$v){
if(($opt['setnulls'] === true || isset($v)) && in_array($o, $model->_fields)){
if (is_object($v)) {
$field_and_value .= "$o=$v,";
} elseif ($v === null) {
$field_and_value .= "$o=null,";
} else {
$values[] = $v;
$field_and_value .= $o .'=?,';
}
}
}
}
$field_and_value = substr($field_and_value, 0, strlen($field_and_value)-1);
if(isset($model->{$obj['_primarykey']})){
$where = $obj['_primarykey'] .'=?';
$values[] = $model->{$obj['_primarykey']};
$sql ="UPDATE {$obj['_table']} SET {$field_and_value} WHERE {$where}";
}else{
$where = $opt['where'];
if(isset($opt['param']))
$values = array_merge($values, $opt['param']);
if(isset($opt['limit'])){
$sql ="UPDATE {$obj['_table']} SET {$field_and_value} WHERE {$where} LIMIT {$opt['limit']}";
}else{
$sql ="UPDATE {$obj['_table']} SET {$field_and_value} WHERE {$where}";
}
}
return $this->query($sql, $values)->rowCount();
}
/**
* Update an existing record with a list of keys & values (assoc array). (Prepares and execute the UPDATE statements)
* @param mixed $model The model object to be updated.
* @param array $opt Associative array of options to generate the UPDATE statement. Supported: <i>where, limit, field, param</i>
* @return int Number of rows affected
*/
public function update_attributes($model, $data, $opt=NULL){
if(is_string($model)){
$model = $this->loadModel($model,true);
$table = $model->_table;
}else{
$table = $model->_table;
}
$values = array();
$field_and_value = '';
if(isset($opt['field'])){
$opt['field'] = explode(',', str_replace(' ', '', $opt['field']));
foreach($data as $o=>$v){
if(in_array($o, $opt['field'])){
if(isset($v) && in_array($o, $model->_fields)){
if(is_object($v)){
$field_and_value .= "$o=$v,";
}else{
$values[] = $v;
$field_and_value .= $o .'=?,';
}
}
}
}
}else{
foreach($data as $o=>$v){
if(isset($v) && in_array($o, $model->_fields)){
if(is_object($v)){
$field_and_value .= "$o=$v,";
}else{
$values[] = $v;
$field_and_value .= $o .'=?,';
}
}
}
}
$field_and_value = substr($field_and_value, 0, strlen($field_and_value)-1);
if(isset($data[$model->_primarykey])){
$where = $model->_primarykey .'=?';
$values[] = $data[$model->_primarykey];
$sql ="UPDATE {$model->_table} SET {$field_and_value} WHERE {$where}";
}else{
$where = $opt['where'];
if(isset($opt['param']))
$values = array_merge($values, $opt['param']);
if(isset($opt['limit'])){
$sql ="UPDATE {$model->_table} SET {$field_and_value} WHERE {$where} LIMIT {$opt['limit']}";
}else{
$sql ="UPDATE {$model->_table} SET {$field_and_value} WHERE {$where}";
}
}
return $this->query($sql, $values)->rowCount();
}
/**
* Update an existing record with its associated models. Relational update. (Prepares and execute the UPDATE statements)
* @param mixed $model The model object to be updated.
* @param array $rmodels A list of associated model objects to be updated or insert along with the main model.
* @param array $opt Assoc array of options to update the main model. Supported: <i>where, limit, field, param</i>
*/
public function relatedUpdate($model, $rmodels, $opt=NULL){
$this->update($model, $opt);
foreach($rmodels as $rmodel){
#echo $rmodel->{$rmodel->_primarykey} . '<br>';
//if related model id is set, means update the existing record!
if(isset($rmodel->{$rmodel->_primarykey})){
$this->update($rmodel);
}
//insert the related model with the relation's foreign key
else{
$class_name = get_class($model);
$rclass_name = get_class($rmodel);
//get how the related model relates to the main model object
list($rtype,$rparams) = self::relationType($this->map, $rclass_name, $class_name);
if($rtype==NULL)
throw new SqlMagicException("Model $class_name does not relate to $rclass_name", SqlMagicException::RelationNotFound);
#print_r(array($rtype,$rparams));
if($rtype=='has_many' && isset($rparams['foreign_key']) && isset($rparams['through'])){
//echo '<h2>Insert MAny to many</h2>';
//select only the primary key (id) and the set properties
$obj = get_object_vars($rmodel);
$fieldstr ='';
foreach($obj as $o=>$v){
if(isset($v) && in_array($o, $model->_fields)){
$fieldstr .= ','.$o;
}
}
$fieldstr = "{$rmodel->_primarykey}$fieldstr";
//get the linked key(Model's foreign key) for the $model from the relationship defined. It's not always primary key of the Model
$reversed_relation = self::relationType($this->map, $class_name, $rclass_name);
$model_linked_key = $reversed_relation[1]['foreign_key'];
//$mId = $model->{$rparams['foreign_key']};
$mId = $model->{$model->_primarykey};
//check if the related model already exist it true than insert to the 'through' table with the 2 ids.
$chk_rmodel = $this->find($rmodel, array('select'=>$fieldstr, 'limit'=>1));
if($chk_rmodel!=NULL){
//echo '<h1>'.$chk_rmodel->{$rparams['foreign_key']}.'</h1>';
$rId = $chk_rmodel->{$chk_rmodel->_primarykey};
}else{
//echo '<h2>Not found this related model in many-to-many. Insert it!</h2>';
$rId = $this->insert($rmodel);
}
//insert into the 'through' Table, if exists dun create duplicates
if($this->query("SELECT {$rparams['foreign_key']},{$model_linked_key} FROM {$rparams['through']} WHERE {$rparams['foreign_key']}=? AND {$model_linked_key}=?", array($rId,$mId))->fetch()==NULL){
$this->query( "INSERT INTO {$rparams['through']} ({$model_linked_key},{$rparams['foreign_key']}) VALUES (?,?)", array($mId,$rId));
}
}
else if(isset($rparams['foreign_key'])){
//echo '<h2>Insert 1to1 or 1tomany</h2>';
list($rtype,$rparams) = self::relationType($this->map, $class_name, $rclass_name);
$rmodel->{$rparams['foreign_key']} = $model->{$model->_primarykey};
$this->insert($rmodel);
}
}
}
}
/**
* Delete all records from table (Prepares and executes the DELETE statement)
* @param mixed $model The model object from which to delete all records
*/
public function deleteAll($model) {
if (!is_object($model)) {
$this->loadModel($model);
$model = new $model;
}
$sql = "DELETE FROM {$model->_table}";
$rs = $this->query($sql);
}
/**
* Delete an existing record. (Prepares and execute the DELETE statements)
* @param mixed $model The model object to be deleted.
* @param array $opt Associative array of options to generate the UPDATE statement. Supported: <i>where, limit, param</i>
*/
public function delete($model, $opt=NULL){
if(is_object($model)){
//add values to fields where the model propertie(s) are/is set
$obj = get_object_vars($model);
//WHERE statement needs to change to parameterized stament =? instead of =value
$wheresql ='';
$where_values = array();
foreach($obj as $o=>$v){
if(isset($v) && in_array($o, $model->_fields)){
$wheresql .= " AND {$obj['_table']}.$o=?";
$where_values[] = $v;
}
}
if($wheresql!=''){
if(isset($opt['where'])){
$opt['where'] .= $wheresql;
}else{
$opt['where'] = substr($wheresql, 5);
}
}
}else{
//require file only when String is passed in
$this->loadModel($model);
$model = new $model;
}
if(isset($opt['limit']))
$sql ="DELETE FROM {$model->_table} WHERE {$opt['where']} LIMIT {$opt['limit']}";
else
$sql ="DELETE FROM {$model->_table} WHERE {$opt['where']}";
//conditions WHERE param
if(isset($opt['param']) && isset($where_values))
$rs = $this->query($sql, array_merge( $opt['param'], $where_values));
else if(isset($opt['param']))
$rs = $this->query($sql, $opt['param']);
else if(isset($where_values))
$rs = $this->query($sql, $where_values);
else
$rs = $this->query($sql);
}
/**
* Retrieve the relationship between 2 model class
* @param array $map The relationship mapping
* @param string $model_name Main model name
* @param string $relate_model_name Related model name
* @return array Relationship with details such as relationship type, foreign key, linked table
*/
public static function relationType($map, $model_name, $relate_model_name){
if (!isset($map[$model_name])) {
throw new SqlMagicException("No relationship mapping found between '{$model_name}' and '{$relate_model_name}'");
}
$r1 = $map[$model_name];
#print_r($r1);
$rtype = NULL;
foreach($r1 as $k=>$v){
#echo "$k<ul>";
foreach($v as $m=>$n){
#echo "<li>$m</li>";
if($m == $relate_model_name){
$rtype = $k;
break 2;
}
}
#echo "</ul>";
}
//return relation type, relation params (foreign_key, through)
return array($rtype, $n);
}
protected static function addWhereNotNull(&$where, $relatedKey, $modelKey){
if(empty($where)){
$where = "WHERE ($relatedKey IS NOT NULL AND $modelKey IS NOT NULL)";
}else{
$where .= " AND ($relatedKey IS NOT NULL AND $modelKey IS NOT NULL)";
}
}
protected static function addWhereIsNull(&$where, $relatedKey, $modelKey){
if(empty($where)){
$where = "WHERE ($relatedKey IS NULL OR $modelKey IS NULL)";
}else{
$where .= " AND ($relatedKey IS NULL OR $modelKey IS NULL)";
}
}
public static function toObject($array, $class_name) {
$object = new $class_name;
if (is_array($array) && count($array) > 0) {
foreach ($array as $name=>$value) {
$name = strtolower(trim($name));
if (!empty($name)) {
$object->$name = $value;
}
}
}
return $object;
}
public static function toArray($object) {
if (is_object($object)) {
$array = get_object_vars($object);
}
return $array;
}
}
class SqlMagicException extends Exception {
const UnexpectedClass = 0;
const RelationNotFound = 1;
const DBConfigNotFound = 2;
const DBConnectionError = 3;
}
* Stack Trace...
PDOException Object
(
[message:protected] => SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Politicas.pdf' in 'where clause'
[string:Exception:private] =>
[code:protected] => 42S22
[file:protected] => /home/grupoancla.com.co/public_html/dooframework/db/DooSqlMagic.php
[line:protected] => 280
[trace:Exception:private] => Array
(
[0] => Array
(
[file] => /home/grupoancla.com.co/public_html/dooframework/db/DooSqlMagic.php
[line] => 280
[function] => execute
[class] => PDOStatement
[type] => ->
[args] => Array
(
[0] => Array
(
[limit] => 1
)
)
)
[1] => Array
(
[file] => /home/grupoancla.com.co/public_html/protected/controller/MainController.php
[line] => 584
[function] => query
[class] => DooSqlMagic
[type] => ->
[args] => Array
(
[0] => select count(*) as total FROM productos WHERE publicado='S' AND id_categoria=561 AND id_fabricante=Politicas.pdf
[1] => Array
(
[limit] => 1
)
)
)
[2] => Array
(
[file] => /home/grupoancla.com.co/public_html/dooframework/app/DooWebApp.php
[line] => 116
[function] => getCategorias
[class] => MainController
[type] => ->
[args] => Array
(
)
)
[3] => Array
(
[file] => /home/grupoancla.com.co/public_html/dooframework/app/DooWebApp.php
[line] => 34
[function] => routeTo
[class] => DooWebApp
[type] => ->
[args] => Array
(
)
)
[4] => Array
(
[file] => /home/grupoancla.com.co/public_html/index.php
[line] => 27
[function] => run
[class] => DooWebApp
[type] => ->
[args] => Array
(
)
)
)
[previous:Exception:private] =>
[errorInfo] => Array
(
[0] => 42S22
[1] => 1054
[2] => Unknown column 'Politicas.pdf' in 'where clause'
)
)
object(DooConfig)#1 (33) {
["AUTOLOAD"] => NULL
["SITE_PATH"] => string(36) "/home/grupoancla.com.co/public_html/"
["PROTECTED_FOLDER"] => string(10) "protected/"
["BASE_PATH"] => string(49) "/home/grupoancla.com.co/public_html/dooframework/"
["LOG_PATH"] => NULL
["APP_URL"] => string(26) "https://grupoancla.com.co/"
["SUBFOLDER"] => string(1) "/"
["APP_MODE"] => string(4) "prod"
["AUTOROUTE"] => bool(true)
["DEBUG_ENABLED"] => bool(true)
["ERROR_404_DOCUMENT"] => NULL
["ERROR_404_ROUTE"] => string(6) "/error"
["CACHE_PATH"] => NULL
["AUTO_VIEW_RENDER_PATH"] => string(47) "/categorias/marcas/:id_cat/:p/:number/:id_marca"
["MEMCACHE"] => NULL
["TEMPLATE_ENGINE"] => string(7) "DooView"
["TEMPLATE_SHOW_COMMENT"] => NULL
["TEMPLATE_ALLOW_PHP"] => NULL
["TEMPLATE_COMPILE_ALWAYS"] => NULL
["TEMPLATE_GLOBAL_TAGS"] => NULL
["MODULES"] => NULL
["APP_NAMESPACE_ID"] => NULL
["RELATIVE_IMG_DIR"] => string(13) "/uploads/img/"
["IMG_DIR"] => string(48) "/home/grupoancla.com.co/public_html/uploads/img/"
["IMG_DIRPR"] => string(49) "/home/grupoancla.com.co/public_html//img_product/"
["FILE_DIR"] => string(43) "/home/grupoancla.com.co/public_html//files/"
["PRODU_DIR"] => string(47) "/home/grupoancla.com.co/public_html//productos/"
["IMG_CATEGORIA"] => string(52) "/home/grupoancla.com.co/public_html//img_categorias/"
["DIR_INTERFACE"] => string(13) "179.50.53.114"
["TEMPLATE"] => string(8) "deepBlue"
["TEMPLATE2"] => string(6) "custom"
["TITLE"] => string(61) "DISTRIBUIDORA ANCLA S.A.S LUBRICACION-FERRETERIA-CONSTRUCCION"
["HEADER"] => string(6) "header"
}
$_SESSION Variables
array(0) {
}
$_COOKIE Variables
array(0) {
}