/**
* joinAdd - adds another dataobject to this, building a joined query.
*
* example (requires links.ini to be set up correctly)
* // get all the images for product 24
* $i = new DataObject_Image();
* $pi = new DataObjects_Product_image();
* $pi->product_id = 24; // set the product id to 24
* $i->joinAdd($pi); // add the product_image connectoin
* $i->find();
* while ($i->fetch()) {
* // do stuff
* }
* // an example with 2 joins
* // get all the images linked with products or productgroups
* $i = new DataObject_Image();
* $pi = new DataObject_Product_image();
* $pgi = new DataObject_Productgroup_image();
* $i->joinAdd($pi);
* $i->joinAdd($pgi);
* $i->find();
* while ($i->fetch()) {
* // do stuff
* }
*
*
* @param optional $obj object |array the joining object (no value resets the join)
* If you use an array here it should be in the format:
* array('local_column','remotetable:remote_column');
* if remotetable does not have a definition, you should
* use @ to hide the include error message..
*
*
* @param optional $joinType string 'LEFT'|'INNER'|'RIGHT'|'' Inner is default, '' indicates
* just select ... from a,b,c with no join and
* links are added as where items.
*
* @param optional $joinAs string if you want to select the table as anther name
* useful when you want to select multiple columsn
* from a secondary table.
* @param optional $joinCol string The column on This objects table to match (needed
* if this table links to the child object in
* multiple places eg.
* user->friend (is a link to another user)
* user->mother (is a link to another user..)
*
* @param optional $joinFull bool includes all possible joins, danger!!!
*
* @param optional $joinMeAs string
*
* @return none
* @access public
* @author Stijn de Reede <*****@*****.**>
*/
function joinAdd($obj = false, $joinType = 'INNER', $joinAs = false, $joinCol = false, $joinFull = false, $joinMeAs = false)
{
if ($obj === false) {
$this->_join = '';
return;
}
// support for array as first argument
// this assumes that you dont have a links.ini for the specified table.
// and it doesnt exist as am extended dataobject!! - experimental.
if (is_string($obj)) {
$this->_join .= $obj;
}
if (is_string($joinFull)) {
$on = $joinFull;
$joinFull = false;
} else {
$on = '';
}
$ofield = false;
// object field
$tfield = false;
// this field
$toTable = false;
if (is_array($obj)) {
$tfield = $obj[0];
list($toTable, $ofield) = explode(':', $obj[1]);
$obj = DB_DataObject2::factory($toTable, $this->database());
if (!$obj || is_a($obj, 'PEAR_Error')) {
$obj = new DB_DataObject();
$obj->__table = $toTable;
}
$obj->_connect();
// set the table items to nothing.. - eg. do not try and match
// things in the child table...???
$items = array();
}
if (!is_object($obj) || !is_a($obj, 'DB_DataObject2')) {
throw new Exception("joinAdd: called without an object", DB_DATAOBJECT_ERROR_NODATA);
}
/* make sure $this->_database is set. */
$this->_connect();
$DB =& DB_DataObject2::$CONNECTIONS[$this->_database_dsn_md5];
if ($joinFull) {
if ($this->_query['data_select'] == '*') {
$this->selectAdd();
$keys = $this->table();
foreach ($keys as $key => $value) {
$this->selectAdd($this->tableName() . '.' . $key . ' AS ' . $key);
}
}
$keys = $obj->table();
foreach ($keys as $key => $value) {
$this->selectAdd($obj->tableName() . '.' . $key . ' AS ' . $obj->tableName() . '_' . $key);
}
}
/* look up the links for obj table */
//print_r($obj->links());
if (!$ofield && ($olinks = $obj->links())) {
foreach ($olinks as $k => $v) {
/* link contains {this column} = {linked table}:{linked column} */
$ar = explode(':', $v);
if ($ar[0] == $this->__table) {
// you have explictly specified the column
// and the col is listed here..
// not sure if 1:1 table could cause probs here..
if ($joinCol !== false) {
throw new Exception("joinAdd: You cannot target a join column in the " . "'link from' table ({$obj->__table}). " . "Either remove the fourth argument to joinAdd() " . "({$joinCol}), or alter your links.ini file.", DB_DATAOBJECT_ERROR_NODATA);
return false;
}
$ofield = $k;
$tfield = $ar[1];
break;
}
}
}
/* otherwise see if there are any links from this table to the obj. */
//print_r($this->links());
if ($ofield === false && ($links = $this->links())) {
foreach ($links as $k => $v) {
/* link contains {this column} = {linked table}:{linked column} */
$ar = explode(':', $v);
if ($ar[0] == $obj->__table) {
if ($joinCol !== false) {
if ($k == $joinCol) {
$tfield = $k;
$ofield = $ar[1];
break;
} else {
continue;
}
} else {
$tfield = $k;
$ofield = $ar[1];
break;
}
}
}
}
/* did I find a conneciton between them? */
if ($ofield === false) {
throw new Exception("joinAdd: {$obj->__table} has no link with {$this->__table}", DB_DATAOBJECT_ERROR_NODATA);
return false;
}
$joinType = strtoupper($joinType);
// we default to joining as the same name (this is remvoed later..)
if ($joinAs === false) {
$joinAs = $obj->__table;
}
$quoteIdentifiers = !empty(DB_DataObject2::$CONFIG['quote_identifiers']);
// not sure how portable adding database prefixes is..
$objTable = $quoteIdentifiers ? $DB->quoteIdentifier($obj->__table) : $obj->__table;
// as far as we know only mysql supports database prefixes..
// prefixing the database name is now the default behaviour,
// as it enables joining mutiple columns from multiple databases...
/* if (
in_array($DB->dsn['phptype'],array('mysql','mysqli')) &&
strlen($obj->_database)
)
{
// prefix database (quoted if neccessary..)
$objTable = ($quoteIdentifiers
? $DB->quoteIdentifier($obj->_database)
: $obj->_database)
. '.' . $objTable;
} */
// nested (join of joined objects..)
$appendJoin = '';
if ($obj->_join) {
/* // postgres allows nested queries, with ()'s
// not sure what the results are with other databases..
// may be unpredictable..
if (in_array($DB->dsn["phptype"],array('pgsql'))) {
$objTable = "($objTable {$obj->_join})";
} else {
$appendJoin = $obj->_join;
} */
$appendJoin = $obj->_join;
}
$table = $this->__table;
if ($quoteIdentifiers) {
$joinAs = $DB->quoteIdentifier($joinAs);
$table = $DB->quoteIdentifier($table);
$ofield = $DB->quoteIdentifier($ofield);
$tfield = $DB->quoteIdentifier($tfield);
}
// add database prefix if they are different databases
$fullJoinAs = '';
$addJoinAs = ($quoteIdentifiers ? $DB->quoteIdentifier($obj->__table) : $obj->__table) != $joinAs;
if ($addJoinAs) {
// join table a AS b - is only supported by a few databases and is probably not needed
// , however since it makes the whole Statement alot clearer we are leaving it in
// for those databases.
// $fullJoinAs = in_array($DB->dsn["phptype"],array('mysql','mysqli','pgsql')) ? "AS {$joinAs}" : $joinAs;
// EVIL BUGFIX / TODO
$fullJoinAs = "AS {$joinAs}";
} else {
// if
/* if (
in_array($DB->dsn['phptype'],array('mysql','mysqli')) &&
strlen($obj->_database)
)
{
$joinAs = ($quoteIdentifiers ? $DB->quoteIdentifier($obj->_database) : $obj->_database) . '.' . $joinAs;
} */
$joinAs = ($quoteIdentifiers ? $DB->quoteIdentifier($obj->getDriver()->getDatabaseName()) : $obj->getDriver()->getDatabaseName()) . '.' . $joinAs;
}
$mytable = $joinMeAs ? $joinMeAs : $table;
switch ($joinType) {
case 'INNER':
case 'LEFT':
case 'RIGHT':
// others??? .. cross, left outer, right outer, natural..?
if (empty($on)) {
$on = "{$joinAs}.{$ofield}={$mytable}.{$tfield} {$appendJoin}";
}
$this->_join .= "\n {$joinType} JOIN {$objTable} {$fullJoinAs}" . " ON " . $on;
break;
case '':
// this is just a standard multitable select..
$this->_join .= "\n , {$objTable} {$fullJoinAs} {$appendJoin}";
$this->whereAdd("{$joinAs}.{$ofield}={$mytable}.{$tfield}");
}
// if obj only a dataobject - eg. no extended class has been defined..
// it obvioulsy cant work out what child elements might exist...
// untill we get on the fly querying of tables..
if (strtolower(get_class($obj)) == 'db_dataobject2') {
return true;
}
/* now add where conditions for anything that is set in the object */
$items = $obj->table();
// will return an array if no items..
// only fail if we where expecting it to work (eg. not joined on a array)
if (!$items) {
throw new Exception("joinAdd: No table definition for {$obj->__table}", DB_DATAOBJECT_ERROR_INVALIDCONFIG);
return false;
}
foreach ($items as $k => $v) {
if (!isset($obj->{$k})) {
continue;
}
$kSql = $quoteIdentifiers ? $DB->quoteIdentifier($k) : $k;
if ($v & DB_DATAOBJECT_STR) {
$this->whereAdd("{$joinAs}.{$kSql} = " . $this->_quote((string) ($v & DB_DATAOBJECT_BOOL ? $obj->{$k} === 'f' ? 0 : (int) (bool) $obj->{$k} : $obj->{$k})));
continue;
}
if (is_numeric($obj->{$k})) {
$this->whereAdd("{$joinAs}.{$kSql} = {$obj->{$k}}");
continue;
}
if (is_a($obj->{$k}, 'DB_DataObject2_Cast')) {
$value = $obj->{$k}->toString($v, $DB);
if (PEAR::isError($value)) {
throw new Exception($value->getMessage(), DB_DATAOBJECT_ERROR_INVALIDARG);
return false;
}
if (strtolower($value) === 'null') {
$this->whereAdd("{$joinAs}.{$kSql} IS NULL");
continue;
} else {
$this->whereAdd("{$joinAs}.{$kSql} = {$value}");
continue;
}
}
/* this is probably an error condition! */
$this->whereAdd("{$joinAs}.{$kSql} = 0");
}
if (!isset($this->_query)) {
throw new Exception("joinAdd can not be run from a object that has had a query run on it,\n clone the object or create a new one and use setFrom()", DB_DATAOBJECT_ERROR_INVALIDARGS);
return false;
}
// and finally merge the whereAdd from the child..
if (!$obj->_query['condition']) {
return true;
}
$cond = preg_replace('/^\\sWHERE/i', '', $obj->_query['condition']);
$this->whereAdd("({$cond})");
return true;
}
