public function findHousesByPostalCode($postalCode)
{
$sql = '
SELECT *
FROM houses
WHERE postal_code = ?q';
return $this->db->execute($sql, [$postalCode])->fetchResult();
}
public function testTransactionCommitBeforeAnyQueries()
{
//There is a validation that we can start transaction
//before any queries and connection object will establish
//connection automatically (lazy connection)
$this->connection->start();
$this->connection->execute('SELECT 1');
$this->connection->commit();
$this->assertTrue(true);
}
public function findPlace($place)
{
$sql = '
SELECT p.id, pt.system_name type_system_name
FROM places p
INNER JOIN place_types pt
ON pt.id = p.type_id
WHERE lower(full_title) = lower(?q)';
return $this->db->execute($sql, [$place])->fetchOneOrFalse();
}
private function removeRows(array $rows)
{
$ids = [];
foreach ($rows as $row) {
if (empty($row[$this->keyFieldXml])) {
throw new \LogicException('Не найдено поле: ' . $this->keyFieldXml);
}
$ids[] = $row[$this->keyFieldXml];
}
$this->db->execute('DELETE FROM ?f WHERE ?f IN (?l)', [$this->table, $this->keyFieldDatabase, $ids]);
}
public function import(DataSource $reader)
{
$i = 0;
while ($rows = $reader->getRows($this->rowsPerInsert)) {
$this->db->execute($this->getQuery($rows[0]), [$rows]);
++$i;
if ($i % 100 == 0) {
$this->db->getLogger()->reset();
}
}
return $this->table;
}
public function getAddressLevelId($code)
{
$result = $this->db->execute('SELECT id FROM address_object_levels WHERE code = ?q', [$code])->fetchResult();
if ($result === null) {
throw new BadRequestException('Некорректное значение уровня адреса: ' . $code);
}
return $result;
}
public static function initPostgis(ConnectionInterface $db)
{
// initialize PostGIS in the DB if we can
// TODO find a way to check if postgis is installed correctly
try {
$db->execute('CREATE EXTENSION IF NOT EXISTS postgis');
} catch (QueryException $ignored) {
}
}
private function findPlaces(array $placeWords, array $type, $parentId)
{
$pattern = implode(' ', $placeWords);
$sql = "\n SELECT full_title title, (CASE WHEN have_children THEN 0 ELSE 1 END) is_complete, pt.system_name type_system_name\n FROM places p\n INNER JOIN place_types pt\n ON p.type_id = pt.id\n WHERE ?p\n ORDER BY p.title\n LIMIT ?e";
$whereParts = [$this->db->replacePlaceholders("p.title ilike '?e%'", [$pattern])];
if ($type) {
$whereParts[] = $this->db->replacePlaceholders('type_id = ?q', [$type['id']]);
}
if ($parentId) {
$whereParts[] = $this->db->replacePlaceholders('p.parent_id = ?q', [$parentId]);
}
$values = ['(' . implode($whereParts, ') AND (') . ')', $this->limit];
$items = $this->db->execute($sql, $values)->fetchAll();
if ($items) {
foreach ($items as $key => $item) {
$items[$key]['is_complete'] = $item['is_complete'] ? true : false;
$items[$key]['tags'] = ['place', $item['type_system_name']];
}
}
return $items;
}
private function getBiggestSuitableStack()
{
// Берем наименьший адрес и через родительские связки строим стек.
return $this->db->execute("WITH RECURSIVE\n address_stack(parent_id, title, full_title, level, address_level) AS (\n SELECT parent_id, prefix || ' ' || title, full_title, level, address_level\n FROM address_objects ao\n WHERE address_id IN (\n SELECT address_id\n FROM address_objects\n WHERE postal_code = ?q\n ORDER BY level\n LIMIT 1\n )\n UNION ALL\n SELECT r.parent_id, r.prefix || ' ' || r.title, r.full_title, r.level, r.address_level\n FROM address_objects r\n INNER JOIN address_stack ads\n ON ads.parent_id = r.address_id\n\n )\n SELECT parent_id, a.title, full_title, level, al.code address_level\n FROM address_stack a\n INNER JOIN address_object_levels al\n ON al.id = address_level\n ORDER BY level\n ", [$this->postalCode])->fetchAll();
}
public static function getLastVersionId(ConnectionInterface $db)
{
return (int) $db->execute('SELECT MAX(version_id) FROM update_log')->fetchResult();
}
public static function updateNextAddressLevelFlag(ConnectionInterface $db)
{
$db->execute("UPDATE address_objects ao\n SET next_address_level = tmp.address_level\n FROM (\n SELECT DISTINCT ON (parent_id) parent_id, address_level\n FROM address_objects\n WHERE parent_id IS NOT NULL\n ORDER BY parent_id, address_level\n ) tmp\n WHERE tmp.parent_id = address_id\n ");
}