Hey
So I've spent around 10hrs now looking at various ways to model and query
something that I can do easily in an RDMS and have yet to find a solution
that feels right using OrientDB though I know it must exist!
The scenario:
A user can watch / love / own a movie. When browsing movies, it should show
what actions they have applied, and the total counts of each action for all
users.
Use Edge per action (the first attempt) with a updatedAt:dateTime property
1) User (V) -> likes[updatedAt:dateTime] (E) -> Film (V)
or User (V) -> owns[updatedAt:dateTime] (E) -> Film (V)
or User (V) -> watched[updatedAt:dateTime] (E) -> Film (V)
Use a single Edge with action properties
2) User (V) -> actioned[loved:dateTime, watched:dateTime, owns:dateTime,
updatedAt:dateTime] (E) -> Film (V)
User a join table (RDBMS design)
3) User (V) <-> FilmAction (V) <-> Film (V)
I got far with 1, then found it hard to order the films by the last
actioned as each edge has it's own updatedAt property and seems to slow on
ordering.
With 2, that was resolved, but became very slow when ordering by films
which are most loved / owns or watched etc as it has to look at the
properties of every edge
3 I've not done yet in OrientDB but had it working fine in PostGres using 2
queries - one to get the films, then another IN clause to get the actions
for that film for the logged in user
With design 1, which I feel is the most suitable, I find it very fast (<
0.1s) for something like this:
select @rid, in_watched.size(), in_owns.size(), in_loved.size(), title,
inE('watched','loved','owns')[out=#12:25903][email protected]()
from film
Which gives me the counts and the actions of the relevant user if they exist
However, as soon as I add an order clause, e.g.
select @rid, in_watched.size(), in_owns.size(), in_loved.size(), title,
inE('watched','loved','owns')[out=#12:25903][email protected]()
from film
order by in_watched.size()
query time drops to around 600-700ms which I'm not very happy with knowing
I should be getting faster and the test data isn't massively populated
I've tried use LET $a = in_watched etc but doesn't help. Removing the query
for the users actions, but keeping the ordering, it runs fast, <100ms so I
am faced with one or the other right now
Any advice on the best way to model something like this would be great
appreciated or how to optimise a query that gets the count of all the
edges, as well as if that edge exists for a specific user.
Kindest Regards
Sky
--
---
You received this message because you are subscribed to the Google Groups
"OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
