![]() We deployed this app using Vercel's serverless infrastructure, which allowed us to easily and quickly deploy and scale our app with minimal server management. With SQrL’s help, we ended up with the following query: WITH table_match AS ( SELECT m.title, m.movieId, m.vector FROM user_choice t INNER JOIN movie_with_tags_with_vectors m ON m.title = t.title WHERE userid = %s ), movie_pairs AS ( SELECT m1.movieId AS movieId1, m1.title AS title1, m2.movieId AS movieId2, m2.title AS title2, DOT_PRODUCT(m1.vector, m2.vector) AS similarity FROM table_match m1 CROSS JOIN movie_with_tags_with_vectors m2 WHERE m1.movieId != m2.movieId AND NOT EXISTS ( SELECT 1 FROM user_choice uc WHERE uc.userid = %s AND uc.title = m2.title ) ), movie_match AS ( SELECT movieId1, title1, movieId2, title2, similarity FROM movie_pairs ORDER BY similarity DESC ), distinct_count AS ( SELECT DISTINCT movieId2, title2 AS Title, ROUND ( AVG (similarity), 4 ) AS Rating_Match FROM movie_match GROUP BY movieId2, title2 ORDER BY Rating_Match DESC ), average_ratings AS ( SELECT movieId, AVG (rating) AS Avg_Rating FROM ratings GROUP BY movieId ) SELECT dc.Title, dc.Rating_Match, ROUND (ar.Avg_Rating, 4 ) AS Avg_Rating FROM distinct_count dc JOIN average_ratings ar ON dc.movieId2 = ar.movieId ORDER BY dc.Rating_Match DESC LIMIT 5 The structure of the query is as follows:įinally, we added additional analytics by finding the average user rating for the selected movie with an aggregate and a join with the ratings table. SQrL also provided us with specialized code snippets that were optimized for our Flask app and Python environment, ensuring seamless integration and optimal performance. To bring our idea to life, we consulted with SQrL and leveraged our native vector functions, like dot_product for cosine similarity, as well as filters and Common Table Expressions (CTE) to create the core algorithm. We knew we needed to calculate the cosine similarity between the user-selected movies and all movies in our database, average the match scores for each movie and return the top five matches. fetchall() Semantic Search with AnalyticsĪs hobbyist developers, we had a general understanding of how to implement the semantic search algorithm. ![]() execute( query, ( prefix, prefix )) rows = cursor. ![]() route ( '/autocomplete', methods =) def search_movies (): try : data = request.get_json() prefix = data "*" conn = init_connection () query = ( "WITH queryouter AS (" "SELECT DISTINCT(title), movieId, MATCH(title) AGAINST ( %s ) as relevance " "FROM movies_with_full_text " "WHERE MATCH(title) AGAINST ( %s ) " "ORDER BY relevance DESC " "LIMIT 3" ")" "SELECT title, movieId FROM queryouter " ) cursor = conn. Our native support for full-text indices enables us to quickly provide the movies with the highest relevance scores to the frontend: app. This route executes an SQL query that matches the title of all movies in our database with the input from the frontend using %s. To enhance our full-text search experience, we created a route called /autocomplete that is triggered from the frontend every time a user enters a new keystroke in the search box. Semantic search query that considers user input to find five movies with the highest match_score.
0 Comments
Leave a Reply. |