Orientdb sql to select edge and vertex field properties.

Question

I have the following database structure.

users → comment → products

a. users and products are vertices that contain some information, etc.: username, product_name, and ... b. comment is an edge that contains the comment and the created / modified date.

what the sql query looks like to show the following result.

Note. I must show all products that may or may not have comments.

• product_name, user_name, comment, comment_created_date, comment_modified_date
• product_name, user_name, '', '', ''
• product_name, user_name, comment, comment_created_date, comment_modified_date

Answer 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 this is your situation, I find that the query you are looking for is something like:

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

UPDATE:

This is not very pretty, but as a workaround you can use:

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

Answer 2

You cannot "join" classes / tables when prompted. instead, combine the result sets -> start with the edge class for Product using Comment s, then use let and unionall() to add Comment ed Product to 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, which is supplied with null from the first query (i.e., from the $a result set) and Product from the second query ( $b result set)

Answer 3

using @vitorenesduarte's schema, the following query may match the current requirement

 select name AS product_name,in(comment).name AS user_name,inE(comment).comment AS comment,inE(comment).createDate AS comment_created_date,inE(comment).modifiedDate AS comment_modified_date from product