Для получения полного доступа
зарегистрируйтесь.
RSS

Все сниппеты с тэгом «PostgreSQL»



Gravatar image
Insolita
  • Репутация: 7
  • Сниппеты: 3
  • Ревизии: 1
<?php

use yii\db\Migration;

class m170105_004305_add_fts extends Migration
{
    public function safeUp()
    {

        /*
         * PREPARE SEARCH CONFIGURATION
         *----------------------------
         */
        $this->getDb()->createCommand(
            '
           CREATE TEXT SEARCH DICTIONARY ispell_ru (
           template  =   ispell,
           dictfile  =   ru,
           afffile   =   ru,
           stopwords =   russian
           );
           '
        )->execute();
        $this->getDb()->createCommand(
            '
           CREATE TEXT SEARCH DICTIONARY ispell_en (
           template  = ispell,
           dictfile  = en,
           afffile   = en,
           stopwords = english
           );
           '
        )->execute();
        $this->getDb()->createCommand('CREATE TEXT SEARCH CONFIGURATION ru ( COPY = russian );')->execute();
        $this->getDb()->createCommand(
            'ALTER TEXT SEARCH CONFIGURATION ru
           ALTER MAPPING
           FOR word, hword, hword_part
           WITH ispell_ru, russian_stem;
           '
        )->execute();
        $this->getDb()->createCommand(
            'ALTER TEXT SEARCH CONFIGURATION ru
           ALTER MAPPING
           FOR asciiword, asciihword, hword_asciipart
           WITH ispell_en, english_stem;'
        )->execute();
        $this->getDb()->createCommand('SET default_text_search_config = \'ru\';')->execute();
       
        /** ADD tsvector column **/
        $this->getDb()->createCommand(
            '
           ALTER TABLE {{%tovar}} ADD COLUMN fts tsvector;
        '
        )->execute();
        $this->getDb()->createCommand(
            '
           UPDATE {{%tovar}} SET fts=
setweight( coalesce( to_tsvector(\'ru\', [[name]]),\'\'),\'A\') || \' \' ||
setweight( coalesce( to_tsvector(\'ru\', [[description]]),\'\'),\'B\') || \' \';
        '
        )->execute();
        $this->getDb()->createCommand('create index fts_index on {{%tovar}} using gin (fts);')->execute();
        
        /**
         * ---   ADD AUTO FILL fts TRIGGER ON INSERT NEW RECORD
         * (in my case 'on update' trigger not neccessary)
        **/
        $this->getDb()->createCommand(
            '
            CREATE FUNCTION fts_vector_update() RETURNS TRIGGER AS $$
BEGIN
   NEW.fts=setweight( coalesce( to_tsvector(\'ru\', NEW.name),\'\'),\'A\') || \' \' ||
			setweight( coalesce( to_tsvector(\'ru\', NEW.description),\'\'),\'B\') || \' \';
			RETURN NEW;
END;
$$ LANGUAGE \'plpgsql\';
CREATE TRIGGER tovar_fts_update BEFORE INSERT ON {{%tovar}}
FOR EACH ROW EXECUTE PROCEDURE fts_vector_update();
        '
        )->execute();
    }

    public function safeDown()
    {
        $this->dropIndex('fts_index', '{{%tovar}}');
        $this->dropColumn('{{%tovar}}', 'fts');
        $this->getDb()->createCommand('DROP TRIGGER tovar_fts_update ON {{%tovar}}')->execute();
        $this->getDb()->createCommand('DROP FUNCTION IF EXISTS fts_vector_update()')->execute();
    }
}

Gravatar image
Insolita
  • Репутация: 7
  • Сниппеты: 3
  • Ревизии: 1
 
    public function findSuggest(string $query, int $cat = null): array
    {
        $query = $this->prepareQuery($query);
        $tQuery = (new Query())->from('{{%tovar}}')
                               ->select([
                                   '{{%tovar}}.id',
                                   '{{%tovar}}.name',
                                   '{{%tovar}}.slug',
                                   '{{%category}}.name as category',
                                   new Expression("ts_rank({{%tovar}}.fts,plainto_tsquery('ru', :q)) as rank"),
                                        ])
                               ->leftJoin('{{%category}}','{{%tovar}}.category_id={{%category}}.id')
                               ->where(new Expression("{{%tovar}}.fts  @@ plainto_tsquery('ru', :q)", [':q' => $query]))
                               ->limit(10)
                               ->orderBy(['rank' => SORT_DESC]);
        if($cat > 0){
            $tQuery->andWhere(['{{%tovar}}.category_id'=>$cat]);
        }
        return $tQuery->all();
    }
Gravatar image
graid2030
  • Репутация: 6
  • Сниппеты: 1
  • Ревизии: 0

Конвертация при хранении дат в int'овом формате туда и обратно.

-- Из числа в timestamp (2013-12-15 17:26:25+04)
SELECT to_timestamp(1387113985);
-- Из числа в дату (2013-12-15)
SELECT to_timestamp(1387113985)::date;
-- Из числа в произвольный формат (2013\12\15 17:26:25)
SELECT to_char(to_timestamp(1387113985), 'YYYY\MM\DD HH24:MI:SS');

-- Из timestamp с учетом временной зоны в число (1387128385)
SELECT EXTRACT(EPOCH FROM '2013-12-15 17:26:25+04' at time zone 'Europe/Moscow')::int;