In the last post, I have introduced you to a simple ETL use case for graph database like Orient DB. If you haven’t read it, I suggest you read this - OrientDB A simple use case note.
After loading data, you might want to play around with the graph structure and its possible traversal logic. As it is easy to represent the semantic relationships between them, the queries we will write also be designed based on the logic we come up with. In the last post, I have provided the query to find out the books bought by the buyers he know or befriended. In this post, I will provide some more simple examples to query such a graph in Orient DB. Here I a using the native query supported by the database.
How do we find out the books bought by a buyer named ‘Hary’?
select @rid, title from (select expand(out('Bought')) from Buyer where name='Hary')
Here this query will return the RecordId of the format <<cluster: position>> form. In OrientDB each record has its own self-assigned unique ID within the database called Record ID or RID. cluster-id is the id of the cluster and cluster-position is the position of the record inside the cluster. You can consider a cluster as a Table where each class (say, Buyer) of records are stored. Here the subquery uses expand function to expand the collection in the field and use it as result. It will fetch the records linked to the edge ‘Bought’.
How do we find out the people ‘Hary’ knows?
select expand(out('Knows')) from Buyer where name='Hary'
Find out the books bought by friends of Hary?
select title from ( select expand(out('Bought')) from (select expand(out('Knows') ) from Buyer where name='Hary'))
Here we combined both of the queries above it to make a logical decision as the interlinking of vertices is clearly identified.
Find out books bought by Hary but not by his friends, so that we can recommend some?
select title from (select expand(out('Bought')) from Buyer where name='Hary') let $temp = ( select title from ( select expand(out('Bought')) from ( select expand(out('Knows')) from Buyer where name='Hary' ) ) )where title not in $temp
Here we used LET to assign the results of a subquery. In the subquery, we find the books bought by Hary’s friends. Then we find the books bought by Hary but not by friends.
Find out the books who also bought a book like The Shining? This is a common use case for recommend links where we may want to list the similar products bought by people who is about to buy the displayed product.
select expand(inE('Bought').outV().OutE('Bought').inV().title) from Book where title not in ['The Shining']