/**
* Search of AutoComplete type for fields that contained foreign keys.
*
* POST params:
* string $term Searching value (the search will be executed by {$term}%)
* string $fieldBy Field in SQL table is to search by
* string $keyData Encoded into a row data about a table which is referred by the key
*
* @todo Security problem: there is need to restrict the search within the tables which are not denied for user (ideally - only within the controller the query is going out from).
*/
public function actionForeignkey()
{
$term = Yii::app()->request->getPost('term');
$fieldBy = Yii::app()->request->getPost('fieldBy');
$keyData = Yii::app()->request->getPost('keyData');
if(empty($term) || empty($fieldBy) || empty($keyData))
throw new CHttpException(406);
$field = AAHelperUrl::decodeParam($keyData);
//Primitively exclude an opportunity to access another DB, input injections and so other
if(preg_match('/[^a-z_]/i', $field->options['table']) || !isset($field->options['searchBy'][$fieldBy]))
throw new CHttpException(406);
$data = array();
$matches = array();
$q = Yii::app()->{AADb::$dbConnection}->createCommand()
->from($field->options['table'])
->select(array_merge(array($field->options['pk']), array_keys($field->options['select'])))
->where(array('LIKE', "{$field->options['table']}.{$fieldBy}", AADb::metaToLike($term)))
->order($fieldBy);
$result = $q->queryAll();
foreach($result as $r)
{
$label = "";
foreach($r as $kField=>$value)
{
if($kField != $field->options['pk'])
$label .= ($label ? ' - ' : '').$value;
}
$matches[] = array('label'=>$label, 'value'=>$r[$field->options['pk']]);
}
echo CJSON::encode($matches);
}
public function modifySqlQuery()
{
//Joining tables by foreign key (one to many relation)
if($this->options['select'])
{ //There is no sense to join anything for overall data list if select fields of foreign table was not set
$modifySql = array(
'select' => array(),
'join' => array(),
);
foreach($this->options['select'] as $fieldName=>$fieldAlias)
{
$modifySql['select'][] = "{$this->options['tableAlias']}.{$fieldName} AS {$fieldAlias}";
}
$modifySql['join']['table'] = "{$this->options['table']} AS {$this->options['tableAlias']}";
if(!empty($this->options['dbName'])) //Foreign table is in another DB
$modifySql['join']['table'] = "{$this->options['dbName']}.{$modifySql['join']['table']}";
$modifySql['join']['type'] = $this->allowNull ? 'left' : 'inner';
$modifySql['join']['conditions'] = array('AND', "{$this->tableName}.{$this->name} = {$this->options['tableAlias']}.{$this->options['pk']}");
if(!empty($this->options['conditions']))
{
$conditions = $this->options['conditions'];
AADb::addTableAliasToCond($conditions, $this->options['table'], $this->options['tableAlias']);
$modifySql['join']['conditions'][] = $conditions;
if(!empty($this->options['params']))
$modifySql['join']['params'] = $this->options['params'];
}
return $modifySql;
}
return false;
}