in operator not working with subquery in OrientDB

Question

I have the following sql query in OrientDB to find the names of the most active customers in a social network:

SELECT name
FROM Customer
Where id in (Select id, count(id) as cnt
             from (Select IN('PersonHasPost').id[0] as id
                   From Post
                   Where creationDate>= date( '2012-10-01', 'yyyy-MM-dd')
                  )
             Group by id
             Order by cnt DESC 
             limit 10
            )
GROUP BY id;

However this query returns no results. When I run the subquery separately it does give me the ids of the 10 most active customers, together with the number of posts, which makes me think something is wrong with the in operator. What am I doing wrong here? I am running this query on OrientDB 3.0.5.

Answer 1

There are a few issues that I can immediately see. Try this:

SELECT name
FROM Customer
Where id in (Select id
             from (Select IN('PersonHasPost').id[0] as id
                   From Post
                   Where creationDate>= date('2012-10-01', 'yyyy-MM-dd')
                  ) p
             Group by id
             Order by count(*) DESC 
             limit 10
            );

Notes:

• Your subquery is returning two columns, but IN requires just 1.
• Some databases require a table alias for a derived table, so that might also be an issue.
• The outer GROUP BY does not match the SELECT. I'm not sure what you really want, but I don't think aggregation or duplicate elimination is necessary.