orientdb sql query to select edge and vertex fields property.

Question

I do have following database structure.

users -> comment -> products

a. users and products are the vertexes that contain some info etc: user_name, product_name and .... b. comment is the edge that contains comment and created/modified date.

what is the sql query may look like in order to show the following result.

Note: i have to show all of the products that may have or no have comment.

1. product_name, user_name, comment, comment_created_date, comment_modified_date
2. product_name, user_name, '', '', ''
3. product_name, user_name, comment, comment_created_date, comment_modified_date

Solution 1

create class User extends V
create property User.name string

create class Product extends V
create property Product.name string

create class Comment extends E
create property Comment.comment string
create property Comment.createDate datetime
create property Comment.modifiedDate datetime


create vertex User set name = 'u1' # 12:0
create vertex Product set name = 'p1' # 13:0
create vertex Product set name = 'p2' # 13:1

create edge Comment from #12:0 to #13:0 set comment = 'nice product', createDate = sysdate()

If the above is your situation, I believe the query you're looking for is something like:

select *, expand(inE('Comment')) from Product

UPDATE:

It's not very pretty, but as a workaround you could use:

select *, inE('Comment').include('comment', 'createDate', 'modifiedDate') from Product

Solution 2

you cannot "join" classes/tables when querying. instead, merge the result sets -> start from the edge class for Products with Comments, then use let and unionall() to add the non-Commented Products before expand()ing:

select expand($c)
let $a = (select in.name as name, out.name as User, comment, createDate, modifiedDate from Comment),
    $b = (select from Product where in_Comment is null),
    $c = unionall($a, $b)

note that in the result set you will have the @CLASS field fed with nulls from the first query (i.e., from the $a result set) and with Product from the second query (the $b result set)

Comments

[rmuller]

The OP is asking for all products, also the products without comments (edges). Also note key in this question is having the properties of both the comment edge (if it exists) and the product.

[martin]

as according to your given query, it only show me the result related to comment edge only. it's not my intention. i'm looking forward to get result that show both comment edge and product in each row.

[vitorenesduarte]

Hi @martin. You're right, I just tested it. But it should work. I'm querying for all fields and the expand of some RID's. I didn't know that expand function behaves like this.

[vitorenesduarte]

I've tried to solve it with variables but without success. Try to ask here, maybe someone can help you.

[vitorenesduarte]

Hi @martin. I have opened an issue about this. When it's solved you can use my suggestion. Meanwhile I can't find any workaround :(

[martin]

lets take a look at $b that only select products that dont' have any comment. how about the products that do have comments?

[vitorenesduarte]

Hi @martin. I forgot to let you know, I've updated this post a long time ago with a workaround.

[tetotechy]

they are caught by $a