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); } }