It's time to share...Share on FacebookTweet about this on TwitterShare on LinkedInShare on Google+Pin on Pinterest

In the last week I tried  2.2 OrientDB version, thanks to Luigi Dell’Aquila and Luca Garulli that helped me to download WIP version.

The aim of this post is to explore the new MATCH command that I think is the big surprise of OrientDB 2.2 version that is going to come out. This command allow you to write easily “pattern matching” query, like Cypher language.

I started to see this documentation page then I tried this new command with MoviesRatings DB.

MovieRatings DB is the database schema I created in this post to create a recommendation engine with OrientDB. Now MovieRatings schema is one of the schemas available into “import public database” function of OrientDB (thanks to OrientDB staff), so it easy to import and to practice with it.

Import_database

Let’s try the new command in the MovieRatings schema.

Starting to write a simple query to practice with new command syntax:

MATCH {class:Movies, as:m, where (id=1)} return m

This query is equal to:

SELECT FROM Movies where id=1

The difference is that the Match query returns only @RID while traditional sql query returns all attributes.

Now we can write a more powerful query to retry all film reviewed by user 1 with a five stars rating:

MATCH {class:Users, as:me, where: (id=1)}
      .outE('rated'){as:my5stars, where: (rating=5)}
      .inV(){as:myMovies}
RETURN myMovies

Or if we want to see movies title :

MATCH {class:Users, as:me, where: (id=1)}
      .outE('rated'){as:my5stars, where: (rating=5)}
      .inV(){as:myMovies}
RETURN myMovies.title

Or:

SELECT myMovies.*
  FROM (MATCH {class:Users, as:me, where: (id=1)}
              .outE('rated'){as:my5stars, where: (rating=5)}
              .inV(){as:myMovies}
        RETURN myMovies)
ORDER BY myMovies.title

The last query mixes the MATCH instruction and the SQL in order to return “expanded” results ordered by title.

Now we can try a simple recommendation query to retrieve users similar to user 1. In particulary we search the users that gave the most 5 stars to the films to which the users 1 has given 5 stars:

SELECT similarUsers.id, count(*) as count5Stars
  FROM (MATCH {class:Users, as:me, where: (id=1)}
              .outE('rated'){where: (rating=5)}
              .inV(){as:myMovies}
              .inE('rated'){where: (rating=5 and out != $matched.me)}
              .outV(){as:similarUsers} 
        RETURN similarUsers)
  GROUP BY similarUsers.id
  ORDER BY count5Stars desc

See this instruction out != $matched.me where $matched.me represents the result of the expression MATCH {class:Users, as:me, where: (id=1)}.

The last query of this post create a recommendation by product like a typical e-commerce recommendation system. In this case we create a recommendation by Toystory lovers:

SELECT recMovies.title, count(*) as 5stars
  FROM (MATCH {class:Movies, as:toyStory, where: (@rid = #13:0)}
              .inE('rated'){where: (rating=5)}
              .outV(){as:toyStoryLovers}
              .outE('rated'){where: (rating=5 and in != $matched.toyStory)}
              .inV(){as: recMovies} return recMovies)
  GROUP BY recMovies.title 
  ORDER BY 5stars desc

Conclusions

I think that the MATCH command is very useful and is a good news for OrientDB users that needs pattern matching based solution. In this moment the gap between Neo4j about pattern matching has narrowed ( sooner OrientDB could use OpenCypher…).

The performances are not excellent but are good considering that I used a WIP alpha release where not all features have been developed. If in the future OrientDB developers will introduce more options (like group by or limit in json syntax of this command) I think that SQL + Pattern Matching syntax could be a game changer

It's time to share...Share on FacebookTweet about this on TwitterShare on LinkedInShare on Google+Pin on Pinterest