/**
* If a left join is needed by the <data> $data element:
* Adds if not yet added a JOIN statement for the <data> element $data
* and returns the table AS alias for accessing the corresponding table.
* Otherwise returns NULL.
*
* @param SimpleXmlElement $data
* @param array $subFormula For process_reverse_sql_field ONLY (if we decide to use): SQL conditions in array which are imploded by AND for the merge
* @return string|null Name of table alias (a to z) if the <data> element required a left join.
*/
protected function _addGetJoinAs($data, $subFormula = null)
{
$tableAs = null;
$cnt_name = $data->attributes('name');
$cnt_as = $data->attributes('as');
$cnt_table = $data->attributes('table');
$cnt_key = $data->attributes('key');
$cnt_val = $data->attributes('value');
$cnt_valtype = $data->attributes('valuetype');
$cnt_joinkeys = $data->getElementByPath('joinkeys');
if ($cnt_table && $this->_table && ($cnt_table != $this->_table || $cnt_key && $cnt_val || $cnt_joinkeys)) {
// Compute JOIN keyword, e.g. LEFT JOIN:
$joinKeyword = $this->computeJoinKeyword($cnt_joinkeys);
if ($cnt_joinkeys) {
if (!$cnt_key && !$cnt_val) {
// compute the array-indexes for the leftJoindTableKeyValueArray:
foreach ($cnt_joinkeys->children() as $column) {
/** @var $column SimpleXmlElement */
$cnt_key[] = $column->attributes('name');
$cnt_val[] = $column->attributes('value');
$cnt_valtype[] = $column->attributes('valuetype');
$subFormula[] = $column->attributes('operator') . $column->attributes('type');
// Could be this but that doesn't work for group-search in CB User Management, to check later why:
// $subFormula[] = $this->_db->NameQuote( $column->attributes( 'name' )) . ' ' . $column->attributes( 'operator' ) . ' ' . $this->_currentTableAs . '.'
// . ( in_array( $column->attributes( 'valuetype' ), array( 'sql:field' ) ) ? $this->_db->NameQuote( $column->attributes( 'value' ) ) : $column->attributes( 'value' ) );
}
$cnt_key = implode('&', $cnt_key);
$cnt_val = implode('&', $cnt_val);
$cnt_valtype = implode('&', $cnt_valtype);
// Above change would make this look like WHERE statements: sql:field`user_id` = a.`id`
// done below: $subFormulaArrayKey = implode( '&', $subFormula );
} else {
trigger_error(sprintf('SQLXML::addGetJoinAs notice: data %s has joinkeys and key="%s" and/or value="%s" at same time. Ignoring key and value.', $cnt_name, $cnt_key, $cnt_val), E_USER_NOTICE);
}
}
$subFormulaArrayKey = is_array($subFormula) ? implode('&', $subFormula) : $subFormula;
// if different table or same table but a key and a value are specified as self-join,
// field value is taken from related record in other or self-joined table:
if (isset($this->leftJoindTableKeyValueArray[$cnt_table][$cnt_key][$cnt_val][$cnt_valtype . $subFormulaArrayKey])) {
$tableAs = $this->leftJoindTableKeyValueArray[$cnt_table][$cnt_key][$cnt_val][$cnt_valtype . $subFormulaArrayKey];
} else {
$this->incrementTableAs();
if ($cnt_joinkeys) {
$subFormulaReal = $this->process_joinkeys($cnt_joinkeys);
$this->leftJoinArray[$this->tableAs] = $joinKeyword . ' ' . $this->_db->NameQuote($cnt_table) . ' AS ' . $this->tableAs . ' ON ' . $subFormulaReal;
} elseif (!$cnt_valtype || $cnt_valtype == 'sql:field' || $cnt_valtype == 'sql:parentfield') {
$this->leftJoinArray[$this->tableAs] = $joinKeyword . ' ' . $this->_db->NameQuote($cnt_table) . ' AS ' . $this->tableAs . ' ON ' . $this->tableAs . '.' . ($subFormula === null ? '`' . $cnt_key . '` = ' . ($cnt_valtype == 'sql:parentfield' ? $this->_currentTableAsStack[0] : $this->_currentTableAs) . '.`' . $cnt_val . '`' : implode(' AND ', $subFormula));
//TBD this $subFormula is a temporary simplification/hack for process_reverse_sql_field ONLY: not even sure if it's needed !!! : check if really needed.
} elseif ($cnt_key && $cnt_val && $cnt_valtype) {
$value = $this->sqlCleanQuote($cnt_val, $cnt_valtype);
$this->leftJoinArray[$this->tableAs] = $joinKeyword . ' ' . $this->_db->NameQuote($cnt_table) . ' AS ' . $this->tableAs . ' ON ' . $this->tableAs . '.`' . $cnt_key . '` = ' . $value;
}
$this->leftJoindTableKeyValueArray[$cnt_table][$cnt_key][$cnt_val][$cnt_valtype . $subFormulaArrayKey] = $this->tableAs;
$tableAs = $this->tableAs;
}
$this->leftJoinedFieldsTable[$cnt_as ? $cnt_as : $cnt_name] = $tableAs;
}
return $tableAs;
}