OrientDB query for vertices with no particular edge from another vertex

Question

I have a Post and a User vertices, related by a Posted edge.

I want to retrieve posts that have no posted relation from a specific user.

Here is my SQL Query:

SELECT posts.* FROM posts WHERE posts.user_id != 19.

I can not do something like

SELECT FROM Post WHERE user.id != 19

because I don't have links between the classes, I have edges.

How can I solve the problem?

Answer 1

create class User extends V
create class Post extends V

create class Posted extends E

create vertex User set name = 'user1'     
create vertex User set name = 'user2'     

create vertex Post set name = 'post0'
create vertex Post set name = 'post1'
create vertex Post set name = 'post2'

create edge Posted from (select from Post where name = 'post0') to (select from User where name = 'user1')
create edge Posted from (select from Post where name = 'post1') to (select from User where name = 'user1')
create edge Posted from (select from Post where name = 'post2') to (select from User where name = 'user2')

If you have something similar to the above, and e.g., you want to know all posts not related with user1 (that would be post2). Let's say the the Record ID of user1 is #12:0.

You can:

select from Post where  #12:0 not in out('Posted')
// or
select expand(in('Posted')) from (select from User where @rid not in [#12:0])

The output:

---

UPDATE

You can also use the name of the user instead of its @rid:

select * from Post where $record_id.rid[0] not in out('Posted')
let $record_id = ( select @rid from User where name = 'user1' )

But note that the let clause is executed once for every Post (in this case, 3 times). This means that it won't be efficient when you have a lot of posts.