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 (подтверждённая дружба))