Fetch first N of sorted group results

Hi everyone, I am fairly new to OrientDB and trying to wrap my head around some concepts. Currently I am trying to load the all the newest two conversations a user had with other users.

In SQL I would do something like:

SELECT message_text, sender_id, receiver_id FROM (
  SELECT row_number() over( PARTITION BY sender_id, receiver_id ORDER BY message_created_at 
  DESC), message_text, sender_id, receiver_id
) WHERE row_number < 3

How to achieve it with Orient, I can’t seem to find a numbering function

Best regards,
Marius

Hi @marius.schmidt

I’m afraid in OrientDB there is no concept of PARTITION, so you have to find another way to do it.
Can you please share some more info about your schema? Is all the info in a single class?

Thanks

Luigi

Hi Luigi,

We have this:

(UserVertex) <-- [SentMessage] -- (MessageVertex) -- [ReceiveMessage] --> (UserVertex)

SentMesage and ReceiveMessage are both extending MessageEvent, MessageEvent extends E. Given a certain user, we want to present a conversation overview showing the last two conversations for every conversation partner, regardless if the user was sender or receiver of the message. MessageVertex contains a timestamp to sort on. Beside the message-text stored in MessageVertex, we would also have to fetch UserVertex.name, and UserVertex.profileImage for both user and conversation partner. Lets call the LEFT (UserVertex) user and the RIGHT (UserVertex) conversation_partner.

Thx for you taking time :slight_smile:

Hi @marius.schmidt

You can try something as follows:

SELECT list(msg)[0..3], u1, u2 FROM (
   MATCH
     {class:User, as:u1, where:(userId = :user1)} <-Sent- {as:msg} -Received-> {class:User, as:u2} 
   RETURN msg, u1, u2 order by msg.date
) GROUP BY u1, u2

It only covers the messages sent by the user, but you can do another query for the received and mix them with a unionAll()

I hope it helps

Thanks

Luigi

Hi Luigi,

your query is parsed correctly, but unfortunately the list(msg)[0…N] is not limiting the amount of ORIDs returned :confused: Here’s my query used

SELECT list(msg)[0..2], u1, u2 FROM (
MATCH
{class:User, as:u1, where:(userKey='f0000000-0000-0000-0000-000000000001')} <-SentMessage- {class:Message, as:msg} -ReceiveMessage-> {class:User, as:u2}
RETURN msg, u1, u2 order by msg.date
) GROUP BY u1, u2

And here is, what the result looks like

You can try UNWIND in Query which obtaining multiple records as a result.

Please refer below link
http://orientdb.com/docs/3.0.x/sql/SQL-Query.html#unwinding

I am not sure but relate below query
SELECT list(msg)[0…2] as list, u1, u2 FROM (
MATCH
{class:User, as:u1, where:(userKey=‘f0000000-0000-0000-0000-000000000001’)} <-SentMessage- {class:Message, as:msg} -ReceiveMessage-> {class:User, as:u2}
RETURN msg, u1, u2 order by msg.date
) GROUP BY u1, u2
UNWIND list

Hi guys,

thanks to your input, I came up with a working solution:

SELECT msgs[0..2] AS foo, u1, u2 FROM (
  SELECT list(msg) as msgs, u1, u2 FROM (
    MATCH
    {class:User, as:u1, where:(userKey='f0000000-0000-0000-0000-000000000001')} <-SentMessage- {class:Message, as:msg} -ReceiveMessage-> {class:User, as:u2}
    RETURN msg, u1, u2 order by msg.date
    ) GROUP BY u1, u2
  ) unwind foo

But do you think this will perform well when e.g. on average each user exchanged 10.000 messages with on average each of his 100 friends? How does one usually solve the “pick top N per group” style question with OrientDB?