Querying - Practical Neo4j - page 39

WITH, ORDER BY, SKIP, and LIMIT
The clauses covered in this section are often used together to structure what is returned at different points in the
query. The WITH clause allows you to pass a subquery result on to the next part of the query and to manipulate the
data in some way before proceeding. In Listing 4-19, the WITH statement is used in conjunction with DISTINCT,
which removes duplicates from the values.
In many applications, it is impractical and inefficient to return the entire result set for a specific query. For
example, in a social graph application that contains status updates, it is likely that the users will only want to see the
latest updates and have a way to periodically retrieve previous ones. In addition, it is more performant to request a
specific result set and only later to retrieve subsequent subsets. Using the SKIP and LIMIT clauses allows for this to
happen quite easily.
Many applications require data to be ordered based on a specific property that exists on an entity, such as
alphabetical ordering of a list of users or second level of ordering on a linked list of status updates. Listing 4-19 shows
the clauses used together to retrieve updates in a specific user's status update feed.
Listing 4-19. Using WITH, ORDER BY, SKIP and LIMIT to Retrieve Status Updates
MATCH (u:User {username: {u} })-[:FOLLOWS*0..1]->f
WITH DISTINCT f,u
MATCH f-[:CURRENT]-lp-[:NEXT*0..]-su
RETURN su, f.username as username, f=u as owner
ORDER BY su.timestamp desc
SKIP {s}
LIMIT 4
Listing 4-20 shows the clauses used together to retrieve status updates in a specific user's status update feed,
adding an ORDER BY to the WITH clause.
Listing 4-20. Retreiving Status Udpates of a User and the Users Being Followed
MATCH (u:User {username: {u} })-[:FOLLOWS*0..1]->f
WITH DISTINCT f,u
ORDER BY u.username
MATCH f-[:CURRENT]-lp-[:NEXT*0..]-su
RETURN su, f.username as username, f=u as owner
ORDER BY su.timestamp desc
SKIP {s}
LIMIT 4
Using
When setting up a MATCH statement or WHERE clause with a Cypher query, Neo4j can use the property information
supplied in the query to determine an index that should be used to perform the look up. However, the index selected
by Neo4j might not be the best choice from a performance perspective because Cypher might begin the search in an
index that is not applicable to the search. As shown in Listings 4-21 and 4-22, the USING clause allows you to specify
an index that should be used (sometimes referred to as an index hint ).