/**
* Validate join filters.
* @dataProvider join_filter_provider
* @param array $init
* @param array $expected00
* @param array $expected01
* @param array $expected10
* @param array $expected11
*/
public function test_join_filter($init, $expected00, $expected01, $expected10, $expected11)
{
$construct = function ($localfield, $foreigntable, $foreignfield, data_filter $filter = null, $notexist = false, $unique = true) {
return new join_filter($localfield, $foreigntable, $foreignfield, $filter, $notexist, $unique);
};
$filter = call_user_func_array($construct, $init);
data_filter::$_prefix_num = 0;
$this->assertfiltersqlequals($expected00, $filter->get_sql());
data_filter::$_prefix_num = 0;
$this->assertfiltersqlequals($expected01, $filter->get_sql(true));
data_filter::$_prefix_num = 0;
$this->assertfiltersqlequals($expected10, $filter->get_sql(false, 'x'));
data_filter::$_prefix_num = 0;
$this->assertfiltersqlequals($expected11, $filter->get_sql(true, 'x'));
}
public function get_sql($use_join = false, $tablename = null, $paramtype = SQL_PARAMS_QM, moodle_database $db = null)
{
$rv = array();
if ($tablename) {
$local_field = "{$tablename}.{$this->local_field}";
} else {
$local_field = $this->local_field;
}
$jointablename = data_filter::_get_unique_name();
if ($use_join && $this->unique) {
if ($this->not_exist) {
// get the filter SQL to tack on to the JOIN condition
$filter_sql = $this->filter ? $this->filter->get_sql(false, $jointablename, $paramtype, $db) : array();
$add_filter = empty($filter_sql) ? '' : "AND ({$filter_sql['where']})";
// and create the join
$rv['join'] = "LEFT JOIN {{$this->foreign_table}} {$jointablename}\n ON {$jointablename}.{$this->foreign_field} = {$local_field} {$add_filter}";
$rv['join_parameters'] = isset($filter_sql['where_parameters']) ? $filter_sql['where_parameters'] : array();
$rv['where'] = "{$jointablename}.id IS NULL";
$rv['where_parameters'] = array();
} else {
// get the sql from the filter
if ($this->filter) {
$filter_sql = $this->filter->get_sql(true, $jointablename, $paramtype, $db);
} else {
$filter_sql = array();
}
if (isset($filter_sql['where'])) {
$rv['where'] = $filter_sql['where'];
$rv['where_parameters'] = $filter_sql['where_parameters'];
}
// and create the join
$rv['join'] = "JOIN {{$this->foreign_table}} {$jointablename} ON {$jointablename}.{$this->foreign_field} = {$local_field}" . (isset($filter_sql['join']) ? ' ' . $filter_sql['join'] : '');
$rv['join_parameters'] = array();
if (isset($filter_sql['join'])) {
$rv['join_parameters'] = $rv['join_parameters'] + $filter_sql['join_parameters'];
}
}
} else {
$filter_sql = $this->filter ? $this->filter->get_sql(true, $jointablename, $paramtype, $db) : array();
if ($tablename) {
// if the table name is specified, we can use the more
// efficient EXISTS instead of IN
$exists = $this->not_exist ? 'NOT EXISTS' : 'EXISTS';
$params = array();
$sql = "{$exists} (SELECT 'x'\n FROM {{$this->foreign_table}} {$jointablename} ";
if (isset($filter_sql['join'])) {
$sql .= $filter_sql['join'];
$params = $filter_sql['join_parameters'];
}
$sql .= " WHERE {$jointablename}.{$this->foreign_field} = {$local_field}";
if (isset($filter_sql['where'])) {
$sql .= " AND {$filter_sql['where']}";
$params += $filter_sql['where_parameters'];
}
$sql .= ')';
$rv['where'] = $sql;
$rv['where_parameters'] = $params;
} else {
$filter_sql = $this->filter ? $this->filter->get_sql(true, $jointablename, $paramtype, $db) : array();
$in = $this->not_exist ? 'NOT IN' : 'IN';
$params = array();
$sql = "{$local_field} {$in} (SELECT {$jointablename}.{$this->foreign_field}\n FROM {{$this->foreign_table}} {$jointablename} ";
if (isset($filter_sql['join'])) {
$sql .= $filter_sql['join'];
$params = $filter_sql['join_parameters'];
}
if (isset($filter_sql['where'])) {
$sql .= " WHERE {$filter_sql['where']}";
$params += $filter_sql['where_parameters'];
}
$sql .= ')';
$rv['where'] = $sql;
$rv['where_parameters'] = $params;
}
}
return $rv;
}
public function get_sql($use_join = false, $tablename = null, $paramtype = SQL_PARAMS_QM, moodle_database $db = null)
{
global $DB;
if ($tablename) {
$name = "{$tablename}.{$this->idfield}";
} else {
$name = $this->idfield;
}
$field_filter = new field_filter('COALESCE(fdata.data, fdefault.data)', $this->value, $this->comparison);
$field_filter = $field_filter->get_sql(false, null, $paramtype, $db);
if ($paramtype == SQL_PARAMS_NAMED) {
$paramindex = data_filter::_get_unique_name('param');
$paramname = ":{$paramindex}";
} else {
$paramname = '?';
$paramindex = 0;
}
$sql = "SELECT ctx.id\n FROM {context} ctx\n LEFT JOIN {{$this->field->data_table()}} fdata ON fdata.contextid = ctx.id AND fdata.fieldid = {$this->field->id}\n LEFT JOIN {{$this->field->data_table()}} fdefault ON fdefault.contextid IS NULL AND fdefault.fieldid = {$this->field->id}\n WHERE ctx.contextlevel = {$paramname}";
$params = array($paramindex => $this->contextlevel);
if (isset($field_filter['where'])) {
$sql .= " AND {$field_filter['where']}";
if (!empty($field_filter['where_parameters']) && is_array($field_filter['where_parameters'])) {
$params = array_merge($params, $field_filter['where_parameters']);
}
}
if ($tablename) {
// if the table name is specified, we can use the more
// efficient EXISTS instead of IN
return array('where' => "EXISTS ({$sql} AND ctx.instanceid = {$name})", 'where_parameters' => $params);
} else {
return array('where' => "{$name} IN ({$sql})", 'where_parameters' => $params);
}
}
public function get_sql($use_join = false, $tablename = null, $paramtype = SQL_PARAMS_QM, moodle_database $db = null)
{
global $DB;
if ($db === null) {
$db = $DB;
}
$clsttable = data_filter::_get_unique_name();
$parenttable = data_filter::_get_unique_name();
$childtable = data_filter::_get_unique_name();
$childclsttable = data_filter::_get_unique_name();
$parent_path = $db->sql_concat("{$parenttable}.path", "'/%'");
$sql = "SELECT {$clsttable}.id\n FROM {" . userset::TABLE . "} {$clsttable}\n JOIN {context} {$parenttable}\n ON {$parenttable}.instanceid = {$clsttable}.id\n AND {$parenttable}.contextlevel = " . CONTEXT_ELIS_USERSET . "\n JOIN {context} {$childtable}\n ON {$childtable}.path LIKE {$parent_path}\n AND {$childtable}.contextlevel = " . CONTEXT_ELIS_USERSET . "\n JOIN {" . userset::TABLE . "} {$childclsttable}\n ON {$childtable}.instanceid = {$childclsttable}.id ";
$filtersql = $this->filter->get_sql(true, $childclsttable, $paramtype, $db);
$params = array();
if (isset($filtersql['join'])) {
$sql .= $filtersql['join'];
$params = $filtersql['join_parameters'];
}
if (isset($filtersql['where'])) {
$sql .= ' WHERE ' . $filtersql['where'];
$params = $filtersql['where_parameters'];
}
$NOT = $this->not_subset ? 'NOT ' : '';
if ($tablename) {
return array('where' => "{$NOT}EXISTS (" . $sql . (isset($filtersql['where']) ? " AND " : " WHERE ") . "{$clsttable}.id = {$tablename}.{$this->name})", 'where_parameters' => $params);
} else {
return array('where' => "{$this->name} {$NOT}IN (" . $sql . ')', 'where_parameters' => $params);
}
}
