I am just starting out with nosql databases, and OrientDB in particular. My prior experience is with relational databases, mostly with SQL Server.
In MSSQL, I can do something like this:
SELECT s.url, p.title
FROM Site s
JOIN Page p ON s.Id = p.SiteId
And it will give me a table of all pages, along with the url of the site they belong to:
url | title
----------------------------
site1.com | page1
site1.com | page2
site2.com | page1
Now, in OrientDb, my understanding is that you should use a link for a one-way relationship, or an edge for a bidirectional relationship. Since I want to know what pages belong to a site, as well as what site a particular page belongs to, I have decided to use an edge in this case. The Site and Page classes/vertices are already created in similar fashion, but I can't figure out how to get a similar result set. From the documentation (https://orientdb.com/docs/2.2/SQL.html):
OrientDB allows only one class (classes are equivalent to tables in this discussion) as opposed to SQL, which allows for many tables as the target. If you want to select from 2 classes, you have to execute 2 sub queries and join them with the UNIONALL function
Their example SELECT FROM E, V then becomes SELECT EXPAND( $c ) LET $a = ( SELECT FROM E ), $b = ( SELECT FROM V ), $c = UNIONALL( $a, $b ), but that's not what I want. That results in something along the lines of
url | title
----------------------------
site1.com |
site1.com |
site2.com |
| page1
| page2
| page1
How would I go about creating the original result set, like in MSSQL?
Additional consideration: My training and experience with MSSQL dictates that database operations should be done in the database, rather than the application code. For example, I could have done one database call to get the s.url and s.id fields, then a second call to get the p.title and p.SiteId fields, and then matched them up in application code. The reason I avoid this is because multiple database calls is less efficient time-wise than the time it takes to return the extra/redundant information (in my example, site1.com is returned twice).
Is this perhaps not the case for OrientDb, or even graph/nosql databases in general? Should I instead be making two separate calls to get all of the data I need, i.e. SELECT FROM Site WHERE Url = "site1.com" AND SELECT EXPAND(OUT("HasPages")) FROM Site WHERE Name = "site1.com"?
Thank you
Try this:
select Url, out("HasPages").title as title from Site unwind title
Hope it helps
Regards