vladbakumenko/java-filmorate

"Filmorate" - movie recommendation service project

java-filmorate

Template repository for Filmorate project.

list of features for review:

• add-director
• add-common-films
• add-feed
• add-recommendations
• add-most-populars
• add-search
• add-reviews
• add-remove-endpoint

link for image of db

examples of basic database queries:

• get all films:
  SELECT *
  FROM films

• get film by id (parametr: int id):
  SELECT *
  FROM films AS f
  WHERE f.id = (entered id)

• get popular film (parametr: int count):
  SELECT *
  FROM films AS f
  LEFT OUTER JOIN likes_by_users AS l ON f.id = l.id_film
  GROUP BY f.id
  ORDER BY COUNT(l.id_film) DESC
  LIMIT count

• get all users:
  SELECT *
  FROM users

• get user by id (parametr: int id):
  SELECT *
  FROM users AS u
  WHERE u.id = (entered id)

• get friends by id (parametr: int id):
  SELECT *
  FROM users AS u
  WHERE u.id IN (SELECT id_friend
  FROM friends AS f
  WHERE f.id_user = (entered id)
  AND f.id_status = 2 (подтверждённая дружба))

• get common friends by id user and id other user (parameters: int id, int otherId)
  SELECT *
  FROM users AS u
  WHERE u.id IN (SELECT id_friend
  FROM friends AS f
  WHERE f.id_user = (entered id)
  AND f.id_status = 2 (подтверждённая дружба))
  AND u.id IN (SELECT id_friend
  FROM friends AS f
  WHERE f.id_user = (entered otherId)
  AND f.id_status = 2 (подтверждённая дружба))