Триграмма дистанционных операций в sqlalchemy

Question

В настоящее время я пытаюсь использовать операции pg_trgm % а также <->, Индексы GIN для столбцов уже доступны, но я не могу найти sqlalchemy, эквивалентный ранее упомянутым операторам.

Что было бы лучшим подходом для решения этой проблемы, кроме написания чисто текстового запроса.

Простой пример запроса будет:

tag = test
tag_subq = session.query(sticker_tag.file_id, f'sticker_tag.name <-> {tag}'.label(distance)) \
    .filter(f'sticker_tag.name % {tag}')) \
    .filter('distance' < 0.3) \
    .subquery("tag_subq")

Вышеуказанный запрос, очевидно, не работает, а строка select и filter - это просто заполнитель для визуализации того, что я собираюсь сделать.

Answer 1

Вы можете использовать метод Operators.op(); это генерирует любой оператор, который вам нужен:

sticker_tag.name.op('<->')(tag)
sticker_tag.name.op('%')(tag)

Answer 2

Для людей, использующих Postgres, можно использовать similarity сделать это вместо этого.

ПРИМЕЧАНИЕ. Не забудьте установить pg_trgm расширение в вашем Postgres сначала: CREATE EXTENSION pg_trgm;

Вот пример использования SQLAlchemy:

      # ... other imports
from sqlalchemy import and_, func, or_

def search_store_product(search_string: str) -> Optional[list[Product]]:
    try:
        return session.query(Product).filter(
            or_(
                func.similarity(Product.name, search_string) > 0.6,
                func.similarity(Product.brand, search_string) > 0.4,
            ),
            and_(Product.updated_on >= datetime.utcnow() - timedelta(days=5)),
        ).order_by(Product.created_on).limit(20).all()

    except ProgrammingError as exception:
        logger.exception(exception)
        raise

    finally:
        session.close()

Answer 3

Если кому-то интересно, я провел несколько тестов, сравнивая метод и similarity(...) > xметод, и при использовании . В некоторых случаях более чем в 10 раз.

      SELECT * FROM X WHERE name % 'foo';

намного быстрее, чем

      SELECT name FROM x WHERE similarity(name, 'foo') > 0.7;

Поэтому я рекомендую использовать только similarity(..)функция в SELECTзаявление, если оно имеет отношение к вашему запросу. Как это:

      SELECT name, similarity(name, 'foo') FROM X WHERE name % 'foo';

Но вам нужно установить pg_trgm.similarity_thresholdПеред использованием %потому что значение по умолчанию равно 0,3, что, на мой взгляд, слишком нечетко и медленно для большинства приложений. Поэтому ответ rmn предпочтительнее, просто не забудьте установить similarity_thresholdкаждую сессию!

В SQL Alchemy это будет примерно так :

      db.session.execute('SET pg_trgm.similarity_threshold = 0.7;')
items = Model.query.filter(Model.name.op("%")(name)).all()