0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
jb4096
  • 1
  • 3
  • The query is invalid and should raise an error. Poor error handling? – jarlh Mar 24 '21 at 15:42
  • `Where id in (Select id, count(id) as cnt` is trying to compare 1 value to 2 values. Invalid. Remove count(). – jarlh Mar 24 '21 at 15:43
  • It's not about error handling, the syntax is correct and the comparison just evaluates to false, so it's normal that it returns nothing – Luigi Dell'Aquila Mar 25 '21 at 07:44

1 Answers1

0

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.
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I used the group by to count the number of posts by user, or in other words the number of times a single id is returned by the second subquery. I tried to run the query above, but now got the following error: com.orientechnologies.orient.core.sql.OCommandSQLParsingException: Error parsing query: select id 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) ^ Encountered "" at line 1, column 31. Was expecting one of: – jb4096 Mar 24 '21 at 18:39