gqlalchemy-workshop/workshop/gqlalchemy-workshop.ipynb

{
   "cells": [
      {
         "cell_type": "markdown",
         "id": "fad76b97",
         "metadata": {},
         "source": [
            "<h1 align=\"center\">Getting started with Memgraph 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/) - a 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 7444:7444 -p 3000:3000 memgraph/memgraph-platform:2.4.0\n",
            ">```\n",
            ">\n",
            ">**Memgraph Platform** contains:\n",
            ">\n",
            ">- **MemgraphDB** - 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 in the [requirements.txt](https://github.com/memgraph/jupyter-memgraph-tutorials/blob/main/gqlalchemy-workshop/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 the `movies.csv` and `ratings.csv` files look like."
         ]
      },
      {
         "cell_type": "code",
         "execution_count": 28,
         "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": 28,
               "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": 29,
         "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": 29,
               "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 in the `movies.csv` file has a title and genre it belongs to. Therefore, let's create `:Movie` and `:Genre` nodes and a relationship between them of the type `OF_GENRE`. The `:Movie` nodes will have `id` and `title` properties, while `:Genre` nodes will just have the `name` property. \n",
            "\n",
            "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`. The `:User` node only has the `id` property. In the image below, we have the graph model (schema).\n",
            "\n",
            "<img alt=\"movies_graph_model\" src=\"https://raw.githubusercontent.com/memgraph/jupyter-memgraph-tutorials/main/gqlalchemy-workshop/img/movie-schema.png\" width=\"800\" />"
         ]
      },
      {
         "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==1.3.2\n",
            "```\n",
            "\n",
            "> You may need to install [CMake](https://cmake.org/download/) before installing GQLAlchemy."
         ]
      },
      {
         "cell_type": "code",
         "execution_count": 30,
         "id": "e91e3efe",
         "metadata": {},
         "outputs": [],
         "source": [
            "from gqlalchemy import Memgraph"
         ]
      },
      {
         "cell_type": "code",
         "execution_count": 31,
         "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": 32,
         "id": "9f68fcb0",
         "metadata": {},
         "outputs": [],
         "source": [
            "memgraph.drop_database()"
         ]
      },
      {
         "cell_type": "code",
         "execution_count": 33,
         "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": 34,
         "id": "53c11afe",
         "metadata": {
            "scrolled": false
         },
         "outputs": [],
         "source": [
            "from typing import Optional\n",
            "from gqlalchemy import Node, Relationship, Field\n",
            "\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(unique=True, db=memgraph)\n",
            "\n",
            "        \n",
            "class Genre(Node):\n",
            "    name: str = Field()\n",
            "\n",
            "        \n",
            "class Rated(Relationship, type=\"RATED\"):\n",
            "    rating: float = Field()\n",
            "    timestamp: float = Field()\n",
            "\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 an 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": 35,
         "id": "0dfccf29",
         "metadata": {
            "scrolled": true
         },
         "outputs": [
            {
               "name": "stdout",
               "output_type": "stream",
               "text": [
                  "<User id=0 labels={'User'} properties={'id': 1, 'name': 'Katarina'}>\n",
                  "<Movie id=1 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": 36,
         "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": 37,
         "id": "43e50fd0",
         "metadata": {
            "scrolled": true
         },
         "outputs": [
            {
               "name": "stdout",
               "output_type": "stream",
               "text": [
                  "<Rated id=0 start_node_id=0 end_node_id=1 nodes=(0, 1) type=RATED properties={'rating': 5.0, 'timestamp': 1664207598.3194408}>\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": "787855a4",
         "metadata": {},
         "source": [
            "---\n",
            "\n",
            "# ☕ Let's take a small break - 15 mins\n",
            "\n",
            "---"
         ]
      },
      {
         "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": 38,
         "id": "dae2543a",
         "metadata": {},
         "outputs": [],
         "source": [
            "memgraph.drop_database()"
         ]
      },
      {
         "cell_type": "markdown",
         "id": "5b465fca",
         "metadata": {},
         "source": [
            "Now we nee to import `movies.csv` and `ratings.csv` files into Memgraph, by copying them from the [data folder](../data) into the Docker container where Memgraph is running. First, place yourself into the data folder. Next, 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": 39,
         "id": "c40e8239",
         "metadata": {},
         "outputs": [],
         "source": [
            "memgraph.execute(\n",
            "    \"\"\"\n",
            "    LOAD CSV FROM \"/movies.csv\" WITH HEADER AS row\n",
            "    CREATE (m:Movie {id: toInteger(row.movieId), title: row.title}) \n",
            "    WITH m, row\n",
            "    UNWIND split(row.genres, '|') AS genre\n",
            "    CREATE (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": 40,
         "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": 41,
         "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": 42,
         "id": "87dfd43d",
         "metadata": {
            "scrolled": false
         },
         "outputs": [
            {
               "name": "stdout",
               "output_type": "stream",
               "text": [
                  "<Movie id=8603 labels={'Movie'} properties={'id': 117867, 'title': \"'71 (2014)\"}>\n",
                  "<Movie id=8017 labels={'Movie'} properties={'id': 97757, 'title': \"'Hellboy': The Seeds of Creation (2004)\"}>\n",
                  "<Movie id=5531 labels={'Movie'} properties={'id': 26564, 'title': \"'Round Midnight (1986)\"}>\n",
                  "<Movie id=5693 labels={'Movie'} properties={'id': 27751, 'title': \"'Salem's Lot (2004)\"}>\n",
                  "<Movie id=617 labels={'Movie'} properties={'id': 779, 'title': \"'Til There Was You (1997)\"}>\n",
                  "<Movie id=9186 labels={'Movie'} properties={'id': 149380, 'title': \"'Tis the Season for Love (2015)\"}>\n",
                  "<Movie id=1540 labels={'Movie'} properties={'id': 2072, 'title': \"'burbs, The (1989)\"}>\n",
                  "<Movie id=2356 labels={'Movie'} properties={'id': 3112, 'title': \"'night Mother (1986)\"}>\n",
                  "<Movie id=7078 labels={'Movie'} properties={'id': 69757, 'title': '(500) Days of Summer (2009)'}>\n",
                  "<Movie id=5125 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": 43,
         "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": 44,
         "id": "f8721836",
         "metadata": {
            "scrolled": false
         },
         "outputs": [
            {
               "name": "stdout",
               "output_type": "stream",
               "text": [
                  "<Movie id=8603 labels={'Movie'} properties={'id': 117867, 'title': \"'71 (2014)\"}>\n",
                  "<Movie id=8017 labels={'Movie'} properties={'id': 97757, 'title': \"'Hellboy': The Seeds of Creation (2004)\"}>\n",
                  "<Movie id=5531 labels={'Movie'} properties={'id': 26564, 'title': \"'Round Midnight (1986)\"}>\n",
                  "<Movie id=5693 labels={'Movie'} properties={'id': 27751, 'title': \"'Salem's Lot (2004)\"}>\n",
                  "<Movie id=617 labels={'Movie'} properties={'id': 779, 'title': \"'Til There Was You (1997)\"}>\n",
                  "<Movie id=9186 labels={'Movie'} properties={'id': 149380, 'title': \"'Tis the Season for Love (2015)\"}>\n",
                  "<Movie id=1540 labels={'Movie'} properties={'id': 2072, 'title': \"'burbs, The (1989)\"}>\n",
                  "<Movie id=2356 labels={'Movie'} properties={'id': 3112, 'title': \"'night Mother (1986)\"}>\n",
                  "<Movie id=7078 labels={'Movie'} properties={'id': 69757, 'title': '(500) Days of Summer (2009)'}>\n",
                  "<Movie id=5125 labels={'Movie'} properties={'id': 8169, 'title': '*batteries not included (1987)'}>\n"
               ]
            }
         ],
         "source": [
            "from gqlalchemy import match\n",
            "from gqlalchemy.query_builders.memgraph_query_builder import Order\n",
            "\n",
            "results_from_qb = (\n",
            "    match()\n",
            "    .node(labels=\"Movie\", variable=\"m\")\n",
            "    .return_()\n",
            "    .order_by((\"m.title\", Order.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\"])"
         ]
      },
      {
         "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": 45,
         "id": "cffbded6",
         "metadata": {
            "scrolled": true
         },
         "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": 46,
         "id": "9ee1b6ce",
         "metadata": {
            "scrolled": true
         },
         "outputs": [
            {
               "name": "stdout",
               "output_type": "stream",
               "text": [
                  "116\n"
               ]
            }
         ],
         "source": [
            "from gqlalchemy.query_builders.memgraph_query_builder import Operator\n",
            "\n",
            "home_alone = (\n",
            "    match()\n",
            "    .node(labels=\"User\", variable=\"u\")\n",
            "    .to(relationship_type=\"RATED\")\n",
            "    .node(labels=\"Movie\", variable=\"m\")\n",
            "    .where(item=\"m.title\", operator=Operator.EQUAL, literal=\"Home Alone (1990)\")\n",
            "    .return_((\"count(u)\", \"num_of_users\"))\n",
            "    .execute()\n",
            ")\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 how many genres the movie 'Matrix, The (1999)' is connected to.\n",
            "\n",
            "***\n",
            "\n",
            "### 🔷 Example 3: Find the genres the movie 'Blue Lagoon, The (1980)' is connected 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": 47,
         "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(relationship_type=\"OF_GENRE\")\n",
            "    .node(labels=\"Genre\", variable=\"g\")\n",
            "    .where(item=\"m.title\", operator=Operator.EQUAL, literal=\"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": 48,
         "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(relationship_type=\"OF_GENRE\")\n",
            "    .node(labels=\"Genre\", variable=\"g1\")\n",
            "    .where(item=\"g1.name\", operator=Operator.EQUAL, literal=\"Action\")\n",
            "    .match()\n",
            "    .node(labels=\"Movie\", variable=\"m\")\n",
            "    .to(relationship_type=\"OF_GENRE\")\n",
            "    .node(labels=\"Genre\", variable=\"g2\")\n",
            "    .where(item=\"g2.name\", operator=Operator.EQUAL, literal=\"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 movie 'Star Wars: Episode IV - A New Hope (1977)'.\n",
            "\n",
            "Here we're going to use one of the **Cypher functions**. We are going to use the aggregation `avg` function, which 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": 49,
         "id": "536c3eaa",
         "metadata": {},
         "outputs": [
            {
               "name": "stdout",
               "output_type": "stream",
               "text": [
                  "4.231075697211155\n"
               ]
            }
         ],
         "source": [
            "rating = (\n",
            "    match()\n",
            "    .node(labels=\"User\")\n",
            "    .to(relationship_type=\"RATED\", variable=\"r\")\n",
            "    .node(labels=\"Movie\", variable=\"m\")\n",
            "    .where(item=\"m.title\", operator=Operator.EQUAL, literal=\"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 movie 'Star Wars: Episode I - The Phantom Menace (1999)'."
         ]
      },
      {
         "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) query. The memory-based approach uses user rating data to compute the similarity between users or items."
         ]
      },
      {
         "cell_type": "code",
         "execution_count": 50,
         "id": "43e2f45a",
         "metadata": {},
         "outputs": [
            {
               "name": "stdout",
               "output_type": "stream",
               "text": [
                  "Inside Job (2010) 5.0\n",
                  "Usual Suspects, The (1995) 5.0\n",
                  "Manhattan (1979) 5.0\n",
                  "Manhattan Murder Mystery (1993) 5.0\n",
                  "Godfather, The (1972) 5.0\n",
                  "Into the Wild (2007) 5.0\n",
                  "Wolf of Wall Street, The (2013) 5.0\n",
                  "Annie Hall (1977) 5.0\n",
                  "Pan's Labyrinth (Laberinto del fauno, El) (2006) 5.0\n",
                  "The Jinx: The Life and Deaths of Robert Durst (2015) 5.0\n",
                  "Step Brothers (2008) 5.0\n",
                  "Mad Max: Fury Road (2015) 5.0\n",
                  "Minority Report (2002) 5.0\n",
                  "Avengers, The (2012) 5.0\n",
                  "Shakespeare in Love (1998) 5.0\n",
                  "Matrix, The (1999) 5.0\n",
                  "Rain Man (1988) 5.0\n",
                  "Princess Bride, The (1987) 5.0\n",
                  "Goodfellas (1990) 5.0\n",
                  "Godfather: Part II, The (1974) 5.0\n",
                  "Big Fish (2003) 5.0\n",
                  "Payback (1999) 5.0\n",
                  "Lord of the Rings: The Two Towers, The (2002) 5.0\n",
                  "Harry Potter and the Deathly Hallows: Part 1 (2010) 5.0\n",
                  "Beautiful Mind, A (2001) 5.0\n",
                  "Catch Me If You Can (2002) 5.0\n",
                  "Chungking Express (Chung Hing sam lam) (1994) 5.0\n",
                  "Piano, The (1993) 5.0\n",
                  "Iron Giant, The (1999) 5.0\n",
                  "Intouchables (2011) 5.0\n",
                  "Harry Potter and the Deathly Hallows: Part 2 (2011) 5.0\n",
                  "Despicable Me (2010) 5.0\n",
                  "Gone Girl (2014) 5.0\n",
                  "Like Water for Chocolate (Como agua para chocolate) (1992) 5.0\n",
                  "Brazil (1985) 5.0\n",
                  "Young Frankenstein (1974) 5.0\n",
                  "Out of Sight (1998) 5.0\n",
                  "Green Mile, The (1999) 4.75\n",
                  "Iron Man (2008) 4.75\n",
                  "Dark Knight, The (2008) 4.625\n",
                  "Fight Club (1999) 4.625\n",
                  "Inception (2010) 4.5\n",
                  "Casino Royale (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",
                  "Coraline (2009) 4.5\n",
                  "Lord of the Rings: The Return of the King, The (2003) 4.5\n",
                  "Sixth Sense, The (1999) 4.5\n",
                  "Town, The (2010) 4.5\n",
                  "Gran Torino (2008) 4.5\n",
                  "Good Will Hunting (1997) 4.5\n",
                  "Skyfall (2012) 4.5\n",
                  "Iron Man 3 (2013) 4.5\n",
                  "District 9 (2009) 4.5\n",
                  "Warrior (2011) 4.5\n",
                  "Harry Potter and the Goblet of Fire (2005) 4.5\n",
                  "Hannah and Her Sisters (1986) 4.5\n",
                  "10th & Wolf (2006) 4.5\n",
                  "Gravity (2013) 4.5\n",
                  "13th Warrior, The (1999) 4.5\n",
                  "French Connection, The (1971) 4.5\n",
                  "Eat Drink Man Woman (Yin shi nan nu) (1994) 4.5\n",
                  "Harry Potter and the Prisoner of Azkaban (2004) 4.5\n",
                  "Sherlock Holmes: A Game of Shadows (2011) 4.5\n",
                  "How to Train Your Dragon (2010) 4.333333333333333\n",
                  "Truman Show, The (1998) 4.25\n",
                  "Big Hero 6 (2014) 4.25\n",
                  "Inglourious Basterds (2009) 4.25\n",
                  "Streetcar Named Desire, A (1951) 4.25\n",
                  "Shutter Island (2010) 4.166666666666667\n",
                  "Indiana Jones and the Last Crusade (1989) 4.0\n",
                  "Ferris Bueller's Day Off (1986) 4.0\n",
                  "Harry Potter and the Order of the Phoenix (2007) 4.0\n",
                  "Schindler's List (1993) 4.0\n",
                  "The Hunger Games (2012) 4.0\n",
                  "Shawshank Redemption, The (1994) 4.0\n",
                  "Ronin (1998) 4.0\n",
                  "Deconstructing Harry (1997) 4.0\n",
                  "Jungle Book, The (1967) 4.0\n",
                  "Tommy Boy (1995) 4.0\n",
                  "Cinderella Man (2005) 4.0\n",
                  "V for Vendetta (2006) 4.0\n",
                  "Game, The (1997) 4.0\n",
                  "Mission: Impossible - Ghost Protocol (2011) 4.0\n",
                  "Memento (2000) 4.0\n",
                  "Maltese Falcon, The (a.k.a. Dangerous Female) (1931) 4.0\n",
                  "11th Hour, The (2007) 4.0\n",
                  "Rashomon (Rashômon) (1950) 4.0\n",
                  "Sex, Lies, and Videotape (1989) 4.0\n",
                  "Silence of the Lambs, The (1991) 4.0\n",
                  "Star Wars: Episode V - The Empire Strikes Back (1980) 4.0\n",
                  "Whiplash (2014) 4.0\n",
                  "City of God (Cidade de Deus) (2002) 4.0\n",
                  "Dances with Wolves (1990) 4.0\n",
                  "Cape Fear (1991) 4.0\n",
                  "Pulp Fiction (1994) 4.0\n",
                  "Naked (1993) 4.0\n",
                  "Kill Bill: Vol. 1 (2003) 4.0\n",
                  "The Martian (2015) 4.0\n",
                  "Sleeper (1973) 4.0\n",
                  "Lord of War (2005) 4.0\n",
                  "Star Wars: Episode I - The Phantom Menace (1999) 4.0\n",
                  "Django Unchained (2012) 4.0\n",
                  "Dark Knight Rises, The (2012) 4.0\n",
                  "28 Weeks Later (2007) 4.0\n",
                  "Louis C.K.: Hilarious (2010) 4.0\n",
                  "Megamind (2010) 4.0\n",
                  "Pirates of the Caribbean: The Curse of the Black Pearl (2003) 4.0\n",
                  "Metropolitan (1990) 4.0\n",
                  "Lives of Others, The (Das leben der Anderen) (2006) 4.0\n",
                  "Saving Private Ryan (1998) 4.0\n",
                  "Faculty, The (1998) 4.0\n",
                  "Crying Game, The (1992) 4.0\n",
                  "Lord of the Rings: The Fellowship of the Ring, The (2001) 4.0\n",
                  "The Imitation Game (2014) 4.0\n",
                  "Deadpool (2016) 4.0\n",
                  "Tank Girl (1995) 4.0\n",
                  "Primary Colors (1998) 4.0\n",
                  "Departed, The (2006) 4.0\n",
                  "28 Days Later (2002) 4.0\n",
                  "Whole Nine Yards, The (2000) 4.0\n",
                  "Prometheus (2012) 4.0\n",
                  "Interstellar (2014) 4.0\n",
                  "Dr. Strangelove or: How I Learned to Stop Worrying and Love the Bomb (1964) 4.0\n",
                  "Prestige, The (2006) 4.0\n",
                  "Enemy of the State (1998) 4.0\n",
                  "Red (2010) 4.0\n",
                  "Gladiator (2000) 4.0\n",
                  "Talladega Nights: The Ballad of Ricky Bobby (2006) 4.0\n",
                  "Independence Day (a.k.a. ID4) (1996) 4.0\n",
                  "Boogie Nights (1997) 4.0\n",
                  "Elizabeth (1998) 4.0\n",
                  "Pollock (2000) 4.0\n",
                  "Maria Full of Grace (Maria, Llena eres de gracia) (2004) 4.0\n",
                  "Star Wars: Episode IV - A New Hope (1977) 4.0\n",
                  "Dead Poets Society (1989) 4.0\n",
                  "Edge of Tomorrow (2014) 4.0\n",
                  "11:14 (2003) 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",
                  "Ex Machina (2015) 3.5\n",
                  "10 Things I Hate About You (1999) 3.5\n",
                  "Carrie (1976) 3.5\n",
                  "10,000 BC (2008) 3.5\n",
                  "Mean Streets (1973) 3.5\n",
                  "Collateral (2004) 3.5\n",
                  "Bourne Ultimatum, The (2007) 3.5\n",
                  "House of Games (1987) 3.5\n",
                  "Spirited Away (Sen to Chihiro no kamikakushi) (2001) 3.5\n",
                  "Fargo (1996) 3.5\n",
                  "Illusionist, The (2006) 3.5\n",
                  "Troy (2004) 3.5\n",
                  "Cloud Atlas (2012) 3.5\n",
                  "Guardians of the Galaxy 2 (2017) 3.5\n",
                  "Stranger than Fiction (2006) 3.5\n",
                  "Jerk, The (1979) 3.5\n",
                  "Book of Eli, The (2010) 3.5\n",
                  "Girl with the Dragon Tattoo, The (2011) 3.25\n",
                  "Bowfinger (1999) 3.0\n",
                  "Rocky IV (1985) 3.0\n",
                  "Thor: Ragnarok (2017) 3.0\n",
                  "Blind Side, The  (2009) 3.0\n",
                  "Rob Roy (1995) 3.0\n",
                  "Omen, The (1976) 3.0\n",
                  "Silver Linings Playbook (2012) 3.0\n",
                  "Police Academy (1984) 3.0\n",
                  "Showgirls (1995) 3.0\n",
                  "Pursuit of Happyness, The (2006) 3.0\n",
                  "Toy Story (1995) 3.0\n",
                  "Captain America: The First Avenger (2011) 3.0\n",
                  "From Russia with Love (1963) 3.0\n",
                  "Exit Through the Gift Shop (2010) 3.0\n",
                  "Armageddon (1998) 3.0\n",
                  "Adjustment Bureau, The (2011) 3.0\n",
                  "2 Fast 2 Furious (Fast and the Furious 2, The) (2003) 3.0\n",
                  "Karate Kid, Part II, The (1986) 3.0\n",
                  "Zombieland (2009) 3.0\n",
                  "Waking Ned Devine (a.k.a. Waking Ned) (1998) 3.0\n",
                  "William Shakespeare's Romeo + Juliet (1996) 3.0\n",
                  "Midnight in the Garden of Good and Evil (1997) 3.0\n",
                  "Wreck-It Ralph (2012) 3.0\n",
                  "Doors, The (1991) 3.0\n",
                  "Die Hard (1988) 3.0\n",
                  "American Graffiti (1973) 3.0\n",
                  "30 Days of Night (2007) 3.0\n",
                  "L.A. Confidential (1997) 3.0\n",
                  "Léon: The Professional (a.k.a. The Professional) (Léon) (1994) 3.0\n",
                  "Terminator, The (1984) 3.0\n",
                  "Source Code (2011) 2.5\n",
                  "Mystery Men (1999) 2.5\n",
                  "Strange Brew (1983) 2.5\n",
                  "Iron Man 2 (2010) 2.5\n",
                  "Kick-Ass (2010) 2.5\n",
                  "Matilda (1996) 2.5\n",
                  "Fletch (1985) 2.5\n",
                  "Star Wars: Episode VII - The Force Awakens (2015) 2.5\n",
                  "100 Girls (2000) 2.5\n",
                  "Excalibur (1981) 2.5\n",
                  "Looper (2012) 2.5\n",
                  "Entrapment (1999) 2.5\n",
                  "Michael (1996) 2.5\n",
                  "X-Men: First Class (2011) 2.25\n",
                  "E.T. the Extra-Terrestrial (1982) 2.0\n",
                  "Thinner (1996) 2.0\n",
                  "The Scorpion King (2002) 2.0\n",
                  "Twins (1988) 2.0\n",
                  "Shaft (2000) 2.0\n",
                  "Beverly Hills Cop III (1994) 2.0\n",
                  "Gremlins (1984) 2.0\n",
                  "Ace Ventura: Pet Detective (1994) 2.0\n",
                  "Dumbo (1941) 2.0\n",
                  "Spy Hard (1996) 2.0\n",
                  "Leaving Las Vegas (1995) 2.0\n",
                  "My Best Friend's Wedding (1997) 2.0\n",
                  "Big Momma's House (2000) 2.0\n",
                  "Batman Forever (1995) 2.0\n",
                  "Harry and the Hendersons (1987) 2.0\n",
                  "The Drop (2014) 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 users, 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 the 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": 51,
         "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\", \"rank\"])\n",
            "            .where(item=\"node\", operator=Operator.LABEL_FILTER, expression=\"Movie\")\n",
            "            .return_([(\"node.title\", \"movie_title\"), \"rank\"])\n",
            "            .order_by((\"rank\", Order.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.12"
      }
   },
   "nbformat": 4,
   "nbformat_minor": 5
}