Understanding AGE SKIP and LIMIT Clauses.

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 a guide on installing PostgreSQL.
2. Installed and set up AGE from source code. Here's a guide on installing AGE.
3. Basic knowledge of Vertices and Edges in graph databases. Here's 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.
  

• 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.
  

• 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.
  

• 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.
  

• 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.
  
  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