Create search options for movies seen

The Feature

I'd like to be able to search within movies that I've seen, passing it optional arguments and returning a tiled list of movies (like the existing behavior for other searches) or perhaps a table view in chronological order by date_watched.

The Problem

I want this feature because I'd been wracking my brain to remember a Christmas Hallmark/Lifetime movie that I watched in my hotel room in Columbus, OH, very specifically between 2018-12-05 and 2018-12-15 and I knew it had something to do with Louisiana because it made me feel happy, having moved away from New Orleans only 6 months prior. Four years later, my best guesses + googling efforts to find this movie led us to watching this atrocity

which was terrible and not in the good kind of way like the absurd A Very Nutty Christmas.

After a query in the production database (always the best way to solve your UX problems, no? 😆 ), I got my answer.

I was able to confirm that this is indeed the movie I had been trying to recall:

Huzzah! Joy to the world, I've located a Christmas RomCom that I will probably watch this weekend.

Implementation Ideas

Our existing Advanced Search is designed to find movies based on details about the movie and related actors. The search I am proposing is also interested in details about the current user's screenings.

I am not sure if we want to add complexity to this search page or if we want to make a new page entirely. And honestly, having this table view from the production database was very helpful as I played with the query and narrowed my search:

So I'm not really sure if a tiled view will be as useful if the user really does want to see those details.

I think it will require a little tinkering (technical discovery 😉 ) before we decide which direction to go. But this query handles all of my concerns for this new feature:

SELECT m.title, r.value AS "rating", s.date_watched, s.location_watched, s.notes
FROM movies m
LEFT JOIN screenings s on s.movie_id = m.id
LEFT JOIN users u on s.user_id = u.id
LEFT JOIN ratings r on r.user_id = u.id AND r.movie_id = m.id
WHERE u.id = '<current user id>'
  AND s.date_watched BETWEEN '2018-12-05' AND '2018-12-15'
  --AND s.location_watched ILIKE '%%'
  --AND m.title ILIKE '%%'
  AND m.overview ILIKE '%louisiana%'
ORDER BY s.date_watched
;

mikevallano / tmdb-moviequeue