Блог

Главная - Вопросы по программированию - SQL-запрос для поиска проверок от друзей

SQL-запрос для поиска проверок от друзей

#sql #postgresql

#sql #postgresql

Вопрос:

У меня есть следующие таблицы в PostgreSQL:

 Categories | Locations   | Checkins     | Users  | Friendships
id         | id          | id           | id     | user_id
name       | category_id | location_id  | gender | friend_id
icon       | name        | user_id      |        |

Теперь я хочу получить следующую информацию о месте проведения

  • Сколько пользователей женского и мужского пола в местоположении
  • Название категории и значок
  • Название местоположения
  • Сколько друзей зарегистрировались в определенном месте (с заданным идентификатором пользователя)

За исключением последнего пункта, я решил его. Но у меня проблемы с подсчетом друзей с заданным идентификатором пользователя. Я попробовал это с помощью этого запроса:

 SELECT distinct locations.id,
 max(locations.name) as name,
 max(locations.location) as location,
 max(categories.name) as cat,
 max(categories.icon) as caticon,
 SUM(CASE WHEN users.gender = 'm' THEN 1 ELSE 0 END) AS male,
 SUM(CASE WHEN users.gender = 'f' THEN 1 ELSE 0 END) AS female,
 SUM(CASE WHEN friendships.user_id = 1 OR friendships.friend_id=1 THEN 1 ELSE 0 END) AS friends
 FROM locations
 INNER JOIN checkins ON checkins.location_id = locations.id 
 INNER JOIN users ON users.id = checkins.user_id 
 INNER JOIN categories ON categories.id = locations.category_id
 LEFT JOIN friendships ON friendships.user_id = users.id OR friendships.friend_id = users.id
 WHERE locations.id=7
 GROUP BY locations.id

Но я получаю неверное число для пользователей женского пола. Есть идеи, что я делаю не так? Я думаю, мне нужно левое соединение для таблицы friendships, потому что, если у пользователя нет друзей (или пользователь не указан), он должен возвращать только 0 для подсчета друзей.

Надеюсь, я ясно выразился, спасибо, tux

Комментарии:

1. FROM Locations Я считаю, что так и должно быть. Также, WHEN friendships.user_id = 1 OR friendships.user_id=1 вероятно, должно быть WHEN friendships.user_id = 1 OR friendships.friend_id = 1 . И у меня есть вопрос: если два пользователя являются друзьями друг друга, сколько записей будет в friendships , 1 или 2? И должны ли 2 записи создавать 1 или 2 дружеских отношения?

2. спасибо за ваш ответ (я это исправил, сейчас слишком раннее утро ;)). Когда два пользователя являются друзьями друг друга, в таблице friendships есть ровно одна запись, пользователь, запросивший дружбу, сохраняется в user_id , а другой в friend_id

Ответ №1:

 SELECT
  L.id,
  L.name,
  c.name AS cat,
  c.icon AS caticon,
  COUNT(CASE u.gender WHEN 'm' THEN 1 END) AS male,
  COUNT(CASE u.gender WHEN 'f' THEN 1 END) AS female,
  COUNT(f.user_id) AS friends
FROM Locations L
  INNER JOIN Categories c ON c.id = L.category_id
  INNER JOIN Checkins ch ON ch.location_id = L.id
  INNER JOIN Users u ON u.id = ch.user_id
  LEFT JOIN Friendships f ON f.user_id = @user_id AND f.friend_id = ch.user_id
                          OR f.user_id = ch.user_id AND f.friend_id = @user_id
WHERE L.id = @location_id
GROUP BY L.id, L.name, c.name, c.icon

Ответ №2:

Удалите distinct в первой строке. У вас уже есть group by предложение для того же поля. Дайте мне знать, если это поможет.