/** * Returns the complete SQL statement and the values to apply * * @return array ($sql, $vals) */ public function getSQL() { // FROM needs commas only for tables, not joins $from = ''; foreach ($this->from as $alias => $statement) { if ($this->type[$alias] == 'table' && $from) { $from .= ",\n"; } else { $from .= "\n"; } $from .= $statement; } // prepare aliases for the select columns $selects = array(); foreach ($this->select as $alias => $select) { $selects[] = "{$select} AS {$alias}"; } $sql = ' SELECT ' . join(",\n", $selects) . "\n" . ' FROM ' . $from . "\n" . ' WHERE ' . $this->where->toSQL() . "\n"; if ($this->groupby) { $sql .= 'GROUP BY ' . join(",\n", $this->groupby) . "\n"; } if ($this->orderby) { $sql .= 'ORDER BY ' . join(",\n", $this->orderby) . "\n"; } return $this->fixPlaceholders($sql); }
/** * returns where clause as string generated from QueryBuilderWhere instance * @param QueryBuilderWhere $where * @param bool $isSub - whether is it generated for sub where or not (true only for recursive call, so do not care) * @return string */ protected function getWhereStringByObject(QueryBuilderWhere $where, $isSub = false) { // whereString $whereString = ""; /* FULLTEXT WHERE */ if ($where instanceof QueryBuilderWhereFullText) { $columnsString = ""; $phrasesString = ""; foreach ($where->getColumns() as $columnName) { $columnsString .= strlen($columnsString) > 0 ? "," : ""; $columnsString .= reset(self::getQuotesColumnName()) . $columnName . end(self::getQuotesColumnName()); } foreach ($where->getPhrases() as $phrase) { $phrase = preg_replace('/[-!"#$%&\'()*+,./:;<=>?@[\\\\]^_`{|}~]/', '', $phrase); //$phrase = ereg_replace("[[:punct:]]", "", $phrase); if (strlen(trim($phrase)) < 1) { continue; } $phrase = $this->escapeString($phrase); $phrasesString .= strlen($phrasesString) > 0 ? " " : ""; $phrasesString .= '"' . $phrase . '"'; } $whereString = "MATCH (" . $columnsString . ")\n\t\t\t\t\t\t\t\tAGAINST ('{$phrasesString}')"; } else { if (count($where->getConditions()) < 1) { NULL; } else { foreach ($where->getConditions() as $condition) { $columnName = $condition["column"]; $glue = $condition["glue"] > 0 ? "OR" : "AND"; switch ($condition["comparison"]) { case -3: $comparisonValue = strtoupper($this->getValueWrapped($condition["value"])) == "NULL" ? " IS NOT NULL" : " != " . $this->getValueWrapped($condition["value"]) . ""; break; case -2: $comparisonValue = " < " . $this->getValueWrapped($condition["value"]) . ""; break; case -1: $comparisonValue = " <= " . $this->getValueWrapped($condition["value"]) . ""; break; case 0: $comparisonValue = strtoupper($this->getValueWrapped($condition["value"])) == "NULL" ? " IS NULL" : " = " . $this->getValueWrapped($condition["value"]) . ""; break; case 1: $comparisonValue = " >= " . $this->getValueWrapped($condition["value"]) . ""; break; case 2: $comparisonValue = " > " . $this->getValueWrapped($condition["value"]) . ""; break; case 3: $comparisonValue = " LIKE " . $this->getValueWrapped("%" . $condition["value"] . "") . ""; break; case 4: $comparisonValue = " LIKE " . $this->getValueWrapped("%" . $condition["value"] . "%") . ""; break; case 5: $comparisonValue = " LIKE " . $this->getValueWrapped("" . $condition["value"] . "%") . ""; break; default: throw new DbControlException("Ilegal comparison '" . $condition["comparison"] . "'!"); } $whereString .= strlen($whereString) > 0 ? " {$glue} " : ""; $whereString .= reset(self::getQuotesColumnName()) . $columnName . end(self::getQuotesColumnName()) . $comparisonValue; } } } foreach ($where->getWheres() as $subWhereSet) { $subWhere = $subWhereSet["where"]; $subWhereGlue = $subWhereSet["glue"] > 0 ? "OR" : "AND"; if (strlen($subWhereString = $this->getWhereStringByObject($subWhere, true)) < 1) { continue; } $whereString = strlen($whereString) > 0 ? "({$whereString}) {$subWhereGlue} " : ""; $whereString .= "({$subWhereString})"; } if (!$isSub && strlen($whereString) > 0) { $whereString = "WHERE {$whereString}"; } return $whereString; }
/** * returns prior set where instance * @return QueryBuilderWhere */ protected function getWhere() { try { if ($this->where instanceof QueryBuilderWhere) { return $this->where; } $itemType = $this->getClassVar("itemType"); $where = new QueryBuilderWhere(); $passwordColNames = eval("return {$itemType}::\$passwordColNames;"); @reset($this->filter); $fcur = @current($this->filter); if (!empty($fcur) || (int) $fcur === 0) { if ($this->filter !== false) { foreach ((array) $this->filter as $column => $value) { // password columns if (in_array($column, $passwordColNames)) { $value = md5($value); } $where->addConditionColumn($column, $value); } } } // add whereAdd addition if ($this->whereAdd instanceof QueryBuilderWhere) { $where->addWhere($this->whereAdd); } return $this->where = $where; } catch (Exception $e) { throw $e; } }
/** * getter na vsechny XT roles v systemu, krome roli, ktere jsou vyhrazene jako superrole * @param bool $superAdmin - with superadmin roles * @return XTRolesRecords * @throws Exception */ public static function getXTRoles($superAdmin = false) { try { if ($superAdmin) { $where = NULL; } else { //$where = $superAdmin ? NULL : "name != '". self::XT_ROLE_NAME_SUPERADMIN ."'"; $where = new QueryBuilderWhere(); $where->addConditionColumn("name", self::XT_ROLE_NAME_SUPERADMIN, -3); $where->addConditionColumn($pidColName, "<<NULL>>", 0, 1); } return new XTRolesRecords(false, array("id" => 0), false, $where); } catch (Exception $e) { throw $e; } }
/** * return descendants count * @return int * @throws Exception */ public function getDescendantsCount() { try { $tableName = $this->getClassVar("tableName"); $idColName = $this->getClassVar("idColName"); $treeColNames = $this->getClassVar("treeColNames"); $lftColName = $treeColNames[0]; $rgtColName = $treeColNames[1]; $bidColName = $treeColNames[3]; $myLft = $this->params[$lftColName]; $myRgt = $this->params[$rgtColName]; $myBid = $this->params[$bidColName]; $where = new QueryBuilderWhere(); $where->addConditionColumn($lftColName, $myLft, 2); $where->addConditionColumn($rgtColName, $myRgt, -2); $where->addConditionColumn($bidColName, $myBid); $sql = $this->getQueryBuilder()->getSelectCount($tableName, $where); // var_dump(__CLASS__ ."::". __LINE__ .": ". $this->getQueryBuilder()->getSelectCount($tableName, $where)); $result = $this->getDb()->initiateQuery($this->getQueryBuilder()->getSelectCount($tableName, $where)); return (int) $result->count; } catch (Exception $e) { throw $e; } }