/**
	 * 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;
	}