Understanding AGE SKIP and LIMIT Clauses.
Tags: apacheage, postgres, c, database
Published: 2023-06-25 14:43:18

**Introduction:**

Apache AGE(AGE) is a PostgreSQL extension that enables hybrid queries using PostgreSQL. The `SKIP` clause in Apache AGE is used in excluding a specified number of vertices or edges while returning the remaining results. When combined with the `LIMIT` clause, it allows you to precisely control the number of output items from a graph query.

A function can be passed to the `SKIP` and `LIMIT` clauses in a query, it is important to pass only a function that produces a positive integer. The result of evaluating this function will determine the number of records that will be returned. Avoid passing negative values to these clauses, as it may lead to errors in the query execution.

**Prerequisites:**

Before following this tutorial, make sure you have:

1. Installed PostgreSQL from source code. [Here's](https://dev.to/chidera/step-by-step-guide-on-installing-wsl-and-postgresql-from-source-code-on-windows-5c84) a guide on installing PostgreSQL.
2. Installed and set up AGE from source code. [Here's](https://dev.to/chidera/age-installation-guide-for-windows-2c7p) a guide on installing AGE.
3. Basic knowledge of Vertices and Edges in graph databases. [Here's](https://dev.to/chidera/a-simple-guide-to-graph-databases-for-beginners-3log) a beginner's guide to get you started.

**Tutorial Steps:**

1. Create a graph called food using the code snippet below.
    ```
    SELECT create_graph('food');
    ```
2. Create eight different Vertices, with different properties and labels, using the lines of code below.
    ```
SELECT * FROM cypher('food', $$ CREATE (a:food_blog{name:"Tasty Delight",Description: "Tasty Dishes"}),(b:Recipes{name:"Spaghetti",Description:"Classic Italian Pasta dish with rich meat sauce",Difficulty:"Intermediate",Prep_time:"30 minutes"}),(c:Ingredients {name:"meat",name: "Spaghetti"}),(e:Cooking_techniques {name:"Baking"}),(f:Nutrition_tips{name:"Healthy eating"}),(g:Meal_planning {name:"Weekly meal plan"}),(h:Culinary_events {name:"Food festival",Description: "Annual celebration of local and International cuisines"}),(i:Kitchen_tools{name:"Chef's Knife"}) $$) AS (a agtype);
    ```   
3. View the newly created Vertices using the command below.
    ```
    SELECT * FROM cypher('food', $$ MATCH (n) RETURN n $$) AS (v agtype);
    ```

4. Create different Edges to connect our newly created Vertices.
    ```
 SELECT * FROM cypher('food', $$ MATCH (a:food_blog), (b:Recipes)
CREATE (a)-[e:HAS_CONTENT]->(b)RETURN e $$) as (e agtype);
 SELECT * FROM cypher('food', $$ MATCH (a:Recipes), (b:Ingredients) CREATE (a)-[e:BELONGS_TO]->(b)RETURN e $$) as (e agtype);
 SELECT * FROM cypher('food', $$ MATCH (a:food_blog), (b:Nutrition_tips) CREATE (a)-[e:HAS_CONTENT]->(b)RETURN e $$) as (e agtype);
SELECT * FROM cypher('food', $$ MATCH (a:food_blog), (b:Meal_planning)CREATE (a)-[e:HAS_CONTENT]->(b)RETURN e $$) as (e agtype);
SELECT * FROM cypher('food', $$ MATCH (a:Recipes), (b:Cooking_techniques)CREATE (a)-[e:USES_TECHNIQUE]->(b)RETURN e $$) as (e agtype);
SELECT * FROM cypher('food', $$ MATCH (a:food_blog), (b:Culinary_events) CREATE (a)-[e:HAS_CONTENT]->(b)RETURN e $$) as (e agtype);
SELECT * FROM cypher('food', $$ MATCH (a:food_blog), (b:Kitchen_tools)CREATE (a)-[e:HAS_CONTENT]->(b)RETURN e $$) as (e agtype);
SELECT * FROM cypher('food', $$ MATCH (a:Recipes,(b:Culinary_events)CREATE (a)-[e:PARTICIPATES_IN]->(b)RETURN e $$) as (e agtype);

    ```
5. View the created Edges with this code below.
    ```
 SELECT * FROM cypher('food', $$ MATCH p = (v)-[*]->(b) RETURN relationships(p) $$) as (v agtype);
    ```
**Querying the Vertices.**

- To query the vertices, you can use the `SKIP` clause in combination with other clauses and functions. For example, you can retrieve the first five vertices by modifying the given query to include the SKIP clause.
    ```
SELECT * FROM cypher('food', $$ MATCH (n) RETURN n ORDER BY n SKIP 3 $$) as (names agtype);
    ```
- The query above will give you the output below.
![Image showing the returned value from above query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/corpcd7xr468tyeys6e8.jpg)

- Make a query to return just two Vertices by adding the `LIMIT` clause.
    ```
 SELECT * FROM cypher('food', $$ MATCH (n) RETURN n ORDER BY n SKIP 3  LIMIT 2 $$) as (names agtype);
    ```
- The query above will give you the output below.
![Image showing the returned value from above query](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cfpm90t7yibw580osqxk.jpg)
- Add functions to the `LIMIT` to further modify the output as shown in the query below, the result of this function is evaluated and because it's positive 3 records were returned from the graph.
    ```
SELECT * FROM cypher('food', $$ MATCH (n) RETURN n ORDER BY n SKIP 3  LIMIT round(2.5) $$) as (names agtype);
    ```
- The query above will give you the output below.
![Image showing the returned values](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8ycifspd0z2nuz0zxri0.jpg)

- Similarly, a negative value passed to the `LIMIT` or `SKIP` clauses result in an error, as seen with the query below.
    ```
SELECT * FROM cypher('food', $$ MATCH (n) RETURN n ORDER BY n SKIP 3  LIMIT -2.5 $$) as (names agtype);
ERROR:  LIMIT must not be negative
    ```  
**Querying the Edges.**
- To return just 7 Edges, run the command below.  
    ```
SELECT * FROM cypher('food', $$ MATCH p = (v)-[*]->(b) RETURN relationships(p) SKIP 4  $$) as (v agtype);
    ```
- The query above will give you the output below.
![Image showing the returned edges from the query above](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1qympg3eqmq00amxvkkh.jpg)
- To get just four edges with Vertices `food_blog` and other Vertices, run the command below.
    ```
SELECT * FROM cypher('food', $$ MATCH p = (v:food_blog)-[*]->(b) RETURN relationships(p) SKIP 3  $$) as (v agtype);
    ```
- The query above will give you the output below.
![Image showing the returned output from the query above](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w11mkqxqvuofmqhamf2t.jpg)
**Conclusion.**
The `SKIP` clause in AGE allows you to exclude a specified number of vertices or edges from the query results. By utilizing it alongside the `LIMIT` clause, you can precisely control the number of output items returned from a graph query. 

**References**
https://age.apache.org/age-manual/master/clauses/skip.html
https://age.apache.org/age-manual/master/clauses/limit.html