protected function fetch(SqlBuilder $builder, $hasJoin, array $values)
{
$primaryKey = $this->targetRepository->getMapper()->getStorageReflection()->getStoragePrimaryKey()[0];
$builder->addWhere($primaryKey, $values);
$builder->addSelect(($hasJoin ? 'DISTINCT ' : '') . $builder->getTableName() . '.*');
$result = $this->context->queryArgs($builder->buildSelectQuery(), $builder->getParameters());
$entities = [];
while ($data = $result->fetch()) {
$entity = $this->targetRepository->hydrateEntity((array) $data);
$entities[$entity->id] = $entity;
}
return new EntityContainer($entities);
}
/**
* Spusti vytvoreny SQL dotaz se zadanymi parametry
*
* @param string[] $par_where Pole filtru zadane uzivatelem.
* @param string[] $par_order Pole s tridicimi kriterii zadane uzivatelem.
* @param string[] $columns Pole pozadovanych sloupců.
* @param string[] $table Nazev tabulky.
* @param string[] $limit Maximalni velikost vysledku dotazu.
*
* @return array Vybrane radky z tabulky
* @return NULL Nebyly zvoleny vyberova ani tridici kriteria
*/
public function getTimeData($par_where, $par_order, $columns, $table, $limit)
{
$this->par_where = $par_where;
$this->par_order = $par_order;
$this->columns = $columns;
$this->table = $table;
$this->limit = $limit;
if (count($this->par_where, COUNT_RECURSIVE) > 1) {
$this->where = "sm_time.id_event = sm_event.id AND sm_time.id_swimmer = sm_swimmer.id AND ";
}
$this->createSelect();
$this->createWhere();
$this->createOrderBy();
$this->createParVal();
$this->createCommand();
//nebyly zvoleny vyberova ani tridici kriteria
if ($this->select === NULL) {
return NULL;
}
if (count($this->par_where, COUNT_RECURSIVE) <= 1) {
$this->select = substr_replace($this->select, " WHERE sm_time.id_event = sm_event.id AND sm_time.id_swimmer = sm_swimmer.id ", strpos($this->select, "ORDER") - 1, 0);
}
if ($this->limit !== NULL) {
$this->select = $this->select . " LIMIT " . $this->limit;
}
$data = $this->database->queryArgs($this->select, $this->parval);
$this->select = "SELECT\n";
$this->where = "";
$this->order = "";
$this->table = "";
return $data;
}
public function remove(IEntity $parent, array $remove)
{
$this->mapperOne->beginTransaction();
$list = $this->buildList($parent, $remove);
$builder = new SqlBuilder($this->joinTable, $this->context);
$builder->addWhere(array_keys(reset($list)), $list);
$this->context->queryArgs($builder->buildDeleteQuery(), $builder->getParameters());
}
protected function execute()
{
$builder = clone $this->builder;
$builder->addSelect(($this->distinct ? 'DISTINCT ' : '') . $builder->getTableName() . '.*');
$result = $this->context->queryArgs($builder->buildSelectQuery(), $builder->getParameters());
$this->result = [];
while ($data = $result->fetch()) {
$this->result[] = $this->repository->hydrateEntity((array) $data);
}
}
/**
* Updates all rows in result set.
* Joins in UPDATE are supported only in MySQL
* @param array|\Traversable ($column => $value)
* @return int number of affected rows
*/
public function update($data)
{
if ($data instanceof \Traversable) {
$data = iterator_to_array($data);
} elseif (!is_array($data)) {
throw new Nette\InvalidArgumentException();
}
if (!$data) {
return 0;
}
return $this->context->queryArgs($this->sqlBuilder->buildUpdateQuery(), array_merge(array($data), $this->sqlBuilder->getParameters()))->getRowCount();
}
/**
* Insert row in database or update existing one.
*
* @param array
* @return \Nette\Database\Table\ActiveRow automatically found based on first "column => value" pair in $values
*/
public function createOrUpdate(array $values)
{
$pairs = array();
foreach ($values as $key => $value) {
$pairs[] = "`{$key}` = ?";
// warning: SQL injection possible if $values infected!
}
$pairs = implode(', ', $pairs);
$values = array_values($values);
$this->db->queryArgs('INSERT INTO `' . $this->tableName . '` SET ' . $pairs . ' ON DUPLICATE KEY UPDATE ' . $pairs, array_merge($values, $values));
return $this->findOneBy(func_get_arg(0));
}
/**
* Mark given file as done in database updates table.
* @param string $filename
* @throws \Exception
*/
private function setUpdateAsDone($filename)
{
if (!$this->isUpdateTableCreated($this->db, $this->dbUpdateTable)) {
$this->createUpdateTable();
}
$matches = array();
if (!preg_match("/(\\d{4}-\\d{2}-\\d{2})_(\\d{2}-\\d{2}-\\d{2})/", $filename, $matches)) {
throw new \Exception("Filename '{$filename}' is not in valid format.");
}
list(, $date, $time) = $matches;
$this->db->queryArgs("REPLACE INTO `{$this->dbUpdateTable}` (created_at) VALUES (?)", array($date . " " . strtr($time, "-", ":")));
}
private function fetchCounts(SqlBuilder $builder, array $values)
{
$targetStoragePrimaryKey = $this->targetMapper->getStorageReflection()->getStoragePrimaryKey()[0];
$builder = clone $builder;
$table = $builder->getTableName();
$builder->addSelect("{$table}.{$this->joinStorageKey}");
$builder->addSelect("COUNT({$table}.{$targetStoragePrimaryKey}) AS count");
$builder->addWhere("{$table}.{$this->joinStorageKey}", $values);
$builder->setGroup("{$table}.{$this->joinStorageKey}");
$builder->setOrder([], []);
$result = $this->context->queryArgs($builder->buildSelectQuery(), $builder->getParameters());
$counts = [];
foreach ($result->fetchAll() as $row) {
$counts[$row->{$this->joinStorageKey}] = $row['count'];
}
return $counts;
}
