CODEWITHSUNDEEP
orientdb
arisalexis
arisalexis

Reputation: 2210

How to combine fields in OrientDB SQL SELECT statement

I have this rather complicated query

SELECT FROM (
  SELECT EXPAND(INV()) FROM (
    SELECT FROM (
      SELECT EXPAND(OUTE('Streamed')) FROM (
        SELECT EXPAND(OUT('Follows')) FROM #12:3923
      )
    )
  )
)

which can be described as:

From the @rid #12:3923 which is a User class, find the Users that he follows and see which Post (vertex) they have Streamed (edge).

Now with this, I would also like to have the username (User.username) from the person that Streamed each Post in the same row if possible.

Upvotes: 2

Views: 1118

Answers (2)

lsavio
lsavio

Reputation: 850

enter image description here

in reference to this small example, your goal would be derived starting from the vertex 'Alessandro' the people who follow him [Luigi, Gabriele] who have streamed some post [in this case post1, post2]. is it correct?

EDIT Try this query (presented here over several lines for readability):

select username, myPost.name as postBody, myPost.postdate as postDate 
from (select username,out("Streamed") as myPost
      from (select EXPAND(OUT('Follows'))
            from #12:0)
      unwind myPost)

The command 'unwind' serves to separate each line of the various posts so as to obtain a view of the most similar to a table. (Adjust this according to your needs).

The result of the query is: enter image description here

EDIT_2 Result of the query using "mypost.*" enter image description here

Upvotes: 6

Jagrut
Jagrut

Reputation: 922

The datasets are the ones that you need to combine. In your case, first will be User.username and second will be Post. 

select expand($all) let
$a = (//your query for fetching User.username),
$b = (//your query for fetching Post),
$all = unionall($a, $b)

EDIT::

Well, as an alternative you can try something like this:

select username, postName from (TRAVERSE out() from #12:3923 WHILE $depth <= 2)

This will however not give you exactly what you expected. The result will be two columns namely username(from your User vertex) and postName(from your Post vertex).

With the username(s) that you get for the users followed by given @rid, you will get all the corresponding Post vertices of those users in subsequent rows. And the corresponding username column values will be empty.

I assumed, that the Follows edge is from User to User and Streamed edge is from User to Post.

Upvotes: 0

Related Questions