Search code examples
sqlgraphorientdb

OrientDB messages unread count

I currently have the following graph in my OrientDB database:

Database Graph

Which contains of the following properties:

Database Schema'

Basically a User can be part of a so called Thread, this is set by the IsMember edge. If they are a member they are able to send a Message to a Thread.

Inside the IsMember edge there is also a last_read property which is of the type DateTime, this is a date of when they last opened the Thread. So if we try and get all the Messages with a newer created_at we get all the unread Message's. A query to accomplish this could look like this (cluster 12=users 14=thread):

SELECT * FROM Message 
    let $LR = (select lastRead.asLong() from IsMember where in = #12:1320782 AND out = #14:705856)
WHERE in = #14:705856 AND out = #12:1320782 AND created_at.asLong() > $LR[0].lastRead

This is great and all but I would like to show a unread counter for all the Threads. Using this query for all the Threads a User is a Member of would in some cases use up to 200-300 queries.

So basically I am looking for a query that is able to get all the unread Messages of all the threads a User is a member of.

Extra usefull queries: A query to get all the subscribed Threads of a User would look something like this:

select expand(out) from (
  select * from IsMember where in = 12:1320782
)

Query to get the lastRead property from a given User and Thread

select lastRead.asLong() from IsMember where in = #12:1320782 AND out = #14:705856
Solution

  • Try this query

    select in.nick as user ,out.title as thread ,$a.size() as count from IsMember
let $a=(select created_at from Message where out.nick=$parent.current.in.nick and in.title=$parent.current.out.title and created_at > $parent.current.last_read)