workshop/gqlalchemy-workshop.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fad76b97",
   "metadata": {},
   "source": [
    "<h1 align=\"center\">An Introduction to Graph Databases with GQLAlchemy and Python</h1>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4595587e",
   "metadata": {},
   "source": [
    "# Table of contents <a name=\"toc\"></a>\n",
    "1. [Introduction](#introduction)\n",
    "2. [Prerequisites](#prerequisites)\n",
    "3. [Requirements](#requirements)\n",
    "4. [Dataset and graph model](#dataset)\n",
    "5. [Connect to Memgraph with GQLAlchemy](#connect)\n",
    "6. [Define a graph schema](#graph-schema)\n",
    "7. [Creating and returning nodes and relationships](#create-return)\n",
    "8. [Importing data from CSV files](#import)\n",
    "9. [Querying the database and retrieving results](#query)\n",
    "10. [Example queries](#exercises)\n",
    "11. [Want to know more?](#extra)\n",
    "    1. [Movie recommender](#movie)\n",
    "    2. [Graph analytics](#graph-analytics)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4ee4433",
   "metadata": {},
   "source": [
    "## 1. Introduction <a name=\"introduction\"></a>\n",
    "\n",
    "Through this tutorial, we will learn how to create a graph model from a dataset, run Memgraph with Docker, connect to it from a Jupyter Notebook with the help of GQLAlchemy, and perform simple queries. We will explore the **movies dataset** consisting of two CSV files: `movies.csv` containing movie title and genre and `ratings.csv` that holds information about users' ratings.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "172070f5",
   "metadata": {},
   "source": [
    "## 2. Prerequisites <a name=\"prerequisites\"></a>\n",
    "\n",
    "For this tutorial, we need to install:\n",
    "\n",
    "- [Jupyter](https://jupyter.org/install) - to run the Jupyter notebook\n",
    "- [Docker](https://docs.docker.com/get-docker/) - to run Memgraph, since Memgraph is a native Linux application and cannot be installed on Windows and macOS\n",
    "- [CMake](https://cmake.org/install/) - prerequisite for GQLAlchemy\n",
    "- [Memgraph Platform](https://memgraph.com/docs/memgraph/installation) - the complete streaming graph application platform; follow the instructions to install Memgraph Platform with Docker for your OS\n",
    "\n",
    "> ### Memgraph Platform installation using Docker\n",
    ">\n",
    "> After we install Docker, we can run the Memgraph Platform container by running:\n",
    ">\n",
    ">```\n",
    ">docker run -it -p 7687:7687 -p 3000:3000 memgraph/memgraph-platform:2.2.5\n",
    ">```\n",
    ">\n",
    ">**Memgraph Platform** contains:\n",
    ">\n",
    ">- **Memgraph DB** - the database that holds your data\n",
    ">- **Memgraph Lab** - visual user interface for running queries and visualizing graph data (running at >`localhost:3000`)\n",
    ">- **mgconsole** - command-line interface for running queries\n",
    ">- **MAGE** - graph algorithms and modules library\n",
    "\n",
    "## 3. Requirements  <a name=\"requirements\"></a>\n",
    "\n",
    "For this tutorial, we need to install the following Python libraries:\n",
    "\n",
    "- [GQLAlchemy](https://pypi.org/project/gqlalchemy/)\n",
    "- [Pandas](https://pypi.org/project/pandas/)\n",
    "\n",
    "All info about the versions can be found at [requirements](../requirements.txt) file."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70d165b5",
   "metadata": {},
   "source": [
    "## 4. Dataset and graph model <a name=\"dataset\"></a>\n",
    "\n",
    "We will use the Pandas library to show how `movies.csv` and `ratings.csv` files look like."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "20c7a796",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>movieId</th>\n",
       "      <th>title</th>\n",
       "      <th>genres</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Toy Story (1995)</td>\n",
       "      <td>Adventure|Animation|Children|Comedy|Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Jumanji (1995)</td>\n",
       "      <td>Adventure|Children|Fantasy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Grumpier Old Men (1995)</td>\n",
       "      <td>Comedy|Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>Waiting to Exhale (1995)</td>\n",
       "      <td>Comedy|Drama|Romance</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>Father of the Bride Part II (1995)</td>\n",
       "      <td>Comedy</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   movieId                               title  \\\n",
       "0        1                    Toy Story (1995)   \n",
       "1        2                      Jumanji (1995)   \n",
       "2        3             Grumpier Old Men (1995)   \n",
       "3        4            Waiting to Exhale (1995)   \n",
       "4        5  Father of the Bride Part II (1995)   \n",
       "\n",
       "                                        genres  \n",
       "0  Adventure|Animation|Children|Comedy|Fantasy  \n",
       "1                   Adventure|Children|Fantasy  \n",
       "2                               Comedy|Romance  \n",
       "3                         Comedy|Drama|Romance  \n",
       "4                                       Comedy  "
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "movies = pd.read_csv(\"../data/movies.csv\")\n",
    "movies.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d2a68081",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>userId</th>\n",
       "      <th>movieId</th>\n",
       "      <th>rating</th>\n",
       "      <th>timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964982703</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964981247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>4.0</td>\n",
       "      <td>964982224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>47</td>\n",
       "      <td>5.0</td>\n",
       "      <td>964983815</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>50</td>\n",
       "      <td>5.0</td>\n",
       "      <td>964982931</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   userId  movieId  rating  timestamp\n",
       "0       1        1     4.0  964982703\n",
       "1       1        3     4.0  964981247\n",
       "2       1        6     4.0  964982224\n",
       "3       1       47     5.0  964983815\n",
       "4       1       50     5.0  964982931"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratings = pd.read_csv(\"../data/ratings.csv\")\n",
    "ratings.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad86b184",
   "metadata": {},
   "source": [
    "*** \n",
    "\n",
    "Next, we must create a **graph model** for the movies dataset. Every movie has a title and genre it belongs to. Therefore, let's create `:Movie` and `:Genre` nodes and the relationship between them of `OF_GENRE` type. The `:Movie` nodes will have `id` and `title` properties, while `:Genre` nodes will have just `name` property. In `ratings.csv`, we have users who rated movies at a certain moment. Hence, we should create `:User` nodes and a new relationship type called `RATED` between `:User` and `:Movie` nodes. This relationship will have properties `rating` and `timestamp`. `:User` node only has `id` property. In the image below, we have the graph model (schema).\n",
    "\n",
    "![movies_graph_model](../img/movie-schema.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75700197",
   "metadata": {},
   "source": [
    "## 5. Connect to Memgraph with GQLAlchemy <a name=\"connect\"></a>\n",
    "\n",
    "We will use the **GQLAlchemy**'s object graph mapper (OGM) to connect to Memgraph and quickly execute **Cypher** queries. GQLAlchemy also serves as a Python driver/client for Memgraph. We can install it using:\n",
    "\n",
    "```\n",
    "pip install gqlalchemy\n",
    "```\n",
    "\n",
    "> You may need to install [CMake](https://cmake.org/download/) before installing GQLAlchemy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "e91e3efe",
   "metadata": {},
   "outputs": [],
   "source": [
    "from gqlalchemy import Memgraph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "93a1c2a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "memgraph = Memgraph(\"127.0.0.1\", 7687)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "425d5675",
   "metadata": {},
   "source": [
    "Let's make sure that Memgraph is empty before we start with anything else."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9f68fcb0",
   "metadata": {},
   "outputs": [],
   "source": [
    "memgraph.drop_database()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1ffcea1a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'number_of_nodes': 0}\n"
     ]
    }
   ],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (n) RETURN count(n) AS number_of_nodes ;\n",
    "    \"\"\"\n",
    ")\n",
    "print(next(results))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d3acc5c",
   "metadata": {},
   "source": [
    "## 6. Define a graph schema <a name=\"graph-schema\"></a>\n",
    "\n",
    "Next, we have to define the above graph schema in the Python code. This is where **GQLAlchemy** steps in with its Object Graph Mapper."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "53c11afe",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "from typing import Optional\n",
    "from gqlalchemy import Node, Relationship, Field\n",
    "\n",
    "class Movie(Node):\n",
    "    id: int = Field(index=True, unique=True, exists=True, db=memgraph)\n",
    "    title: Optional[str] = Field()\n",
    "        \n",
    "\n",
    "class User(Node):\n",
    "    id: int = Field(index=True, unique=True, exists=True, db=memgraph)\n",
    "    name: Optional[str] = Field()\n",
    "        \n",
    "class Genre(Node):\n",
    "    name: str = Field()\n",
    "\n",
    "class Rated(Relationship, type=\"RATED\"):\n",
    "    rating: float = Field()\n",
    "    timestamp: float = Field()\n",
    "\n",
    "class OfGenre(Relationship, type=\"OF_GENRE\"):\n",
    "    pass"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cb498efe",
   "metadata": {},
   "source": [
    "What’s going on here:\n",
    "\n",
    "- `Node` is a Python class that maps to a graph object in `Memgraph`.\n",
    "- Classes that inherit from `Node` map to a single label in the graph database.\n",
    "- In this case, the class `Movie` maps to the label `:Movie`.\n",
    "- The properties `id` and `title` are mapped to properties of the nodes labeled `Movie` in the graph database. Their types must be defined and are enforced.\n",
    "- If the property type is missing the `Optional` keyword, then it can’t be `None` or missing.\n",
    "- `Field` is a function from pydantic that you can use to define constraints in the graph database like uniqueness constraints, indexes and existence constraints.\n",
    "- Whenever you provide a constraint to the `Field` function, you have to provide a `Memgraph` object in the `db` argument, so those constraints can be enforced.\n",
    "- In this instance, uniqueness and exists constraints, as well as index are added to `Memgraph` on the label `Movie` and the property `id`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e958d8ed",
   "metadata": {},
   "source": [
    "## 7. Creating and returning nodes and relationships <a name=\"create-return\"></a>\n",
    "\n",
    "Before we import the whole dataset from the CSV files, let's see how we can create a simple node and relationship. First, we will create one `User` node and one `Movie` node."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0dfccf29",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<User id=3 labels={'User'} properties={'id': 1, 'name': 'Katarina'}>\n",
      "<Movie id=4 labels={'Movie'} properties={'id': 1, 'title': 'LOTR'}>\n"
     ]
    }
   ],
   "source": [
    "user_node = User(id=1, name=\"Katarina\")\n",
    "movie_node = Movie(id=1, title=\"LOTR\")\n",
    "\n",
    "user_node.save(memgraph)\n",
    "movie_node.save(memgraph)\n",
    "\n",
    "print(user_node)\n",
    "print(movie_node)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e540697d",
   "metadata": {},
   "source": [
    "Now, let's try to create a node using the Cypher query language. We will create a user with an existing `id` just to check if the uniqueness constraint on the property `id` is set correctly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "72d4e770",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Error: A user with the name Katarina is already in the database.\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    results = memgraph.execute(\n",
    "        \"\"\"\n",
    "        CREATE (:User {name: \"Katarina\"});\n",
    "        \"\"\"\n",
    "    )\n",
    "except Exception:\n",
    "    print(\"Error: A user with the name Katarina is already in the database.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "94702cfc",
   "metadata": {},
   "source": [
    "Next, let's create a relationship of type `RATED` between the existing `User` and `Movie` nodes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "43e50fd0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Rated id=1 start_node_id=3 end_node_id=4 nodes=(3, 4) type=RATED properties={'rating': 5.0, 'timestamp': 1648401504.822471}>\n"
     ]
    }
   ],
   "source": [
    "from time import time\n",
    "\n",
    "rated_relationship = Rated(_start_node_id=user_node._id, _end_node_id=movie_node._id, rating=5.0, timestamp=time())\n",
    "\n",
    "rated_relationship.save(memgraph)\n",
    "\n",
    "print(rated_relationship)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2277c50",
   "metadata": {},
   "source": [
    "## 8. Importing data from CSV files <a name=\"import\"></a>\n",
    "\n",
    "First, let's clean up the database to import only data from the CSV files.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "dae2543a",
   "metadata": {},
   "outputs": [],
   "source": [
    "memgraph.drop_database()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b465fca",
   "metadata": {},
   "source": [
    "First, we need to download the `movies.csv` and `ratings.csv` files. To import it into Memgraph, we need to copy it to the Docker container where Memgraph is running.\n",
    "Find the `CONTAINER_ID` by running:\n",
    "\n",
    "```\n",
    "docker ps\n",
    "```\n",
    "\n",
    "Copy the files with the following commands (don't forget to replace `CONTAINER_ID`):\n",
    "\n",
    "```\n",
    "docker cp movies.csv CONTAINER_ID:movies.csv\n",
    "```\n",
    "\n",
    "```\n",
    "docker cp ratings.csv CONTAINER_ID:ratings.csv\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "be194ec4",
   "metadata": {},
   "source": [
    "We will import data to Memgraph using the `LOAD CSV` Cypher clause that enables us to load and use data from a CSV file of our choosing in a row-based manner within a query. Memgraph supports the Excel CSV dialect, the most common one. For more info, check out [Memgraph Docs](https://memgraph.com/docs/memgraph/import-data/load-csv-clause).\n",
    "\n",
    "The following method will run the `LOAD CSV` clause inside Memgraph and create `Movie` and `Genre` nodes and the `OF_GENRE` relationship between them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "c40e8239",
   "metadata": {},
   "outputs": [],
   "source": [
    "memgraph.execute(\n",
    "    \"\"\"\n",
    "    LOAD CSV FROM \"/movies.csv\" WITH HEADER AS row\n",
    "    MERGE (m:Movie {id: toInteger(row.movieId), title: row.title}) \n",
    "    WITH m, row\n",
    "    UNWIND split(row.genres, '|') as genre\n",
    "    MERGE (m)-[:OF_GENRE]->(:Genre {name: genre});\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "320093d6",
   "metadata": {},
   "source": [
    "All that is left to create is `User` node and connect it to the `Movie` node that the user rated. We will again use the `LOAD CSV` clause to import data from the `ratings.csv` file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f9434a5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "memgraph.execute(\n",
    "    \"\"\"\n",
    "    LOAD CSV FROM \"/ratings.csv\" WITH HEADER AS row\n",
    "    MATCH (m:Movie {id: toInteger(row.movieId)})\n",
    "    MERGE (u:User {id: toInteger(row.userId)})\n",
    "    MERGE (u)-[:RATED {rating: toFloat(row.rating), timestamp: toFloat(row.timestamp)}]->(m);\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b7b577d",
   "metadata": {},
   "source": [
    "Great! We imported all the data into Memgraph. Feel free to check it out in Memgraph Lab at `localhost:3000`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3c182648",
   "metadata": {},
   "source": [
    "## 9. Querying the database and retrieving results <a name=\"query\"></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4ee5d55",
   "metadata": {},
   "source": [
    "The simplest usage of the **Cypher query language** is to find data stored in the database. For that, you can use one of the following clauses:\n",
    "\n",
    "- `MATCH` which searches for patterns.\n",
    "- `WHERE` for filtering the matched data.\n",
    "- `RETURN` for defining what will be presented to the user in the result set.\n",
    "- `UNION` and `UNION ALL` for combining results from multiple queries.\n",
    "- `UNWIND` for unwinding a list of values as individual rows.\n",
    "\n",
    "Let's create a simple `MATCH` query to find all movies and order them by their title in ascending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "837c4220",
   "metadata": {},
   "outputs": [],
   "source": [
    "results = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (m:Movie)\n",
    "    RETURN m\n",
    "    ORDER BY m.title ASC\n",
    "    LIMIT 10;\n",
    "    \"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "87dfd43d",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Movie id=8606 labels={'Movie'} properties={'id': 117867, 'title': \"'71 (2014)\"}>\n",
      "<Movie id=8020 labels={'Movie'} properties={'id': 97757, 'title': \"'Hellboy': The Seeds of Creation (2004)\"}>\n",
      "<Movie id=5534 labels={'Movie'} properties={'id': 26564, 'title': \"'Round Midnight (1986)\"}>\n",
      "<Movie id=5696 labels={'Movie'} properties={'id': 27751, 'title': \"'Salem's Lot (2004)\"}>\n",
      "<Movie id=620 labels={'Movie'} properties={'id': 779, 'title': \"'Til There Was You (1997)\"}>\n",
      "<Movie id=9189 labels={'Movie'} properties={'id': 149380, 'title': \"'Tis the Season for Love (2015)\"}>\n",
      "<Movie id=1543 labels={'Movie'} properties={'id': 2072, 'title': \"'burbs, The (1989)\"}>\n",
      "<Movie id=2359 labels={'Movie'} properties={'id': 3112, 'title': \"'night Mother (1986)\"}>\n",
      "<Movie id=7081 labels={'Movie'} properties={'id': 69757, 'title': '(500) Days of Summer (2009)'}>\n",
      "<Movie id=5128 labels={'Movie'} properties={'id': 8169, 'title': '*batteries not included (1987)'}>\n"
     ]
    }
   ],
   "source": [
    "results = list(results)\n",
    "\n",
    "for result in results:\n",
    "    print(result[\"m\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2e57132",
   "metadata": {},
   "source": [
    "We may wonder whether these results have been correctly serialized back to the defined schema. We can check that out:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "8c77df3b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Movie:  (500) Days of Summer (2009)\n",
      "Type:  <class '__main__.Movie'>\n"
     ]
    }
   ],
   "source": [
    "m = results[8][\"m\"]\n",
    "\n",
    "print(\"Movie: \", m.title)\n",
    "print(\"Type: \", type(m))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "030cf990",
   "metadata": {},
   "source": [
    "As expected, the movie in the result is an instance of `Movie` class and it has properties `id` and `title` that can be read.\n",
    "\n",
    "If you're not keen on Cypher query language, you can try out the GQLAlchemy **query builder**. Let's rewrite the above Cypher query with the help of the query builder:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "f8721836",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Movie id=8606 labels={'Movie'} properties={'id': 117867, 'title': \"'71 (2014)\"}>\n",
      "<Movie id=8020 labels={'Movie'} properties={'id': 97757, 'title': \"'Hellboy': The Seeds of Creation (2004)\"}>\n",
      "<Movie id=5534 labels={'Movie'} properties={'id': 26564, 'title': \"'Round Midnight (1986)\"}>\n",
      "<Movie id=5696 labels={'Movie'} properties={'id': 27751, 'title': \"'Salem's Lot (2004)\"}>\n",
      "<Movie id=620 labels={'Movie'} properties={'id': 779, 'title': \"'Til There Was You (1997)\"}>\n",
      "<Movie id=9189 labels={'Movie'} properties={'id': 149380, 'title': \"'Tis the Season for Love (2015)\"}>\n",
      "<Movie id=1543 labels={'Movie'} properties={'id': 2072, 'title': \"'burbs, The (1989)\"}>\n",
      "<Movie id=2359 labels={'Movie'} properties={'id': 3112, 'title': \"'night Mother (1986)\"}>\n",
      "<Movie id=7081 labels={'Movie'} properties={'id': 69757, 'title': '(500) Days of Summer (2009)'}>\n",
      "<Movie id=5128 labels={'Movie'} properties={'id': 8169, 'title': '*batteries not included (1987)'}>\n"
     ]
    }
   ],
   "source": [
    "from gqlalchemy import match\n",
    "\n",
    "results_from_qb = (\n",
    "    match()\n",
    "    .node(labels=\"Movie\", variable=\"m\")\n",
    "    .return_()\n",
    "    .order_by(\"m.title ASC\")\n",
    "    .limit(10)\n",
    "    .execute()\n",
    ")\n",
    "results_from_qb = list(results_from_qb)\n",
    "\n",
    "for result in results_from_qb:\n",
    "    print(result[\"m\"])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2710753a",
   "metadata": {},
   "source": [
    "As expected, the result is the same as above.\n",
    "\n",
    "## 10. Example queries  <a name=\"exercises\"></a>\n",
    "\n",
    "\n",
    "### 🔷 Example 1: Find out how many users there are in the database.\n",
    "\n",
    "The correct Cypher query would be:\n",
    "\n",
    "```\n",
    "MATCH (n:User)\n",
    "RETURN count(n) AS num_of_users;\n",
    "```\n",
    "\n",
    "You can try it out in Memgraph Lab at `localhost:3000`.\n",
    "\n",
    "With GQLAlchemy's query builder, the solution is:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "cffbded6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "610\n"
     ]
    }
   ],
   "source": [
    "total_users = (\n",
    "    match()\n",
    "    .node(labels=\"User\", variable=\"u\")\n",
    "    .return_({\"count(u)\": \"num_of_users\"})\n",
    "    .execute()\n",
    ")\n",
    "\n",
    "results = list(total_users)\n",
    "for result in results:\n",
    "    print(result[\"num_of_users\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3ac7c9b4",
   "metadata": {},
   "source": [
    "#### ❓Exercise 1: Find out how many genres there are in the database.\n",
    "\n",
    "***\n",
    "\n",
    "### 🔷 Example 2: Find out how many users rated the movie with the title 'Home Alone (1990)'.\n",
    "\n",
    "The correct Cypher query would be:\n",
    "\n",
    "```\n",
    "MATCH (u:User)-[:RATED]->(:Movie {title: 'Home Alone (1990)'})\n",
    "RETURN count(u) AS num_of_users;\n",
    "```\n",
    "\n",
    "You can try it out in Memgraph Lab at `localhost:3000`.\n",
    "\n",
    "With GQLAlchemy's query builder, the solution is:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "9ee1b6ce",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "116\n"
     ]
    }
   ],
   "source": [
    "home_alone = (\n",
    "    match()\n",
    "    .node(labels=\"User\", variable=\"u\")\n",
    "    .to(\"RATED\")\n",
    "    .node(labels=\"Movie\", variable=\"m\")\n",
    "    .where(\"m.title\", \"=\", \"Home Alone (1990)\")\n",
    "    .return_({\"count(u)\": \"num_of_users\"})\n",
    "    .execute()\n",
    ")\n",
    "\n",
    "results = list(home_alone)\n",
    "\n",
    "for result in results:\n",
    "    print(result[\"num_of_users\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8be36006",
   "metadata": {},
   "source": [
    "#### ❓Exercise 2: Find out to how many genres movie 'Matrix, The (1999)' belongs to.\n",
    "\n",
    "***\n",
    "\n",
    "### 🔷 Example 3: Find out the name of the genres the movie 'Blue Lagoon, The (1980)' belongs to.\n",
    "\n",
    "The correct Cypher query would be:\n",
    "\n",
    "```\n",
    "MATCH (:Movie {title: 'Blue Lagoon, The (1980)'})-[:OF_GENRE]->(g:Genre)\n",
    "RETURN g.name AS genre;\n",
    "```\n",
    "\n",
    "You can try it out in Memgraph Lab at `localhost:3000`.\n",
    "\n",
    "With GQLAlchemy's query builder, the solution is:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "1bb4116b",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Adventure\n",
      "Drama\n",
      "Romance\n"
     ]
    }
   ],
   "source": [
    "genres = (\n",
    "    match()\n",
    "    .node(labels=\"Movie\", variable=\"m\")\n",
    "    .to(\"OF_GENRE\")\n",
    "    .node(labels=\"Genre\", variable=\"g\")\n",
    "    .where(\"m.title\", \"=\", \"Blue Lagoon, The (1980)\")\n",
    "    .return_({\"g.name\": \"genre\"})\n",
    "    .execute()\n",
    ")\n",
    "\n",
    "results = list(genres)\n",
    "\n",
    "for result in results:\n",
    "    print(result[\"genre\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da00fcc6",
   "metadata": {},
   "source": [
    "#### ❓Exercise 3: Find out the title of the movies that the user with `id` 1 rated.\n",
    "\n",
    "***\n",
    "\n",
    "### 🔷 Example 4: List 10 movies of 'Comedy' and 'Action' genres and sort them by title.\n",
    "\n",
    "The correct Cypher query would be:\n",
    "\n",
    "```\n",
    "MATCH (m:Movie)-[:OF_GENRE]->(:Genre {name: \"Action\"})\n",
    "MATCH (m)-[:OF_GENRE]->(:Genre {name: \"Comedy\"})\n",
    "RETURN m.title\n",
    "ORDER BY m.title\n",
    "LIMIT 10;\n",
    "```\n",
    "\n",
    "You can try it out in Memgraph Lab at `localhost:3000`.\n",
    "\n",
    "With GQLAlchemy's query builder, the solution is:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "10814649",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "'Hellboy': The Seeds of Creation (2004)\n",
      "10th Victim, The (La decima vittima) (1965)\n",
      "2 Guns (2013)\n",
      "21 Jump Street (2012)\n",
      "22 Jump Street (2014)\n",
      "3 Ninjas (1992)\n",
      "3 Ninjas Kick Back (1994)\n",
      "3 Ninjas: High Noon On Mega Mountain (1998)\n",
      "30 Minutes or Less (2011)\n",
      "48 Hrs. (1982)\n"
     ]
    }
   ],
   "source": [
    "movies = (\n",
    "    match()\n",
    "    .node(labels=\"Movie\", variable=\"m\")\n",
    "    .to(\"OF_GENRE\")\n",
    "    .node(labels=\"Genre\", variable=\"g1\")\n",
    "    .where(\"g1.name\", \"=\", \"Action\")\n",
    "    .match()\n",
    "    .node(labels=\"Movie\", variable=\"m\")\n",
    "    .to(\"OF_GENRE\")\n",
    "    .node(labels=\"Genre\", variable=\"g2\")\n",
    "    .where(\"g2.name\", \"=\", \"Comedy\")\n",
    "    .return_({\"m.title\": \"movie\"})\n",
    "    .order_by(\"m.title\")\n",
    "    .limit(10)\n",
    "    .execute()\n",
    ")\n",
    "\n",
    "results = list(movies)\n",
    "\n",
    "for result in results:\n",
    "    print(result[\"movie\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "36ff58e0",
   "metadata": {},
   "source": [
    "#### ❓Exercise 4: List 15 movies of 'Documentary' and 'Comedy' genres and sort them by title descending.\n",
    "\n",
    "***\n",
    "\n",
    "### 🔷 Example 5: Find out the average rating of the 'Star Wars: Episode IV - A New Hope (1977)' movie.\n",
    "\n",
    "Here we're going to use one of the **Cypher functions**. We are going to use aggregation `avg` function, that returns an average value of rows with numerical values generated with the `MATCH` or `UNWIND` clause. The list of the supported functions can be found in the [Cypher manual](https://memgraph.com/docs/cypher-manual/functions).\n",
    "\n",
    "The correct Cypher query would be:\n",
    "\n",
    "```\n",
    "MATCH (:User)-[r:RATED]->(:Movie {title: 'Star Wars: Episode IV - A New Hope (1977)'})\n",
    "RETURN avg(r.rating);\n",
    "```\n",
    "\n",
    "You can try it out in Memgraph Lab at `localhost:3000`.\n",
    "\n",
    "With GQLAlchemy's query builder, the solution is:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "536c3eaa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "4.231075697211155\n"
     ]
    }
   ],
   "source": [
    "rating = (\n",
    "    match()\n",
    "    .node(labels=\"User\")\n",
    "    .to(\"RATED\", variable=\"r\")\n",
    "    .node(labels=\"Movie\", variable=\"m\")\n",
    "    .where(\"m.title\", \"=\", \"Star Wars: Episode IV - A New Hope (1977)\")\n",
    "    .return_({\"avg(r.rating)\": \"average_rating\"})\n",
    "    .execute()\n",
    ")\n",
    "\n",
    "results = list(rating)\n",
    "\n",
    "for result in results:\n",
    "    print(result[\"average_rating\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b02bd393",
   "metadata": {},
   "source": [
    "#### ❓Exercise 5: Find out the minimum rating of the 'Star Wars: Episode I - The Phantom Menace (1999)' movie."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "145c17d9",
   "metadata": {},
   "source": [
    "## 11. Want to know more? <a name=\"extra\"></a>\n",
    "\n",
    "### A. Movie recommender <a name=\"movie\"></a>\n",
    "\n",
    "We will implement a simple memory based [collaborative filtering](https://en.wikipedia.org/wiki/Collaborative_filtering). The memory-based approach uses user rating data to compute the similarity between users or items."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "43e2f45a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Pan's Labyrinth (Laberinto del fauno, El) (2006) 5.0\n",
      "Lord of the Rings: The Two Towers, The (2002) 5.0\n",
      "Payback (1999) 5.0\n",
      "Catch Me If You Can (2002) 5.0\n",
      "Iron Giant, The (1999) 5.0\n",
      "Big Fish (2003) 5.0\n",
      "Godfather: Part II, The (1974) 5.0\n",
      "Avengers, The (2012) 5.0\n",
      "Usual Suspects, The (1995) 5.0\n",
      "Intouchables (2011) 5.0\n",
      "Manhattan (1979) 5.0\n",
      "Manhattan Murder Mystery (1993) 5.0\n",
      "Chungking Express (Chung Hing sam lam) (1994) 5.0\n",
      "Minority Report (2002) 5.0\n",
      "Goodfellas (1990) 5.0\n",
      "Harry Potter and the Deathly Hallows: Part 2 (2011) 5.0\n",
      "Annie Hall (1977) 5.0\n",
      "Inside Job (2010) 5.0\n",
      "Beautiful Mind, A (2001) 5.0\n",
      "Godfather, The (1972) 5.0\n",
      "Into the Wild (2007) 5.0\n",
      "The Jinx: The Life and Deaths of Robert Durst (2015) 5.0\n",
      "Princess Bride, The (1987) 5.0\n",
      "Matrix, The (1999) 5.0\n",
      "Wolf of Wall Street, The (2013) 5.0\n",
      "Rain Man (1988) 5.0\n",
      "Like Water for Chocolate (Como agua para chocolate) (1992) 5.0\n",
      "Mad Max: Fury Road (2015) 5.0\n",
      "Piano, The (1993) 5.0\n",
      "Gone Girl (2014) 5.0\n",
      "Shakespeare in Love (1998) 5.0\n",
      "Brazil (1985) 5.0\n",
      "Despicable Me (2010) 5.0\n",
      "Harry Potter and the Deathly Hallows: Part 1 (2010) 5.0\n",
      "Young Frankenstein (1974) 5.0\n",
      "Step Brothers (2008) 5.0\n",
      "Out of Sight (1998) 5.0\n",
      "Iron Man (2008) 4.75\n",
      "Green Mile, The (1999) 4.75\n",
      "Dark Knight, The (2008) 4.625\n",
      "Fight Club (1999) 4.625\n",
      "Coraline (2009) 4.5\n",
      "Lord of the Rings: The Return of the King, The (2003) 4.5\n",
      "Gravity (2013) 4.5\n",
      "Sixth Sense, The (1999) 4.5\n",
      "13th Warrior, The (1999) 4.5\n",
      "French Connection, The (1971) 4.5\n",
      "Inception (2010) 4.5\n",
      "Skyfall (2012) 4.5\n",
      "Hannah and Her Sisters (1986) 4.5\n",
      "Good Will Hunting (1997) 4.5\n",
      "Iron Man 3 (2013) 4.5\n",
      "Casino Royale (2006) 4.5\n",
      "Sherlock Holmes: A Game of Shadows (2011) 4.5\n",
      "Harry Potter and the Goblet of Fire (2005) 4.5\n",
      "10th & Wolf (2006) 4.5\n",
      "Children of Men (2006) 4.5\n",
      "Star Trek (2009) 4.5\n",
      "Harry Potter and the Half-Blood Prince (2009) 4.5\n",
      "Eat Drink Man Woman (Yin shi nan nu) (1994) 4.5\n",
      "Gran Torino (2008) 4.5\n",
      "Warrior (2011) 4.5\n",
      "Harry Potter and the Prisoner of Azkaban (2004) 4.5\n",
      "Town, The (2010) 4.5\n",
      "District 9 (2009) 4.5\n",
      "How to Train Your Dragon (2010) 4.333333333333333\n",
      "Big Hero 6 (2014) 4.25\n",
      "Inglourious Basterds (2009) 4.25\n",
      "Streetcar Named Desire, A (1951) 4.25\n",
      "Truman Show, The (1998) 4.25\n",
      "Shutter Island (2010) 4.166666666666667\n",
      "Louis C.K.: Hilarious (2010) 4.0\n",
      "Pulp Fiction (1994) 4.0\n",
      "Shawshank Redemption, The (1994) 4.0\n",
      "Prestige, The (2006) 4.0\n",
      "V for Vendetta (2006) 4.0\n",
      "Tommy Boy (1995) 4.0\n",
      "Jungle Book, The (1967) 4.0\n",
      "Game, The (1997) 4.0\n",
      "28 Days Later (2002) 4.0\n",
      "Megamind (2010) 4.0\n",
      "Departed, The (2006) 4.0\n",
      "11:14 (2003) 4.0\n",
      "Faculty, The (1998) 4.0\n",
      "Tank Girl (1995) 4.0\n",
      "11th Hour, The (2007) 4.0\n",
      "Memento (2000) 4.0\n",
      "The Hunger Games (2012) 4.0\n",
      "Ferris Bueller's Day Off (1986) 4.0\n",
      "Indiana Jones and the Last Crusade (1989) 4.0\n",
      "Ronin (1998) 4.0\n",
      "28 Weeks Later (2007) 4.0\n",
      "Naked (1993) 4.0\n",
      "Lord of War (2005) 4.0\n",
      "Cape Fear (1991) 4.0\n",
      "Whole Nine Yards, The (2000) 4.0\n",
      "Star Wars: Episode V - The Empire Strikes Back (1980) 4.0\n",
      "Star Wars: Episode I - The Phantom Menace (1999) 4.0\n",
      "Dances with Wolves (1990) 4.0\n",
      "Maltese Falcon, The (a.k.a. Dangerous Female) (1931) 4.0\n",
      "Kill Bill: Vol. 1 (2003) 4.0\n",
      "Deadpool (2016) 4.0\n",
      "Django Unchained (2012) 4.0\n",
      "Dark Knight Rises, The (2012) 4.0\n",
      "Boogie Nights (1997) 4.0\n",
      "Lord of the Rings: The Fellowship of the Ring, The (2001) 4.0\n",
      "The Imitation Game (2014) 4.0\n",
      "Dr. Strangelove or: How I Learned to Stop Worrying and Love the Bomb (1964) 4.0\n",
      "Sex, Lies, and Videotape (1989) 4.0\n",
      "Primary Colors (1998) 4.0\n",
      "Interstellar (2014) 4.0\n",
      "Mission: Impossible - Ghost Protocol (2011) 4.0\n",
      "Enemy of the State (1998) 4.0\n",
      "Red (2010) 4.0\n",
      "Silence of the Lambs, The (1991) 4.0\n",
      "Harry Potter and the Order of the Phoenix (2007) 4.0\n",
      "Gladiator (2000) 4.0\n",
      "Prometheus (2012) 4.0\n",
      "Deconstructing Harry (1997) 4.0\n",
      "Sleeper (1973) 4.0\n",
      "The Martian (2015) 4.0\n",
      "Rashomon (Rashômon) (1950) 4.0\n",
      "Cinderella Man (2005) 4.0\n",
      "City of God (Cidade de Deus) (2002) 4.0\n",
      "Lives of Others, The (Das leben der Anderen) (2006) 4.0\n",
      "Star Wars: Episode IV - A New Hope (1977) 4.0\n",
      "Pirates of the Caribbean: The Curse of the Black Pearl (2003) 4.0\n",
      "Independence Day (a.k.a. ID4) (1996) 4.0\n",
      "Dead Poets Society (1989) 4.0\n",
      "Edge of Tomorrow (2014) 4.0\n",
      "Metropolitan (1990) 4.0\n",
      "Maria Full of Grace (Maria, Llena eres de gracia) (2004) 4.0\n",
      "Pollock (2000) 4.0\n",
      "Elizabeth (1998) 4.0\n",
      "Whiplash (2014) 4.0\n",
      "Talladega Nights: The Ballad of Ricky Bobby (2006) 4.0\n",
      "Schindler's List (1993) 4.0\n",
      "Saving Private Ryan (1998) 4.0\n",
      "Crying Game, The (1992) 4.0\n",
      "Up (2009) 3.8333333333333335\n",
      "WALL·E (2008) 3.8333333333333335\n",
      "Grand Budapest Hotel, The (2014) 3.75\n",
      "American History X (1998) 3.75\n",
      "Mean Streets (1973) 3.5\n",
      "Cloud Atlas (2012) 3.5\n",
      "Carrie (1976) 3.5\n",
      "House of Games (1987) 3.5\n",
      "Ex Machina (2015) 3.5\n",
      "Bourne Ultimatum, The (2007) 3.5\n",
      "Illusionist, The (2006) 3.5\n",
      "Guardians of the Galaxy 2 (2017) 3.5\n",
      "Book of Eli, The (2010) 3.5\n",
      "Jerk, The (1979) 3.5\n",
      "Fargo (1996) 3.5\n",
      "10,000 BC (2008) 3.5\n",
      "Collateral (2004) 3.5\n",
      "Stranger than Fiction (2006) 3.5\n",
      "Spirited Away (Sen to Chihiro no kamikakushi) (2001) 3.5\n",
      "10 Things I Hate About You (1999) 3.5\n",
      "Troy (2004) 3.5\n",
      "Girl with the Dragon Tattoo, The (2011) 3.25\n",
      "Armageddon (1998) 3.0\n",
      "Blind Side, The  (2009) 3.0\n",
      "2 Fast 2 Furious (Fast and the Furious 2, The) (2003) 3.0\n",
      "30 Days of Night (2007) 3.0\n",
      "Wreck-It Ralph (2012) 3.0\n",
      "Rocky IV (1985) 3.0\n",
      "From Russia with Love (1963) 3.0\n",
      "American Graffiti (1973) 3.0\n",
      "Showgirls (1995) 3.0\n",
      "Silver Linings Playbook (2012) 3.0\n",
      "Midnight in the Garden of Good and Evil (1997) 3.0\n",
      "Zombieland (2009) 3.0\n",
      "Die Hard (1988) 3.0\n",
      "Exit Through the Gift Shop (2010) 3.0\n",
      "Adjustment Bureau, The (2011) 3.0\n",
      "Pursuit of Happyness, The (2006) 3.0\n",
      "Bowfinger (1999) 3.0\n",
      "Police Academy (1984) 3.0\n",
      "Captain America: The First Avenger (2011) 3.0\n",
      "Doors, The (1991) 3.0\n",
      "Toy Story (1995) 3.0\n",
      "William Shakespeare's Romeo + Juliet (1996) 3.0\n",
      "Thor: Ragnarok (2017) 3.0\n",
      "L.A. Confidential (1997) 3.0\n",
      "Karate Kid, Part II, The (1986) 3.0\n",
      "Rob Roy (1995) 3.0\n",
      "Omen, The (1976) 3.0\n",
      "Waking Ned Devine (a.k.a. Waking Ned) (1998) 3.0\n",
      "Léon: The Professional (a.k.a. The Professional) (Léon) (1994) 3.0\n",
      "Terminator, The (1984) 3.0\n",
      "Fletch (1985) 2.5\n",
      "Excalibur (1981) 2.5\n",
      "Mystery Men (1999) 2.5\n",
      "Kick-Ass (2010) 2.5\n",
      "Matilda (1996) 2.5\n",
      "Strange Brew (1983) 2.5\n",
      "Source Code (2011) 2.5\n",
      "Star Wars: Episode VII - The Force Awakens (2015) 2.5\n",
      "Michael (1996) 2.5\n",
      "100 Girls (2000) 2.5\n",
      "Looper (2012) 2.5\n",
      "Iron Man 2 (2010) 2.5\n",
      "Entrapment (1999) 2.5\n",
      "X-Men: First Class (2011) 2.25\n",
      "The Scorpion King (2002) 2.0\n",
      "Thinner (1996) 2.0\n",
      "Beverly Hills Cop III (1994) 2.0\n",
      "Twins (1988) 2.0\n",
      "Ace Ventura: Pet Detective (1994) 2.0\n",
      "Shaft (2000) 2.0\n",
      "Gremlins (1984) 2.0\n",
      "Dumbo (1941) 2.0\n",
      "Spy Hard (1996) 2.0\n",
      "E.T. the Extra-Terrestrial (1982) 2.0\n",
      "Leaving Las Vegas (1995) 2.0\n",
      "Batman Forever (1995) 2.0\n",
      "Harry and the Hendersons (1987) 2.0\n",
      "The Drop (2014) 2.0\n",
      "Big Momma's House (2000) 2.0\n",
      "My Best Friend's Wedding (1997) 2.0\n"
     ]
    }
   ],
   "source": [
    "ratings = memgraph.execute_and_fetch(\n",
    "    \"\"\"\n",
    "    MATCH (u1:User {id:608})-[r1:RATED]->(m:Movie)<-[r2:RATED]-(u2:User)\n",
    "    WITH u2.id AS u2_id, avg(abs(r1.rating-r2.rating)) AS similarity, count(*) AS similar_user_count\n",
    "    WHERE similar_user_count > 2\n",
    "    WITH u2_id\n",
    "    ORDER BY similarity\n",
    "    LIMIT 10\n",
    "    WITH collect(u2_id) AS similar_user_set\n",
    "    MATCH (m2: Movie)<-[r3:RATED]-(u3:User)\n",
    "    WHERE u3.id IN similar_user_set\n",
    "    WITH m2, avg(r3.rating) AS prediction_rating\n",
    "    RETURN m2.title AS title, prediction_rating\n",
    "    ORDER BY prediction_rating DESC;\n",
    "    \"\"\"\n",
    ")\n",
    "\n",
    "results = list(ratings)\n",
    "\n",
    "for result in results:\n",
    "    print(result[\"title\"], result[\"prediction_rating\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12a56e5b",
   "metadata": {},
   "source": [
    "**How does this query work?**\n",
    "\n",
    "This query has two parts:\n",
    "\n",
    "1. Finding similar users\n",
    "2. Predicting the score for some movie (recommendation)\n",
    "\n",
    "In the first part, we are looking for **similar users**. Two users are considered similar if they tend to give similar ratings to the same movies. For the target user (with `id` 608) and some other user we are searching for the same movies:\n",
    "\n",
    "```\n",
    "MATCH (u1:User {id:608})-[r1:RATED]->(m:Movie)<-[r2:RATED]-(u2:User)\n",
    "```\n",
    "\n",
    "But that is not enough to find similar users. We need to choose users with similar ratings:\n",
    "\n",
    "```\n",
    "WITH u2.id AS u2_id,\n",
    "     avg(abs(r1.rating-r2.rating)) AS similarity,\n",
    "     count(*) AS similar_user_count\n",
    "WHERE similar_user_count > 2\n",
    "WITH u2_id\n",
    "ORDER BY similarity\n",
    "LIMIT 10\n",
    "WITH collect(u2_id) AS similar_user_set\n",
    "```\n",
    "\n",
    "The similarity is being calculated as the average distance between target user rating and some other user rating on the same set of movies. The `similar_user_count` limit is used for filtering users who have at least 2 movies in common with the target user. The `similar_user_set` size is used to peek top 10 similar users (10 or less).\n",
    "\n",
    "We have a similar user set so we can calculate the prediction rating for all movies in the database:\n",
    "\n",
    "```\n",
    "MATCH (m2: Movie)<-[r3:RATED]-(u3:User)\n",
    "WHERE u3.id IN similar_user_set\n",
    "WITH m2, avg(r3.rating) AS prediction_rating\n",
    "RETURN m2.title AS title, prediction_rating\n",
    "ORDER BY prediction_rating DESC;\n",
    "```\n",
    "\n",
    "\n",
    "We encourage you to play with some parameters, like `similar_user_count` limit and `similar_user_set` size limit. You can also try to use different similarity functions, for example [Euclidean distance](https://en.wikipedia.org/wiki/Euclidean_distance). Then we would calculate the `similarity` like this:\n",
    "\n",
    "```\n",
    "sqrt(reduce(a=0, x IN collect((r1.rating - r2.rating) * (r1.rating - r2.rating)) | a + x)) AS similarity\n",
    "```\n",
    "\n",
    "### B. Graph analytics <a name=\"graph-analytics\"></a>\n",
    "\n",
    "We will perform the **PageRank** algorithm on our database and find out which movie is the most popular. Today, the most popular search engine in the world, Google, owes its popularity solely to this algorithm, developed in the early days by its founders. The PageRank algorithm outputs probability distribution used to represent the likelihood that a person randomly clicking on links will arrive at any particular page.\n",
    "\n",
    "We can calculate ranks using the Cypher query language:\n",
    "\n",
    "```\n",
    "CALL pagerank.get()\n",
    "YIELD *\n",
    "WITH node, rank\n",
    "WHERE node:Movie\n",
    "RETURN node.title AS movie_title, rank\n",
    "ORDER BY rank DESC\n",
    "LIMIT 50;\n",
    "```\n",
    "\n",
    "With GQLAlchemy's query builder, we have:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "59578757",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shawshank Redemption, The (1994) 0.00011449511199358699\n",
      "Forrest Gump (1994) 0.00010651712131114677\n",
      "Pulp Fiction (1994) 0.00010134433501352092\n",
      "Silence of the Lambs, The (1991) 9.558916408175443e-05\n",
      "Matrix, The (1999) 8.940861212633417e-05\n",
      "Braveheart (1995) 8.683898406973661e-05\n",
      "Schindler's List (1993) 8.365149873370419e-05\n",
      "Star Wars: Episode IV - A New Hope (1977) 7.926801065615451e-05\n",
      "Jurassic Park (1993) 7.781134959523956e-05\n",
      "Apollo 13 (1995) 7.707304242122513e-05\n",
      "Terminator 2: Judgment Day (1991) 7.557855706152259e-05\n",
      "Toy Story (1995) 7.146941002354837e-05\n",
      "Usual Suspects, The (1995) 7.094932228233453e-05\n",
      "Batman (1989) 7.079284188857586e-05\n",
      "Fight Club (1999) 7.032529107633532e-05\n",
      "Fugitive, The (1993) 7.016676306753367e-05\n",
      "Dances with Wolves (1990) 6.867780663492522e-05\n",
      "True Lies (1994) 6.790918650560843e-05\n",
      "Independence Day (a.k.a. ID4) (1996) 6.768041604771244e-05\n",
      "Seven (a.k.a. Se7en) (1995) 6.657816036585239e-05\n",
      "Lord of the Rings: The Fellowship of the Ring, The (2001) 6.559203427482653e-05\n",
      "Raiders of the Lost Ark (Indiana Jones and the Raiders of the Lost Ark) (1981) 6.506633555116242e-05\n",
      "Ace Ventura: Pet Detective (1994) 6.49972710010351e-05\n",
      "Aladdin (1992) 6.453057876246956e-05\n",
      "Lord of the Rings: The Two Towers, The (2002) 6.381982810512418e-05\n",
      "Twelve Monkeys (a.k.a. 12 Monkeys) (1995) 6.370851489622359e-05\n",
      "Godfather, The (1972) 6.34315494241871e-05\n",
      "American Beauty (1999) 6.341624258500746e-05\n",
      "Star Wars: Episode V - The Empire Strikes Back (1980) 6.303481350580366e-05\n",
      "Lord of the Rings: The Return of the King, The (2003) 6.257217225652917e-05\n",
      "Saving Private Ryan (1998) 6.233408941463855e-05\n",
      "Batman Forever (1995) 6.0396065499149476e-05\n",
      "Stargate (1994) 6.005057504336128e-05\n",
      "Inception (2010) 5.9904043202630825e-05\n",
      "Die Hard: With a Vengeance (1995) 5.98886776545936e-05\n",
      "Star Wars: Episode VI - Return of the Jedi (1983) 5.905431339019524e-05\n",
      "Speed (1994) 5.865996609085196e-05\n",
      "Fargo (1996) 5.820130848712502e-05\n",
      "Mission: Impossible (1996) 5.7461344442637726e-05\n",
      "Beauty and the Beast (1991) 5.701429974920845e-05\n",
      "Dark Knight, The (2008) 5.693797822555604e-05\n",
      "Dumb & Dumber (Dumb and Dumber) (1994) 5.642081340167846e-05\n",
      "Clear and Present Danger (1994) 5.5841753747034734e-05\n",
      "Gladiator (2000) 5.5713072945427415e-05\n",
      "Lion King, The (1994) 5.509300160329475e-05\n",
      "Mask, The (1994) 5.3496168715240865e-05\n",
      "Sixth Sense, The (1999) 5.346154041075886e-05\n",
      "GoldenEye (1995) 5.341811530957186e-05\n",
      "Crimson Tide (1995) 5.3018325565728164e-05\n",
      "Cliffhanger (1993) 5.240438210301545e-05\n"
     ]
    }
   ],
   "source": [
    "from gqlalchemy import call\n",
    "results = list(\n",
    "            call(\"pagerank.get\")\n",
    "            .yield_()\n",
    "            .with_({\"node\": \"node\", \"rank\": \"rank\"})\n",
    "            .add_custom_cypher(\"WHERE node:Movie\")\n",
    "            .return_({\"node.title\": \"movie_title\", \"rank\": \"rank\"})\n",
    "            .order_by(\"rank DESC\")\n",
    "            .limit(50)\n",
    "            .execute()\n",
    "        )\n",
    "\n",
    "for result in results:\n",
    "    print(result[\"movie_title\"], result[\"rank\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf0d3efd",
   "metadata": {},
   "source": [
    "That's it! Hopefully, you gained new knowledge and enjoyed this workshop. If you have any questions, don't hesitate to contact me directly or [join our Discord server](https://discord.gg/memgraph).\n",
    "\n",
    "> The solutions to all exercises are located in the [gqlalchemy-solutions notebook](../solutions/gqlalchemy-solutions.ipynb). The best way to learn is to solve the exercises yourself and then check out the answers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd518040",
   "metadata": {},
   "source": [
    "<h4 align=\"center\"><a href=#toc>⬆️ GO TO TOP ⬆️</a></h3> "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}