Подскажите пожалуйста, можно ли средствами одного только SQL (MySQL или любой другой опенсорсовый) решить задачу пересечения множеств подобного рода: - У пользователя веб-приложения есть профайл, в котором есть поле, куда он может ввести произвольные теги через запятую, - Есть кнопка, при нажатии на которую, пользователь получает список других пользователей, отранжированный по числу точных совпадений в этом множестве тегов (например, у него множество: [вишня, банан, яблоко, курица, весло], а у кого-то: [танк, газета, яблоко, весло, шило] - 2 совпадения - яблоко и шило). 1. В каком формате лучше хранить это в базе? Как именно? 2. Как потом одним запросом получить этот ранжированный список? 3. В общем, как это сделать лучше и правильнее всего? Знающие люди предложили решить средствами NoSQL (Redis) но может как-то можно сделать обычными средствами? Спасибо.
Для начала перестать думать в терминах кнопок. РСУБД оперируют множествами. Когда ты объединяешь таблицы это и есть операции над множествами. Соответственно значения через запятую тебе надо сохранять как отдельные записи. --- Добавлено --- http://den.girnyk.com/programming/sql/vizualnoe-predstavlenie-joinov-v-sql/ --- Добавлено --- http://www.sql-tutorial.ru/ru/book_set_theoretic_operations_and_select_statement.html --- Добавлено --- Итого 1 - как записи 2 - оператор select этим занимается. 3. "В общем, как это сделать лучше и правильнее всего?" — не тянет на отдельный пункт, если честно тут нет вариантов.
Но я же не могу под каждый профайл по таблице завести. Или что предлагается? --- Добавлено --- о, ссылки добавились)) спасибо, почитаю)
на самом деле таблицы (т.е. множества) часто объединяют сами с собой. только условие там будет не тупо name=name, а либо разные поля в условии, либо оба экземпляра таблицы предварительно отфильтрованы как-то и оформлены в подзапрос. типа Код (SQL): SELECT x.`word` AS `omonim` FROM (SELECT `word` FROM `words` WHERE `kind`='animal') AS x JOIN (SELECT `word` FROM `words` WHERE `kind`='stuff') AS y ON x.`word` = y.`word`
А что мешает делать массив из этих значений? Перевести в строку функцией serialize, записать в бд. Получить строку из бд перевести в массив функцией unserialize. Сравнить массивы, получить результат.
Признаться честно, мне пока что ничего не понятно Я опишу условие более подробно, насколько смогу: 1. Профайл пользователя - это, по сути, класс, например (псевдокодом)))): Class Profile { id: Integer, name: String, tags: Array } 2. Соответственно, у каждого пользователя - свой объект этого класса. 3. В каждом объекте есть сущность tags - это простой массив, список строк: ['груша', 'вишня', 'сапоги', 'дерево'] 4. Пользователей тоже много, и вот у нас есть id: 1, name: 'Вася', tags: ['груша', 'вишня', 'сапоги', 'дерево'] id: 2, name: 'Петя', tags: ['груша', 'вишня', 'сапоги', 'дерево','свинья', 'шапка', 'колесо', 'медведь'] id: 3, name: 'Боря', tags: ['груша', 'вишня'] id: 4, name: 'Жора', tags: ['шапка', 'колесо', 'медведь'] Теперь Вася хочет получить ранжированный список профайлов с наиболее похожими тегами Для него этого будет: Петя - 4 совпадения Боря - 2 совпадения Жора - 0 совпадений Как это лучше всего реализовать с помощью SQL? Сколько таблиц лучше сделать? Можно ли одним запросом все это получить? Ну вот какая-то такая история У меня просто пока что мозги разбегаются в разные стороны, когда я пытаюсь это осмыслить )) --- Добавлено --- Да можно и в поле типа JSON запулить, не вопрос)) Вопрос в том, что если потребуется получить такую выборку для базы с миллионом профайлов (вполне реальная для популярного ресурса цифра), то для начала придется в цикле из миллиона итераций сделать сравнения этих массивов, но прежде, их нужно будет еще получить, и там тоже будут вопросы, как их лучше получать, сразу (миллион записей - это ж сколько оперативы понадобится?) или частями, и если частями - то по сколько именно. И еще нужно будет как-то хранить промежуточные результаты, чтобы сервер не подавился)) Много вопросов возникает технического свойства. Но как запасной вариант - да, держу в уме и это. Мне еще и Redis порекомендовали - там есть поля типа Set и встроенная функция по пересечению таких полей, то есть если придется делать средствами PHP, то лучше тогда использовать Redis. Но я подумал, что может можно как-то через SQL это решить.
Мне нужен список пользователей отранжированный по количеству одних и тех же тегов с текущим пользователем. И я до сих пор не понимаю как это сделать средствами SQL.
@artoodetoo, для начала ему надо определится со структурой БД, а уже потом завозить песок в песочницу. @Roman __construct, начни с трёх таблиц 1 пользователи 2 теги 3 связь пользователи - теги
Ладно, буду прокрастинацию использовать с пользой. Вот песочница: https://www.db-fiddle.com/f/sr7C65CUDSmY8BFKpFZX6u/0 Все могут поиграть с запросами.
Пададам! https://www.db-fiddle.com/f/ntAs6nL7xkQAgXofUummyD/0 Дано: #1 Вася: сапоги,вишня,дерево,груша #2 Петя: колесо,свинья,сапоги,шапка,вишня,дерево,сапоги,груша #3 Боря: груша,вишня #4 Жора: колесо,свинья,шапка Найти с кем пересекается по тегам заданный пользователь, например Петя. Сортировать список по убыванию числа совпадающих тегов. Код (SQL): SELECT u.id, u.username, GROUP_CONCAT(t.tagname) AS user_tags, COUNT(tu2.tag_id) AS similarity FROM users AS u JOIN tag_user AS tu ON tu.user_id=u.id JOIN tags AS t ON t.id=tu.tag_id -- JOIN tag_user AS tu2 ON tu.tag_id=tu2.tag_id AND tu.user_id<>tu2.user_id -- WHERE tu2.user_id=2 GROUP BY u.id ORDER BY similarity DESC --- Добавлено --- Немного поясню: * таблица `tag_user` это связка для организации отношения многие-ко-многим * всё до первого "--" это всё ради вывода красивого списка "пользователь,тег,тег,тег". слава макаронам, в MySQL есть group_concat! * после "--" таблица связка появляется второй раз, это и есть самая мякотка: tu.tag_id = tu2.tag_id AND tu.user_id <> tu2.user_id означает "тег тот же, а пользователь другой" COUNT(tu2.tag_id) AS similarity означает "число совпадающих тегов с текущим пользователем" * наконец после второго "--" указываем с каким пользоватем сравниваем: #2 Петя группируем, сортируем
postgresql с одной таблицей: Код (Text): create table users( id serial primary key, name varchar(30), tags varchar(30)[] ); insert into users(name, tags) values('tom', '{"sql", "postgres", "mysql"}'), ('jane', '{"sqlite", "postgres", "mssql"}'), ('john','{"sql", "mysql", "sqlite"}'); Код (Text): select u.id, u.name, (select count(*) from (select unnest(tags)as b from users where users.id=u.id )t where b in (select unnest(tags) from users where users.id=1) )c from users as u where u.id!=1 order by c desc;
Братья, гран мерси!! )) Вы лучшие! Непременно протестирую оба варианта как доберусь до этого этапа (пока что решаю вопросы с авторизацией) pet-projecta --- Добавлено --- шикарный сервис кстати! не знал что такие есть ))))) еще раз спасибо!