Как проверить содержит ли модель ключевые слова?

Question

[KonstantinDyulin]

У меня на проекте должен быть функционал подписки по ключевым словам на уведомления о новых вакансиях.
Я сохраняю строку содержающую ключевые слова, и email пользователя в таблице.
Теперь мне нужно при создании вакансии, запускать event о создании вакансии, и отправлять в очередь job в котором будет описана логика в которой нужно будет проходится по всем записям из таблицы с ключевыми словами.
Вопрос только в том как описать эту логику.
У модели вакансии есть следующие поля в которых нужно будет проверять наличие ключевых слов:
Name, description, у нее так же есть метод который определяет отношение HasMany к ключевым навыкам, в которых так же надо будет искать вхождения.
Буду благодарен любым предложениям, как можно реализовать логику поиска

Comments

[Дмитрий]

не совсем понятно что вы хотите конкретно. чисто теоретически я бы сделал так. создал набор ключевых слов/токенов вообще. при создании вакансии искал бы их в данных и создавал связку вакансия <-> ключевые слова. А дальше найти людей которые привязались к этим же ключевым словам - вопрос одного запроса вообщем то.

[KonstantinDyulin]

Дмитрий, Мне вот как раз нужно и искать в данных вакансии ключевые слова/токены, которые как раз у меня перечислены в таблице подписок в поле key_words. Вопрос в том как реализовать этот самый поиск(я просто ни разу подобного не делал).

[iljaGolubev]

KonstantinDyulin, https://laravel.com/docs/10.x/queries#full-text-wh...

[Дмитрий]

KonstantinDyulin, в поле key_words - по мне так плохое решение и название так себе. у вас должна быть таблица keywords. и связка многие ко многим user_to_keywords и vacancy_to_keywords. тогда при прописанных релейшенах у вас будет что нибудь

$users = User::query()->whereHas('keywords', function($query) use($vacancy){
         return $query->whereIn('keyword', $vacancy->keywords->pluck('keyword'));
})->get();

[iljaGolubev]

Дмитрий, не сработает - в Name, description произвольный текст, как я понимаю.

[Дмитрий]

iljaGolubev, ну этот произвольный текст и стоит превратить в ключевые слова и связь многие ко многим. и это будет наверное правильнее - нежели каждый раз в базе разбирать строку keywords у пользователя и сидеть в той же базе матчить совпадения. без вопросов можно - но скорее всего тогда лучше не мускул использовать, а какие нибудь поисковые движки.

[iljaGolubev]

так что https://laravel.com/docs/10.x/scout,
или https://laravel.com/docs/10.x/queries#full-text-wh... (mysql, postgres),
или своё что-то городить с FTS и DB::raw,
или движок какой-то использовать (Sphinx, Elastic, Solr).
Но без полнотестового не получится имхо. Или ущербно. А при большом количестве пользователей и иинтенсивных изменениях вакансий ещё и медленно.

[KonstantinDyulin]

Дмитрий, Давайте заново.

У меня есть подобная форма.
Когда пользователь заполняет эту форму и отправляет ее, я сохраняю данные из формы в таблицу подписок.
key_words: ключевые слова из формы
email: email из формы
user_id: id пользователя.
В дальнейщем при создании вакансии, мне нужно получить все записи, и перебирать их, в случае если найдено ключевое слово отправлять уведомление на email, в противном случае перебирать дальше.

О заранее заготовленных ключевых словах/токенах мой работодатель ничего не говорил(я спрашивал).
Я понял что вы имеете ввиду, и что вашим способом было бы правильнее и проще. Но что поделать

[iljaGolubev]

Дмитрий,

произвольный текст и стоит превратить в ключевые слова

Есть алгоритм?

[KonstantinDyulin]

iljaGolubev, Забыл упомянуть что поля name, description выглядят подобным образом

незнаю добавит ли это сложности при поиске совпадений

[Дмитрий]

KonstantinDyulin,
1. key_words - пишется как keywords. Как бы откройте html документ и посмотрите на тэг meta name="keywords"
2. прекрасно. у вас добавляется 3 таблицы одна называется keywords куда вы вписываете по одному те ключевые слова что пользователь вбил. и вторая таблица user_to_keywords для отношения многие ко многим - пользователи к ключевым словам, третья vacancy_to_keywords для отношения многие ко многим - вакансии к ключевым словам
3. при создании вакансии вы смотрите какие слова из таблицы keywords в ней содержится и добавляете связку.

после чего вы получаете приблизительно тот запрос что я написал.

держать в таблице user строку где через запятую лежат ключевые слова - не надо. нормализация, реляционные бд - вот это все немножко против

[iljaGolubev]

KonstantinDyulin,

key_words: ключевые слова из формы

- `программист`

при создании вакансии: description

- `программирование на всём.`

Вам нужно чтобы они находили друг друга? Тогда полнотекстовой поиск без вариантов.

[Дмитрий]

iljaGolubev, ага. у нас есть список ключевых слов которые навбивали пользователи, мы можем посмотреть встречаются ли они в произвольном тексте и превратить их в связку.

[iljaGolubev]

KonstantinDyulin, какой драйвер базы?

[iljaGolubev]

Дмитрий,
программист != программирование
но вашу идею я понял. рабочая. только пользователи будут недовольны.

[KonstantinDyulin]

iljaGolubev, pgsql

[iljaGolubev]

для Postgres

-- при создании вакансии нужно заполнить индекс
insert into "vacancy_fts" ("vacancy_id", "vector") select v.id, 
        setweight(to_tsvector('russian_ispell',v.name), 'A') || 
        setweight(to_tsvector('russian_ispell',coalesce(v.description,'')), 'B') || 
        setweight(to_tsvector('russian_ispell',coalesce(k.tags,'')), 'D')  -- pseudo
from "vacancies" as "v" left join "keywords" as k   -- on ...
where v.id=:id

перебирая всех пользователе (то не оптимально)

DB::table('vacancy_fts')
           ->whereFullText('vector', 'web developer')
           ->get();

[iljaGolubev]

Оптимальнее - отбирать пользователей с подходящими подписками.
для подписок пользователе создать подобную таблицу с полнотекстовым вектором subscription_fts (user_id, vector)

тогда что-то вроде

SELECT s.user_id FROM subscription_fts as u
   LEFT JOIN vacancy_fts as v  ON s.vector = v.vector
WHERE v.id = :id

покажет всех пользователей.
и в обратную сторону (вакансии для подписки) тоже сработает....
должно=) этот код не настоящий

[KonstantinDyulin]

iljaGolubev, а то что у меня в полях name, description тип поля указан как jsonB и выглядит вот таким образом, это не как не помешает?

[Дмитрий]

Ну если уж решили играть в tsvector
то предлагаю вариант

CREATE TABLE vacancies
(
    id      BIGSERIAL PRIMARY KEY,
    name  TEXT,
    description TEXT,
    search_vector  tsvector GENERATED ALWAYS AS (to_tsvector('russian', name || ' ' || description)) stored
);

CREATE INDEX vacancies_search_vector_idx ON vacancies USING gin(search_vector);

explain (analyze, timing off, summary off) SELECT * FROM vacancies WHERE search_vector @@ to_tsquery('russian', 'программист');

[Дмитрий]

KonstantinDyulin, а зачем вы так храните? ну у вас же PostgreSQL а не какая нибудь MongoDB

[KonstantinDyulin]

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

[iljaGolubev]

KonstantinDyulin,

а то что у меня в полях name, description тип поля указан как jsonB

insert into "vacancy_fts" ("vacancy_id", "vector") select v.id,
setweight(to_tsvector('russian_ispell',v.name), 'A') ||

ибъединяете всё что нужно при простоении индекса.
russian_ispell - это для description->ru.
Логику дальше сами придумывайте в соответсвии с выбранным решением.
Если FTS не сталкивались раньше - берите Scout как в единственном ответе.

[iljaGolubev]

Дмитрий,

search_vector tsvector GENERATED ALWAYS AS

индекс на обновлении вакансий. имхо лучше в отдельной таблице.
"безболезненный" truncate на весь индекс и полная перестройка.

[iljaGolubev]

+

to_tsvector('russian'

что делать с 'en' и 'uz' ?

[KonstantinDyulin]

iljaGolubev, Дмитрий, спасибо вам за то что "подтолкнули" в правильную сторону, ценю вашу помощь.

Answer 1

[JhaoDa]

Взять Scout.