queries.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from py2neo import *\n",
    "import pandas as pd\n",
    "import time\n",
    "from tqdm import tqdm\n",
    "import getpass\n",
    "import importlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "username = input(\"Enter username: \")\n",
    "password = getpass.getpass('Enter password: ')\n",
    "port = input(\"Enter Neo4j listening port: \")\n",
    "graph = Graph(f\"bolt://localhost:{port}\", auth=(username, password))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Query 1__: Get __top $\\mathbf{k}$ Genres__, given a __User__."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Execution examples ($k=5$)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "user_uuid_associations = pd.read_pickle(\"data/users_uuids.pkl\")\n",
    "categories_uuid_associations = pd.read_pickle(\"data/categories_uuids.pkl\")\n",
    "genre_uuid_associations = pd.read_pickle(\"data/genre_uuids.pkl\")\n",
    "movies_uuid_associations = pd.read_pickle(\"data/movies_uuids.pkl\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "K=5"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Method 1: average score__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>GENRE</th><th>AVG_SCORE</th></tr><tr><td style=\"text-align:left\">Thriller</td><td style=\"text-align:right\">4.2</td></tr><tr><td style=\"text-align:left\">Action</td><td style=\"text-align:right\">4.0</td></tr><tr><td style=\"text-align:left\">Animation</td><td style=\"text-align:right\">4.0</td></tr><tr><td style=\"text-align:left\">Romance</td><td style=\"text-align:right\">3.9444444444444446</td></tr><tr><td style=\"text-align:left\">Crime</td><td style=\"text-align:right\">3.875</td></tr></table>"
      ],
      "text/plain": [
       " GENRE     |          AVG_SCORE \n",
       "-----------|--------------------\n",
       " Thriller  |                4.2 \n",
       " Action    |                4.0 \n",
       " Animation |                4.0 \n",
       " Romance   | 3.9444444444444446 \n",
       " Crime     |              3.875 "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_id = user_uuid_associations[1]\n",
    "K = 5\n",
    "\n",
    "graph.run(\n",
    "    (\n",
    "        \"MATCH(u:User)-[r:RATES]->(m:Movie)-[h:HAS_GENRE]->(g:Genre)\"\n",
    "        f\"WHERE u.id = '{user_id}'\"\n",
    "        \"RETURN g.name as GENRE, AVG(r.score) as AVG_SCORE \"\n",
    "        \"ORDER BY AVG_SCORE DESC \"\n",
    "        f\"LIMIT {K}\"\n",
    "    )\n",
    ").to_table()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Method 2: max score * count score__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>GENRE</th><th>SCORE</th></tr><tr><td style=\"text-align:left\">Drama</td><td style=\"text-align:right\">265</td></tr><tr><td style=\"text-align:left\">Comedy</td><td style=\"text-align:right\">115</td></tr><tr><td style=\"text-align:left\">Romance</td><td style=\"text-align:right\">90</td></tr><tr><td style=\"text-align:left\">Adventure</td><td style=\"text-align:right\">55</td></tr><tr><td style=\"text-align:left\">Crime</td><td style=\"text-align:right\">40</td></tr></table>"
      ],
      "text/plain": [
       " GENRE     | SCORE \n",
       "-----------|-------\n",
       " Drama     |   265 \n",
       " Comedy    |   115 \n",
       " Romance   |    90 \n",
       " Adventure |    55 \n",
       " Crime     |    40 "
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph.run(\n",
    "    (\n",
    "        \"MATCH(u:User)-[r:RATES]->(m:Movie)-[h:HAS_GENRE]->(g:Genre) \"\n",
    "        f\"WHERE u.id = '{user_id}' \"\n",
    "        \"RETURN g.name as GENRE, MAX(r.score)*COUNT(r.score) as SCORE \"\n",
    "        \"ORDER BY SCORE DESC \"\n",
    "        f\"LIMIT {K}\"\n",
    "    )\n",
    ").to_table()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Computational time (Method 1)__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_top_k_genres(user_id, K):\n",
    "    return graph.run(\n",
    "            (\n",
    "                \"MATCH(u:User)-[r:RATES]->(m:Movie)-[h:HAS_GENRE]->(g:Genre)\"\n",
    "                f\"WHERE u.id = '{user_id}'\"\n",
    "                \"RETURN g.name as GENRE, AVG(r.score) as AVG_SCORE \"\n",
    "                \"ORDER BY AVG_SCORE DESC \"\n",
    "                f\"LIMIT {K}\"\n",
    "            )\n",
    "        ).to_table()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 162541/162541 [17:48<00:00, 152.10it/s] "
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.00651306757677376\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "total_time = 0\n",
    "\n",
    "for user_id in tqdm(user_uuid_associations.values()):\n",
    "    start_time = time.time()\n",
    "    get_top_k_genres(user_id, K)\n",
    "    total_time += (time.time() - start_time)\n",
    "    \n",
    "print(total_time/len(user_uuid_associations.values()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Query 2__: Get __top $\\mathbf{k}$ Categories__, given a __User__ "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Method 1: average relevance * average score__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>CATEGORY</th><th>SCORE</th></tr><tr><td style=\"text-align:left\">jay and silent bob</td><td style=\"text-align:right\">4.473</td></tr><tr><td style=\"text-align:left\">math</td><td style=\"text-align:right\">4.399875</td></tr><tr><td style=\"text-align:left\">mathematics</td><td style=\"text-align:right\">4.33575</td></tr><tr><td style=\"text-align:left\">tolkien</td><td style=\"text-align:right\">3.993859375</td></tr><tr><td style=\"text-align:left\">short-term memory loss</td><td style=\"text-align:right\">3.992</td></tr></table>"
      ],
      "text/plain": [
       " CATEGORY               |       SCORE \n",
       "------------------------|-------------\n",
       " jay and silent bob     |       4.473 \n",
       " math                   |    4.399875 \n",
       " mathematics            |     4.33575 \n",
       " tolkien                | 3.993859375 \n",
       " short-term memory loss |       3.992 "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph.run(\n",
    "    (\n",
    "        \"MATCH(u:User)-[r:RATES]->(m:Movie)-[h:HAS_CATEGORY]->(c:Category) \"\n",
    "        f\"WHERE u.id = '{user_id}' \"\n",
    "        \"RETURN c.name as CATEGORY, AVG(h.relevance)*AVG(r.score) as SCORE \"\n",
    "        \"ORDER BY SCORE DESC \"\n",
    "        f\"LIMIT {K}\"\n",
    "    )\n",
    ").to_table()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Method 2: average relevance* rating count* max score__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>CATEGORY</th><th>SCORE</th></tr><tr><td style=\"text-align:left\">original</td><td style=\"text-align:right\">250.45125</td></tr><tr><td style=\"text-align:left\">criterion</td><td style=\"text-align:right\">211.21375</td></tr><tr><td style=\"text-align:left\">storytelling</td><td style=\"text-align:right\">209.69875</td></tr><tr><td style=\"text-align:left\">melancholic</td><td style=\"text-align:right\">209.55874999999997</td></tr><tr><td style=\"text-align:left\">reflective</td><td style=\"text-align:right\">199.23749999999998</td></tr></table>"
      ],
      "text/plain": [
       " CATEGORY     |              SCORE \n",
       "--------------|--------------------\n",
       " original     |          250.45125 \n",
       " criterion    |          211.21375 \n",
       " storytelling |          209.69875 \n",
       " melancholic  | 209.55874999999997 \n",
       " reflective   | 199.23749999999998 "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph.run(\n",
    "    (\n",
    "        \"MATCH(u:User)-[r:RATES]->(m:Movie)-[h:HAS_CATEGORY]->(c:Category) \"\n",
    "        f\"WHERE u.id = '{user_id}' \"\n",
    "        \"RETURN c.name as CATEGORY, AVG(h.relevance)*COUNT(r.score)*MAX(r.score) as SCORE \"\n",
    "        \"ORDER BY SCORE DESC \"\n",
    "        f\"LIMIT {K}\"\n",
    "    )\n",
    ").to_table()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Computation time (Method 1)__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_top_k_categories(user_id, K):\n",
    "    return graph.run(\n",
    "        (\n",
    "            \"MATCH(u:User)-[r:RATES]->(m:Movie)-[h:HAS_CATEGORY]-(c:Category) \"\n",
    "            f\"WHERE u.id = '{user_id}' \"\n",
    "            \"RETURN c.name as CATEGORY, AVG(h.relevance)*COUNT(r.score)*MAX(r.score) as SCORE \"\n",
    "            \"ORDER BY SCORE DESC \"\n",
    "            f\"LIMIT {K}\"\n",
    "        )\n",
    "    ).to_table()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 162541/162541 [1:23:21<00:00, 32.50it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.030554692825194497\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "total_time = 0\n",
    "\n",
    "for user_id in tqdm(user_uuid_associations.values()):\n",
    "    start_time = time.time()\n",
    "    get_top_k_categories(user_id, K)\n",
    "    total_time += (time.time() - start_time)\n",
    "    \n",
    "print(total_time/len(user_uuid_associations.values()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Query 3__ :Get __top $\\mathbf{k}$ Movies__, given a __Genre__."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Method 1: average score__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>MOVIE</th><th>SCORE</th></tr><tr><td style=\"text-align:left\">Days of Power</td><td style=\"text-align:right\">5.0</td></tr><tr><td style=\"text-align:left\">White Rush</td><td style=\"text-align:right\">5.0</td></tr><tr><td style=\"text-align:left\">Pit Fighter</td><td style=\"text-align:right\">5.0</td></tr><tr><td style=\"text-align:left\">Throwback</td><td style=\"text-align:right\">5.0</td></tr><tr><td style=\"text-align:left\">Santosh Subramaniam</td><td style=\"text-align:right\">5.0</td></tr><tr><td style=\"text-align:left\">Kabir Singh</td><td style=\"text-align:right\">5.0</td></tr><tr><td style=\"text-align:left\">Junga</td><td style=\"text-align:right\">5.0</td></tr><tr><td style=\"text-align:left\">Awe!</td><td style=\"text-align:right\">5.0</td></tr><tr><td style=\"text-align:left\">Kaithi</td><td style=\"text-align:right\">5.0</td></tr><tr><td style=\"text-align:left\">Dangerous Flowers</td><td style=\"text-align:right\">5.0</td></tr></table>"
      ],
      "text/plain": [
       " MOVIE               | SCORE \n",
       "---------------------|-------\n",
       " Days of Power       |   5.0 \n",
       " White Rush          |   5.0 \n",
       " Pit Fighter         |   5.0 \n",
       " Throwback           |   5.0 \n",
       " Santosh Subramaniam |   5.0 \n",
       " Kabir Singh         |   5.0 \n",
       " Junga               |   5.0 \n",
       " Awe!                |   5.0 \n",
       " Kaithi              |   5.0 \n",
       " Dangerous Flowers   |   5.0 "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "genre_id = genre_uuid_associations['Action']\n",
    "\n",
    "graph.run(\n",
    "    (\n",
    "        f\"MATCH(g:Genre{{id: '{genre_id}'}})<-[h:HAS_GENRE]-(m:Movie)<-[r:RATES]-(u:User)\"\n",
    "        \"RETURN m.title as MOVIE, avg(r.score) as SCORE \"\n",
    "        \"ORDER BY SCORE DESC \"\n",
    "        \"LIMIT 10\"\n",
    "    )\n",
    ").to_table()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Method 2: count * max score__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>MOVIE</th><th>SCORE</th></tr><tr><td style=\"text-align:left\">Matrix, The</td><td style=\"text-align:right\">363370</td></tr><tr><td style=\"text-align:left\">Star Wars: Episode IV - A New Hope</td><td style=\"text-align:right\">343585</td></tr><tr><td style=\"text-align:left\">Jurassic Park</td><td style=\"text-align:right\">320720</td></tr><tr><td style=\"text-align:left\">Braveheart</td><td style=\"text-align:right\">295920</td></tr><tr><td style=\"text-align:left\">Fight Club</td><td style=\"text-align:right\">293865</td></tr><tr><td style=\"text-align:left\">Terminator 2: Judgment Day</td><td style=\"text-align:right\">286895</td></tr><tr><td style=\"text-align:left\">Star Wars: Episode V - The Empire Strikes Back</td><td style=\"text-align:right\">286805</td></tr><tr><td style=\"text-align:left\">Star Wars: Episode VI - Return of the Jedi</td><td style=\"text-align:right\">274585</td></tr><tr><td style=\"text-align:left\">Raiders of the Lost Ark (Indiana Jones and the Raiders of the Lost Ark)</td><td style=\"text-align:right\">273375</td></tr><tr><td style=\"text-align:left\">Lord of the Rings: The Return of the King, The</td><td style=\"text-align:right\">253985</td></tr></table>"
      ],
      "text/plain": [
       " MOVIE                                                                   |  SCORE \n",
       "-------------------------------------------------------------------------|--------\n",
       " Matrix, The                                                             | 363370 \n",
       " Star Wars: Episode IV - A New Hope                                      | 343585 \n",
       " Jurassic Park                                                           | 320720 \n",
       " Braveheart                                                              | 295920 \n",
       " Fight Club                                                              | 293865 \n",
       " Terminator 2: Judgment Day                                              | 286895 \n",
       " Star Wars: Episode V - The Empire Strikes Back                          | 286805 \n",
       " Star Wars: Episode VI - Return of the Jedi                              | 274585 \n",
       " Raiders of the Lost Ark (Indiana Jones and the Raiders of the Lost Ark) | 273375 \n",
       " Lord of the Rings: The Return of the King, The                          | 253985 "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph.run(\n",
    "    (\n",
    "        f\"MATCH(g:Genre{{id: '{genre_id}'}})<-[h:HAS_GENRE]-(m:Movie)<-[r:RATES]-(u:User)\"\n",
    "        \"RETURN m.title as MOVIE, count(r.score)*max(r.score) as SCORE \"\n",
    "        \"ORDER BY SCORE DESC \"\n",
    "        \"LIMIT 10\"\n",
    "    )\n",
    ").to_table()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Computation time (Method 1)__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_top_k_movies(genre_id, K):\n",
    "    return graph.run(\n",
    "        (\n",
    "            f\"MATCH(g:Genre{{id: '{genre_id}'}})<-[h:HAS_GENRE]-(m:Movie)<-[r:RATES]-(u:User)\"\n",
    "            \"RETURN m.title as MOVIE, count(r.score)*max(r.score) as SCORE \"\n",
    "            \"ORDER BY SCORE DESC \"\n",
    "            f\"LIMIT {K}\"\n",
    "        )\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 19/19 [06:01<00:00, 19.00s/it]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "19.00047913350557\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "total_time = 0\n",
    "\n",
    "for genre_id in tqdm(genre_uuid_associations.values()):\n",
    "    start_time = time.time()\n",
    "    get_top_k_movies(genre_id, K)\n",
    "    total_time += (time.time() - start_time)\n",
    "    \n",
    "print(total_time/len(genre_uuid_associations.values()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Query 4__ : Get __top $\\mathbf{k}$ Movies__, given a __Category__."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "category_id = categories_uuid_associations[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>MOVIE</th><th>RELEVANCE</th></tr><tr><td style=\"text-align:left\">Tomorrow Never Dies</td><td style=\"text-align:right\">0.99975</td></tr><tr><td style=\"text-align:left\">Octopussy</td><td style=\"text-align:right\">0.99975</td></tr><tr><td style=\"text-align:left\">You Only Live Twice</td><td style=\"text-align:right\">0.99975</td></tr><tr><td style=\"text-align:left\">Never Say Never Again</td><td style=\"text-align:right\">0.99975</td></tr><tr><td style=\"text-align:left\">View to a Kill, A</td><td style=\"text-align:right\">0.99975</td></tr></table>"
      ],
      "text/plain": [
       " MOVIE                 | RELEVANCE \n",
       "-----------------------|-----------\n",
       " Tomorrow Never Dies   |   0.99975 \n",
       " Octopussy             |   0.99975 \n",
       " You Only Live Twice   |   0.99975 \n",
       " Never Say Never Again |   0.99975 \n",
       " View to a Kill, A     |   0.99975 "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph.run(\n",
    "    (\n",
    "        f\"MATCH(c:Category{{id: '{category_id}'}})<-[h:HAS_CATEGORY]-(m:Movie) \"\n",
    "        \"RETURN m.title as MOVIE, h.relevance as RELEVANCE \"\n",
    "        \"ORDER BY RELEVANCE DESC \"\n",
    "        \"LIMIT 5\"\n",
    "    )\n",
    ").to_table()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>MOVIE</th><th>POP_RELEVANCE</th></tr><tr><td style=\"text-align:left\">Casino Royale</td><td style=\"text-align:right\">3.616791603519913</td></tr><tr><td style=\"text-align:left\">Goldfinger</td><td style=\"text-align:right\">3.6059323084552517</td></tr><tr><td style=\"text-align:left\">Ipcress File, The</td><td style=\"text-align:right\">3.605676671214189</td></tr><tr><td style=\"text-align:left\">From Russia with Love</td><td style=\"text-align:right\">3.5721480889553874</td></tr><tr><td style=\"text-align:left\">Dr. No</td><td style=\"text-align:right\">3.5470968750000127</td></tr></table>"
      ],
      "text/plain": [
       " MOVIE                 |      POP_RELEVANCE \n",
       "-----------------------|--------------------\n",
       " Casino Royale         |  3.616791603519913 \n",
       " Goldfinger            | 3.6059323084552517 \n",
       " Ipcress File, The     |  3.605676671214189 \n",
       " From Russia with Love | 3.5721480889553874 \n",
       " Dr. No                | 3.5470968750000127 "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph.run(\n",
    "    (\n",
    "        f\"MATCH(c:Category{{id: '{category_id}'}})<-[h:HAS_CATEGORY]-(m:Movie)<-[r:RATES]-(u:User)\"\n",
    "        \"RETURN m.title as MOVIE, h.relevance*avg(r.score) as POP_RELEVANCE \"\n",
    "        \"ORDER BY POP_RELEVANCE DESC \"\n",
    "        \"LIMIT 5\"\n",
    "    )\n",
    ").to_table()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_top_k_movies(category_id, K):\n",
    "    return graph.run(\n",
    "    (\n",
    "        f\"MATCH(c:Category{{id: '{category_id}'}})<-[h:HAS_CATEGORY]-(m:Movie)<-[r:RATES]-(u:User)\"\n",
    "        \"RETURN m.title as MOVIE, h.relevance*avg(r.score) as POP_RELEVANCE \"\n",
    "        \"ORDER BY POP_RELEVANCE DESC \"\n",
    "        f\"LIMIT {K}\"\n",
    "    )\n",
    ").to_table()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 1128/1128 [3:20:35<00:00, 10.67s/it] "
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "10.668235402157967\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "total_time = 0\n",
    "\n",
    "for category_id in tqdm(categories_uuid_associations.values()):\n",
    "    start_time = time.time()\n",
    "    get_top_k_movies(category_id, K)\n",
    "    total_time += (time.time() - start_time)\n",
    "    \n",
    "print(total_time/len(categories_uuid_associations.values()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### __COLLABORATIVE FILTERING__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<module 'gds_recommendation' from '/home/alessia/Documenti/University/New Generation Data Models and DBMSs/gds_recommendation.py'>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import gds_recommendation\n",
    "importlib.reload(gds_recommendation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# creating a GDS graph projection on which operate for COLLABORATIVE FILTERING\n",
    "gds_recommendation.create_gds_projection(\n",
    "    neo4j_graph=graph,\n",
    "    gds_name=\"cf_projection\",\n",
    "    node_names=[\"User\", \"Movie\"],\n",
    "    orientation= \"UNDIRECTED\",\n",
    "    rel_names = [\"RATES\"],\n",
    "    rel_property= \"score\"\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# projecting FastRP embeddings on the GDS graph\n",
    "gds_recommendation.mutate_fastRP_embedding(\n",
    "    neo4j_graph= graph,\n",
    "    gds_name= \"cf_projection\",\n",
    "    node_names= [\"User\", \"Movie\"],\n",
    "    rel_names= [\"RATES\"],\n",
    "    rel_property= \"score\",\n",
    "    embedding_name= \"cf_embedding\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# writing the embeddings in the neo4j dataset\n",
    "gds_recommendation.write_fastRP_embedding(neo4j_graph=graph, gds_name=\"cf_projection\", nodes=[\"User\", \"Movie\"], embedding_name= \"cf_embedding\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__USER-BASED__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# projecting a second GDS graph to perform KNN on\n",
    "gds_recommendation.create_gds_projection(graph, \"knn_user_cf\", node_names=[\"User\"], node_property= \"cf_embedding\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#writing SIMILARITY KNN relationships back to the neo4j dataset\n",
    "gds_recommendation.write_knn_sim_relationships(neo4j_graph=graph, knn_gds_name=\"knn_user_cf\", node_name= \"User\", rel_name= \"SIMILAR\", embedding_name=\"cf_embedding\", property=\"score\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Query 5__: Get __Similar Users__, given a __User__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>ID</th><th>SCORE</th></tr><tr><td style=\"text-align:left\">6d5cc357-ffdb-43bf-b168-735ef4f85971</td><td style=\"text-align:right\">0.9723402261734009</td></tr><tr><td style=\"text-align:left\">527d00bd-08a6-4702-90c2-2b516318581a</td><td style=\"text-align:right\">0.972097635269165</td></tr><tr><td style=\"text-align:left\">933698e2-f31a-4003-9c0e-abb8bea9561d</td><td style=\"text-align:right\">0.9717543125152588</td></tr><tr><td style=\"text-align:left\">1d08f028-3e2d-47d0-a08e-5be304133ad9</td><td style=\"text-align:right\">0.9714540243148804</td></tr><tr><td style=\"text-align:left\">3ee64c64-0060-4679-b56e-d5bf650187ca</td><td style=\"text-align:right\">0.9714488983154297</td></tr></table>"
      ],
      "text/plain": [
       " ID                                   |              SCORE \n",
       "--------------------------------------|--------------------\n",
       " 6d5cc357-ffdb-43bf-b168-735ef4f85971 | 0.9723402261734009 \n",
       " 527d00bd-08a6-4702-90c2-2b516318581a |  0.972097635269165 \n",
       " 933698e2-f31a-4003-9c0e-abb8bea9561d | 0.9717543125152588 \n",
       " 1d08f028-3e2d-47d0-a08e-5be304133ad9 | 0.9714540243148804 \n",
       " 3ee64c64-0060-4679-b56e-d5bf650187ca | 0.9714488983154297 "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_id = user_uuid_associations[1]\n",
    "graph.run(\n",
    "            (\n",
    "            f\"MATCH (u1:User{{id: '{user_id}'}})-[s:SIMILAR]->(u2:User)\"\n",
    "            \" RETURN u2.id AS ID, s.score AS SCORE\"\n",
    "            \" ORDER BY SCORE DESC\"\n",
    "            )\n",
    "        ).to_table()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Computation time__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_similar_users(user_id):\n",
    "    return graph.run(\n",
    "            (\n",
    "            f\"MATCH (u1:User{{id: '{user_id}'}})-[s:SIMILAR]->(u2:User)\"\n",
    "            \" RETURN u2.id AS ID, s.score AS SCORE\"\n",
    "            \" ORDER BY SCORE DESC\"\n",
    "            )\n",
    "        ).to_table()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 162541/162541 [24:22<00:00, 111.13it/s]  "
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.008887097293124972\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "total_time = 0\n",
    "\n",
    "for user_id in tqdm(user_uuid_associations.values()):\n",
    "    start_time = time.time()\n",
    "    get_similar_users(user_id)\n",
    "    total_time += (time.time() - start_time)\n",
    "    \n",
    "print(total_time/len(user_uuid_associations.values()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Query 6__: Given a __User__, recommend __Movies__ movies based on  __Similar users__ "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>ID</th><th>TITLE</th><th>SCORE</th></tr><tr><td style=\"text-align:left\">79c6fa49-d613-4af3-a72e-4a3739b43bd9</td><td style=\"text-align:left\">Shaun of the Dead</td><td style=\"text-align:right\">4.971629977226257</td></tr><tr><td style=\"text-align:left\">e92771cc-599d-4e90-8597-069a98ca6918</td><td style=\"text-align:left\">Ideal Husband, An</td><td style=\"text-align:right\">4.971629977226257</td></tr><tr><td style=\"text-align:left\">97f4b2d4-a65d-47fa-a08f-416479e9fa68</td><td style=\"text-align:left\">Laputa: Castle in the Sky (Tenkû no shiro Rapyuta)</td><td style=\"text-align:right\">4.971629977226257</td></tr><tr><td style=\"text-align:left\">8435881c-0656-40e2-89ea-3fc463f059c3</td><td style=\"text-align:left\">Importance of Being Earnest, The</td><td style=\"text-align:right\">4.971629977226257</td></tr><tr><td style=\"text-align:left\">6fb08cda-074a-4420-9932-a445ad2797cf</td><td style=\"text-align:left\">Maltese Falcon, The</td><td style=\"text-align:right\">4.971629977226257</td></tr><tr><td style=\"text-align:left\">915c7e80-c3ba-4caa-a9ab-b6a82197f0ab</td><td style=\"text-align:left\">Shallow Grave</td><td style=\"text-align:right\">4.970742166042328</td></tr><tr><td style=\"text-align:left\">30364b6a-8313-405c-a16a-22cd44ab73a9</td><td style=\"text-align:left\">Good bye, Lenin!</td><td style=\"text-align:right\">4.970742166042328</td></tr><tr><td style=\"text-align:left\">c936dcee-e15a-4d0e-ab0a-778197297fe3</td><td style=\"text-align:left\">Henry V</td><td style=\"text-align:right\">4.970742166042328</td></tr><tr><td style=\"text-align:left\">150846fc-92ff-4cfa-b73c-31dea92f536f</td><td style=\"text-align:left\">True Romance</td><td style=\"text-align:right\">4.970742166042328</td></tr><tr><td style=\"text-align:left\">39756336-af98-40a0-9eb4-a255f666bc2d</td><td style=\"text-align:left\">Source Code</td><td style=\"text-align:right\">4.970742166042328</td></tr></table>"
      ],
      "text/plain": [
       " ID                                   | TITLE                                              |             SCORE \n",
       "--------------------------------------|----------------------------------------------------|-------------------\n",
       " 79c6fa49-d613-4af3-a72e-4a3739b43bd9 | Shaun of the Dead                                  | 4.971629977226257 \n",
       " e92771cc-599d-4e90-8597-069a98ca6918 | Ideal Husband, An                                  | 4.971629977226257 \n",
       " 97f4b2d4-a65d-47fa-a08f-416479e9fa68 | Laputa: Castle in the Sky (Tenkû no shiro Rapyuta) | 4.971629977226257 \n",
       " 8435881c-0656-40e2-89ea-3fc463f059c3 | Importance of Being Earnest, The                   | 4.971629977226257 \n",
       " 6fb08cda-074a-4420-9932-a445ad2797cf | Maltese Falcon, The                                | 4.971629977226257 \n",
       " 915c7e80-c3ba-4caa-a9ab-b6a82197f0ab | Shallow Grave                                      | 4.970742166042328 \n",
       " 30364b6a-8313-405c-a16a-22cd44ab73a9 | Good bye, Lenin!                                   | 4.970742166042328 \n",
       " c936dcee-e15a-4d0e-ab0a-778197297fe3 | Henry V                                            | 4.970742166042328 \n",
       " 150846fc-92ff-4cfa-b73c-31dea92f536f | True Romance                                       | 4.970742166042328 \n",
       " 39756336-af98-40a0-9eb4-a255f666bc2d | Source Code                                        | 4.970742166042328 "
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "graph.run(\n",
    "    (\n",
    "        f\"MATCH(u:User {{id: '{user_id}'}})-[:RATES]->(m:Movie) \"\n",
    "        \"WITH collect(m.id) AS watchedMoviesIds \"\n",
    "        f\"MATCH (u1:User{{id: '{user_id}'}})-[s:SIMILAR]->(u2:User)-[r:RATES]->(m:Movie) \"\n",
    "        \"WHERE NOT m.id IN watchedMoviesIds \"\n",
    "        \"RETURN m.id as ID, m.title as TITLE, avg(r.score)*avg(s.score) AS SCORE \"\n",
    "        \"ORDER BY SCORE DESC \"\n",
    "        \"LIMIT 10\"\n",
    "    )\n",
    ").to_table()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Computation time__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_cf_movies(user_id, K):\n",
    "    return graph.run(\n",
    "        (\n",
    "            f\"MATCH(u:User {{id: '{user_id}'}})-[:RATES]->(m:Movie) \"\n",
    "            \"WITH collect(m.id) AS watchedMoviesIds \"\n",
    "            f\"MATCH (u1:User{{id: '{user_id}'}})-[s:SIMILAR]->(u2:User)-[r:RATES]->(m:Movie) \"\n",
    "            \"WHERE NOT m.id IN watchedMoviesIds \"\n",
    "            \"RETURN m.id as ID, m.title as TITLE, avg(r.score)*avg(s.score) AS SCORE \"\n",
    "            \"ORDER BY SCORE DESC \"\n",
    "            f\"LIMIT {K}\"\n",
    "        )\n",
    "    ).to_table()   \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 162541/162541 [41:41<00:00, 64.98it/s] \n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.015258965377337765\n"
     ]
    }
   ],
   "source": [
    "total_time = 0\n",
    "\n",
    "for user_id in tqdm(user_uuid_associations.values()):\n",
    "    start_time = time.time()\n",
    "    get_cf_movies(user_id, K)\n",
    "    total_time += (time.time() - start_time)\n",
    "    \n",
    "print(total_time/len(user_uuid_associations.values()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__ITEM-BASED__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# projecting a second GDS graph to perform KNN on\n",
    "gds_recommendation.create_gds_projection(graph, \"knn_item_cf\", node_names=[\"Movie\"], node_property= \"cf_embedding\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#writing SIMILARITY KNN relationships back to the neo4j dataset\n",
    "gds_recommendation.write_knn_sim_relationships(neo4j_graph=graph, knn_gds_name=\"knn_item_cf\", node_name= \"Movie\", rel_name= \"USERS_ALSO_LIKED\", embedding_name=\"cf_embedding\", property=\"score\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>m1.id</th><th>ID</th><th>TITLE</th><th>SCORE</th></tr><tr><td style=\"text-align:left\">09d946c5-61ff-457b-a78e-69dcb3b44be9</td><td style=\"text-align:left\">253491e7-a09c-40be-9c80-0da9b9fed6ce</td><td style=\"text-align:left\">Three Colors: White (Trzy kolory: Bialy)</td><td style=\"text-align:right\">4.991997480392456</td></tr><tr><td style=\"text-align:left\">838a7128-9e2a-426a-83ef-de9cb34a880a</td><td style=\"text-align:left\">336851cb-2cee-4892-b91b-9be7e4d8ba0c</td><td style=\"text-align:left\">Eclisse, L&#039; (Eclipse)</td><td style=\"text-align:right\">4.98407244682312</td></tr><tr><td style=\"text-align:left\">bac4ba03-6d54-49dc-9591-e89b4387baea</td><td style=\"text-align:left\">f7990bd7-1794-4fb6-a4c0-d9f3e3f392b8</td><td style=\"text-align:left\">Seven (a.k.a. Se7en)</td><td style=\"text-align:right\">4.982698559761047</td></tr><tr><td style=\"text-align:left\">bac4ba03-6d54-49dc-9591-e89b4387baea</td><td style=\"text-align:left\">500b440d-0626-4218-a9a7-41c4049cddc9</td><td style=\"text-align:left\">Silence of the Lambs, The</td><td style=\"text-align:right\">4.981599450111389</td></tr><tr><td style=\"text-align:left\">72825dfb-fa4a-4204-af33-475c6e42e46e</td><td style=\"text-align:left\">35bf95d7-819c-4f80-b88d-dca971c1cd50</td><td style=\"text-align:left\">Donnie Darko</td><td style=\"text-align:right\">4.980944395065308</td></tr></table>"
      ],
      "text/plain": [
       " m1.id                                | ID                                   | TITLE                                    |             SCORE \n",
       "--------------------------------------|--------------------------------------|------------------------------------------|-------------------\n",
       " 09d946c5-61ff-457b-a78e-69dcb3b44be9 | 253491e7-a09c-40be-9c80-0da9b9fed6ce | Three Colors: White (Trzy kolory: Bialy) | 4.991997480392456 \n",
       " 838a7128-9e2a-426a-83ef-de9cb34a880a | 336851cb-2cee-4892-b91b-9be7e4d8ba0c | Eclisse, L' (Eclipse)                    |  4.98407244682312 \n",
       " bac4ba03-6d54-49dc-9591-e89b4387baea | f7990bd7-1794-4fb6-a4c0-d9f3e3f392b8 | Seven (a.k.a. Se7en)                     | 4.982698559761047 \n",
       " bac4ba03-6d54-49dc-9591-e89b4387baea | 500b440d-0626-4218-a9a7-41c4049cddc9 | Silence of the Lambs, The                | 4.981599450111389 \n",
       " 72825dfb-fa4a-4204-af33-475c6e42e46e | 35bf95d7-819c-4f80-b88d-dca971c1cd50 | Donnie Darko                             | 4.980944395065308 "
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_id = user_uuid_associations[1]\n",
    "\n",
    "graph.run(\n",
    "    (   f\"MATCH(u:User {{id: '{user_id}'}})-[:RATES]->(m:Movie) \"\n",
    "        \"WITH collect(m.id) AS watchedMoviesIds \"\n",
    "        f\"MATCH (u:User{{id: '{user_id}'}})-[r:RATES]->(m1:Movie)-[s:USERS_ALSO_LIKED]->(m2:Movie) \"\n",
    "        \"WHERE NOT m2.id IN watchedMoviesIds \"\n",
    "        \"RETURN m2.id AS ID , m2.title AS TITLE, s.score*r.score AS SCORE \"\n",
    "        \"ORDER BY SCORE DESC \"\n",
    "        \"LIMIT 5\"\n",
    "    )\n",
    ").to_table()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### __CONTENT BASED CF__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<module 'gds_recommendation' from '/home/alessia/Documenti/University/New Generation Data Models and DBMSs/gds_recommendation.py'>"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import gds_recommendation\n",
    "importlib.reload(gds_recommendation)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "gds_recommendation.create_gds_cypher_projection(\n",
    "    neo4j_graph=graph,\n",
    "    gds_name=\"cb_projection\",\n",
    "    node_names=[\"Movie\", \"Genre\", \"Category\"],\n",
    "    rel_names=[\"HAS_CATEGORY\", \"HAS_GENRE\"],\n",
    "    property_name=\"relevance\",\n",
    ")\n",
    "\n",
    "gds_recommendation.mutate_fastRP_embedding(\n",
    "    neo4j_graph=graph,\n",
    "    gds_name=\"cb_projection\",\n",
    "    node_names=[\"Genre\", \"Movie\", \"Category\"],\n",
    "    rel_names=[\"HAS_GENRE\", \"HAS_CATEGORY\"],\n",
    "    rel_property= \"relevance\",\n",
    "    embedding_name= \"cb_embedding\"\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "gds_recommendation.write_fastRP_embedding(\n",
    "    neo4j_graph=graph,\n",
    "    gds_name= \"cb_projection\",\n",
    "    nodes= [\"Movie\"],\n",
    "    embedding_name= \"cb_embedding\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "gds_recommendation.create_gds_projection(\n",
    "    neo4j_graph=graph,\n",
    "    gds_name=\"knn_cb\",\n",
    "    node_names= [\"Movie\"],\n",
    "    node_property= \"cb_embedding\"\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "gds_recommendation.write_knn_sim_relationships(\n",
    "    neo4j_graph=graph,\n",
    "    knn_gds_name = \"knn_cb\",\n",
    "    node_name= \"Movie\",\n",
    "    embedding_name= \"cb_embedding\",\n",
    "    rel_name=\"SIMILAR\",\n",
    "    property= \"score\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Query 7__: Given a __Movie__, find similar __Movies__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>ID</th><th>TITLE</th><th>SIMILARITY</th></tr><tr><td style=\"text-align:left\">c1637da5-4a10-4705-91a3-ccd2b2dfae5d</td><td style=\"text-align:left\">Monsters, Inc.</td><td style=\"text-align:right\">0.9974408149719238</td></tr><tr><td style=\"text-align:left\">291ee42a-318f-4f13-9663-bdfdb4563a00</td><td style=\"text-align:left\">Finding Nemo</td><td style=\"text-align:right\">0.996065616607666</td></tr><tr><td style=\"text-align:left\">e1296d83-27ef-4eb8-acb1-1c843561bfd1</td><td style=\"text-align:left\">Toy Story 2</td><td style=\"text-align:right\">0.9955590963363647</td></tr><tr><td style=\"text-align:left\">7f677070-b37b-4994-a622-4478c1199e7c</td><td style=\"text-align:left\">Toy Story 3</td><td style=\"text-align:right\">0.9943152666091919</td></tr><tr><td style=\"text-align:left\">3012ff53-1a92-477e-9721-25418152ff99</td><td style=\"text-align:left\">Ratatouille</td><td style=\"text-align:right\">0.9941790103912354</td></tr></table>"
      ],
      "text/plain": [
       " ID                                   | TITLE          |         SIMILARITY \n",
       "--------------------------------------|----------------|--------------------\n",
       " c1637da5-4a10-4705-91a3-ccd2b2dfae5d | Monsters, Inc. | 0.9974408149719238 \n",
       " 291ee42a-318f-4f13-9663-bdfdb4563a00 | Finding Nemo   |  0.996065616607666 \n",
       " e1296d83-27ef-4eb8-acb1-1c843561bfd1 | Toy Story 2    | 0.9955590963363647 \n",
       " 7f677070-b37b-4994-a622-4478c1199e7c | Toy Story 3    | 0.9943152666091919 \n",
       " 3012ff53-1a92-477e-9721-25418152ff99 | Ratatouille    | 0.9941790103912354 "
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "movie_id = movies_uuid_associations[1]\n",
    "\n",
    "graph.run(\n",
    "    (\n",
    "        f\"MATCH (m1:Movie{{id: '{movie_id}'}})-[s:SIMILAR]->(m2:Movie) \"\n",
    "        \"RETURN m2.id AS ID, m2.title AS TITLE, s.score AS SIMILARITY \"\n",
    "        \"ORDER BY SIMILARITY DESC \"\n",
    "    )\n",
    ").to_table()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_similar_movies(movie_id):\n",
    "    return graph.run(\n",
    "        (\n",
    "            f\"MATCH (m1:Movie{{id: '{movie_id}'}})-[s:SIMILAR]->(m2:Movie) \"\n",
    "            \"RETURN m2.id AS ID, m2.title AS TITLE, s.score AS SIMILARITY \"\n",
    "            \"ORDER BY SIMILARITY DESC \"\n",
    "        )\n",
    "    ).to_table()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 62423/62423 [05:24<00:00, 192.60it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.0051384527509131485\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "total_time = 0\n",
    "\n",
    "for movie_id in tqdm(movies_uuid_associations.values()):\n",
    "    start_time = time.time()\n",
    "    get_similar_movies(movie_id)\n",
    "    total_time += (time.time() - start_time)\n",
    "    \n",
    "print(total_time/len(movies_uuid_associations.values()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Query 8__: Given a __User__, recommend __Movies__ based on __similarità__ with the ones he has watched"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table><tr><th>ID</th><th>TITLE</th><th>SCORE</th></tr><tr><td style=\"text-align:left\">d94f08ef-db2a-43b1-803b-687d5b6404cd</td><td style=\"text-align:left\">Reservoir Dogs</td><td style=\"text-align:right\">4.989688396453857</td></tr><tr><td style=\"text-align:left\">8f6da847-c589-4703-8acc-f09946ebfb53</td><td style=\"text-align:left\">Youth</td><td style=\"text-align:right\">4.983693361282349</td></tr><tr><td style=\"text-align:left\">7a227d5b-d4fd-48e9-b06b-8466edaa9fc5</td><td style=\"text-align:left\">3-Iron (Bin-jip)</td><td style=\"text-align:right\">4.981707334518433</td></tr><tr><td style=\"text-align:left\">c392b034-a10f-4a43-b474-6e4d4d49415d</td><td style=\"text-align:left\">Akira Kurosawa&#039;s Dreams (Dreams)</td><td style=\"text-align:right\">4.981599748134613</td></tr><tr><td style=\"text-align:left\">f1d11eed-5ce7-4e6a-898a-9bc9910300ed</td><td style=\"text-align:left\">Reconstruction</td><td style=\"text-align:right\">4.981414079666138</td></tr><tr><td style=\"text-align:left\">3545fcc6-35d0-4250-b6be-fa16a750132f</td><td style=\"text-align:left\">In Bruges</td><td style=\"text-align:right\">4.981272220611572</td></tr><tr><td style=\"text-align:left\">e6095f71-9c8b-4e8c-bd5c-cfea313f85ab</td><td style=\"text-align:left\">Last Life in the Universe (Ruang rak noi nid mahasan)</td><td style=\"text-align:right\">4.9810075759887695</td></tr><tr><td style=\"text-align:left\">39a2f054-3a0e-40c1-9877-5d38c5121a18</td><td style=\"text-align:left\">Once Upon a Time in Hollywood</td><td style=\"text-align:right\">4.980625808238983</td></tr><tr><td style=\"text-align:left\">8cb22eaa-51df-44c1-96cd-d800376a08ce</td><td style=\"text-align:left\">Nostalghia</td><td style=\"text-align:right\">4.980106949806213</td></tr><tr><td style=\"text-align:left\">b3345260-d037-43cb-b333-8f7a41cf9476</td><td style=\"text-align:left\">The Hateful Eight</td><td style=\"text-align:right\">4.979964196681976</td></tr></table>"
      ],
      "text/plain": [
       " ID                                   | TITLE                                                 |              SCORE \n",
       "--------------------------------------|-------------------------------------------------------|--------------------\n",
       " d94f08ef-db2a-43b1-803b-687d5b6404cd | Reservoir Dogs                                        |  4.989688396453857 \n",
       " 8f6da847-c589-4703-8acc-f09946ebfb53 | Youth                                                 |  4.983693361282349 \n",
       " 7a227d5b-d4fd-48e9-b06b-8466edaa9fc5 | 3-Iron (Bin-jip)                                      |  4.981707334518433 \n",
       " c392b034-a10f-4a43-b474-6e4d4d49415d | Akira Kurosawa's Dreams (Dreams)                      |  4.981599748134613 \n",
       " f1d11eed-5ce7-4e6a-898a-9bc9910300ed | Reconstruction                                        |  4.981414079666138 \n",
       " 3545fcc6-35d0-4250-b6be-fa16a750132f | In Bruges                                             |  4.981272220611572 \n",
       " e6095f71-9c8b-4e8c-bd5c-cfea313f85ab | Last Life in the Universe (Ruang rak noi nid mahasan) | 4.9810075759887695 \n",
       " 39a2f054-3a0e-40c1-9877-5d38c5121a18 | Once Upon a Time in Hollywood                         |  4.980625808238983 \n",
       " 8cb22eaa-51df-44c1-96cd-d800376a08ce | Nostalghia                                            |  4.980106949806213 \n",
       " b3345260-d037-43cb-b333-8f7a41cf9476 | The Hateful Eight                                     |  4.979964196681976 "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_id = user_uuid_associations[1]\n",
    "\n",
    "graph.run(\n",
    "    (   f\"MATCH(u:User {{id: '{user_id}'}})-[:RATES]->(m:Movie) \"\n",
    "        \"WITH collect(m.id) AS watchedMoviesIds \"\n",
    "        f\"MATCH (u:User{{id: '{user_id}'}})-[r:RATES]->(m1:Movie)-[s:SIMILAR]->(m2:Movie) \"\n",
    "        \"WHERE NOT m2.id IN watchedMoviesIds \"\n",
    "        \"RETURN m2.id AS ID , m2.title AS TITLE, s.score*r.score AS SCORE \"\n",
    "        \"ORDER BY SCORE DESC \"\n",
    "        \"LIMIT 10\"\n",
    "    )\n",
    ").to_table()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "__Computation time__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_cb_movies(user_id, K):\n",
    "    return graph.run(\n",
    "        (   f\"MATCH(u:User {{id: '{user_id}'}})-[:RATES]->(m:Movie) \"\n",
    "            \"WITH collect(m.id) AS watchedMoviesIds \"\n",
    "            f\"MATCH (u:User{{id: '{user_id}'}})-[r:RATES]->(m1:Movie)-[s:SIMILAR]->(m2:Movie) \"\n",
    "            \"WHERE NOT m2.id IN watchedMoviesIds \"\n",
    "            \"RETURN m2.id AS ID , m2.title AS TITLE, s.score*r.score AS SCORE \"\n",
    "            \"ORDER BY SCORE DESC \"\n",
    "            f\"LIMIT {K}\"\n",
    "        )\n",
    "    ).to_table()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 162541/162541 [34:14<00:00, 79.13it/s]  \n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.012507320784452268\n"
     ]
    }
   ],
   "source": [
    "total_time = 0\n",
    "\n",
    "for user_id in tqdm(user_uuid_associations.values()):\n",
    "    start_time = time.time()\n",
    "    get_cb_movies(user_id, K)\n",
    "    total_time += (time.time() - start_time)\n",
    "    \n",
    "print(total_time/len(user_uuid_associations.values()))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.10.4 ('venv': venv)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.4"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "56856fb219af371e327a0bd05d55f5c65d03579680a2662f718f0cbf637246ff"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}