Как хранить категории для юзера в postgres?

Question

[WAYNEDEV]

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

Не могу сообразить лучший вариант и в гугле нет ответов.

Answer 1

[galaxy]

Стандартное отношение many-to-many через таблицу связи

users
__________
id
name
...

categories
__________
id
name
...

user_categories
__________
user_id
category_id

В принципе, если категории просто текстовые теги, можно хранить в поле таблицы users в виде JSON, например.

Comments

[WAYNEDEV]

А если в json, то как потом делать поиск?

[galaxy]

WAYNEDEV, например, GIN индекс и оператор ?

'["a", "b", "c"]'::jsonb ? 'b' → t
https://www.postgresql.org/docs/current/functions-...

Но вообще это плохой вариант, анти-реляционный.
Например, у вас не будет общего списка категорий.

[WAYNEDEV]

galaxy, а при первом способе при добавлении 100 категорий придётся делать 100 запросов на создание? Или есть вариант проще?

[galaxy]

WAYNEDEV, 100 строк вставлять придется, да. Запроса хватит одного, например:

INSERT INTO user_categories VALUES
(1, 17),
(1, 22),
(1, 144)
....

[WAYNEDEV]

galaxy, а не подскажите, какой запрос должен быть на изменение категорий у юзера? Ведь он может их удалять и тп
Буду вам очень благодарен!

[galaxy]

WAYNEDEV, через удаление и добавление

DELETE FROM user_categories  WHERE user_id = 1 AND category_id IN (12, 20, 88);
INSERT INTO user_categories VALUES (1, 17), (1, 22);

 -- удалить категории 12, 20, 88, добавить: 17, 22

[WAYNEDEV]

И напоследок, буду прям вам максимально благодарен!
Как сделать поиск по юзерам, опираясь на таблицу user_categories?

[galaxy]

WAYNEDEV, в смысле, по заданным категориям найти юзеров?

[WAYNEDEV]

Да, какой запрос должен быть?

[galaxy]

WAYNEDEV,
найти юзеров, имеющих хотя бы одну из категорий 1, 2, 3 (в т.ч. подходит просто для поиска по одной категории):

SELECT * FROM users WHERE id IN (
  SELECT user_id FROM user_categories WHERE category_id IN (1, 2, 3)
)

найти юзеров, имеющих все категории 1, 2, 3:

SELECT u.* FROM users u JOIN (
  SELECT user_id, array_agg(category_id) cats FROM user_categories WHERE category_id IN (1, 2, 3) GROUP BY 1
) uc ON (u.id = uc.user_id)
 WHERE cats @> ARRAY[1, 2, 3]

-- также при UNIQUE ограничении на (user_id, category_id) подойдет
SELECT u.* FROM users u JOIN (
  SELECT user_id, COUNT(*) ncats FROM user_categories WHERE category_id IN (1, 2, 3) GROUP BY 1
) uc ON (u.id = uc.user_id)
 WHERE ncats = 3

[WAYNEDEV]

Спасибо вам большое! Вы лучший

[WAYNEDEV]

А как сделать доп. Фильтры? Мол по категориям и имени и тп?

[galaxy]

WAYNEDEV,

А как сделать доп. Фильтры? Мол по категориям и имени и тп?

в WHERE добавляйте
... WHERE ncats = 3 AND u.name = 'john'