Can not get sub-query with variable to work. Some help would be great!

[Julian Liebl]

Hi,

I am new to orientdb. Working with it since 2 weeks and it feels just great. However there a few things I still need to learn and I can't find a sample or an explanation which solves my problem. I have searched a lot (4 hours + now).

Here is a break down of the two querys I execute. My goal is to merge these into one query:

select from FUser where userId = "642255" //userId is unique and returns the following rid -> #10:47158
select from FOrder where user = #10:47158

To reach my goal I tried the following querys: select from FOrder LET $user = (select @rid from FUser where userId = "642255") where user = $user 

This however does not seem to work. It does not give an error but also does not return any result. Can someone point me in the right direction?

Thank you for any help!
Have a nice day,

Julian

[Luigi Dell'Aquila]

Hi Julian,

first of all let's make your query work, this is the right one:

select from FOrder where user IN (select @rid from FUser where userId = "642255")

Please note that I used IN operator instead of = because a query returns a result set, that is a collection.

But all this is just for the record, now let's move to the real problem. You are using OrientDB as if it were a relational database, in the end you are doing a JOIN (!!!) while you could have direct links (document) or edges (graph).

If you change your domain to be more "graph", you would have this (just an example)

Class FOrder extends V (vertex)

Class FUser extends V (vertex)

Class Ordered extends E (edge)

your data structure would be something like

FUser --Ordered--> FOrder

and your query would be something like

select out("Ordered") from FUser where where userId = "642255"

This query, in addition to being more readable, is also more efficient than the previous one, because it relies on an index only to find the user based on its userId, and then uses the "Ordered" edge to find linked orders. This last operation (the graph traversal) is a constant time operation ( O(1), while index search is O(log N) ), that means that having a graph with 10 vertexes or having one of 10.000.000.000 is exactly the same in terms of traversal time

Luigi

--

---
You received this message because you are subscribed to the Google Groups "OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Luigi Dell'Aquila]

Ah, of course you could use the link in your query and do something like

select from FOrder where user.userId = "642255"

but it's less efficient than the one with the graph (because with an edge you can have bi-directional traversal, and this lets you better exploit indexes on userId)

[Julian Liebl]

Thank you very much for the quick reply. I thought it would be enough to keep links. But even if I would make two links to make it bi-directional (which slows the import down) I would have to use 2 indexes to reach my goal. You are right. Thank you very much! Zzzzz new database model here I come ;)!