Thanks Hartmut, Now it all makes sense.
On Friday, January 27, 2017 at 5:23:12 PM UTC-8, Borov wrote: > > Hello graph gurus, > > I'm trying to create a graph based calendar. I got so far as selecting an > exact date to pull vertexes and it works very fast. However, I'm breaking > my head on *how to perform the date range selection especially when the > start end end dates fall on different years, months and days*. Please > help if you have any idea on how to do it properly with OrientDB. > > Here is my simple calendar sketch: > > > <https://lh3.googleusercontent.com/-ZHSDulmbcrc/WIvoVcgts6I/AAAAAAAAABU/noT-iNfX4IgSHVqUGxESa07PR0W6z3QWwCLcB/s1600/graph_calendar.png> > > > Basically, I have vertex for the Year, Month and Day. Each one is > connected by an edge from the Year -> Month -> Day. I have different > campaigns (vertex) connected via its own edges 'CreatedYear' or > 'DeliveredYear' to the head of the calendar - the Year vertex. We always > starting the traversal of the graph from the Campaign level to the Year and > down to the desired Day and then pick the right vertexes based on the > in('CreatedOn') edge or for a different vertex by in('DeliveredOn') edge. > > Here is my query to pick the exact date: > > select expand( > out('CreatedYear')[value = 2016] > .out('CreatedMonth')[value = 11] > .out('CreatedDay')[value = 1] > .in('CreatedOn')) > from #... > > What I need is to pass in the start and end dates and get the results for > the range. > > I've written a script that will create the calendar graph with sample data > so you can experiment with it: > > create class Year extends V > create property Year.value INTEGER (NOTNULL TRUE, MANDATORY TRUE) > > create class Month extends V > create property Month.value INTEGER (NOTNULL TRUE, MANDATORY TRUE) > > create class Day extends V > create property Day.value INTEGER (NOTNULL TRUE, MANDATORY TRUE) > > create class Campaign1 extends V > create class Campaign2 extends V > > create class CreatedYear extends E > create property CreatedYear.out LINK Campaign1 (MANDATORY TRUE) > create property CreatedYear.in LINK Year (MANDATORY TRUE) > create index UniqueCreatedYear on CreatedYear (out,in) UNIQUE > > create class CreatedMonth extends E > create property CreatedMonth.out LINK Year (MANDATORY TRUE) > create property CreatedMonth.in LINK Month (MANDATORY TRUE) > create index UniqueCreatedMonth on CreatedMonth (out,in) UNIQUE > > create class CreatedDay extends E > create property CreatedDay.out LINK Month (MANDATORY TRUE) > create property CreatedDay.in LINK Day (MANDATORY TRUE) > create index UniqueCreatedDay on CreatedMonth (out,in) UNIQUE > > create class DeliveredYear extends E > create property DeliveredYear.out LINK Campaign2 (MANDATORY TRUE) > create property DeliveredYear.in LINK Year (MANDATORY TRUE) > create index UniqueDeliveredYear on DeliveredYear (out,in) UNIQUE > > create class Lead extends V > create property Lead.name STRING > > create class Delivery extends V > create property Delivery.name STRING > > create class CreatedOn extends E > create property CreatedOn.out LINK Lead (MANDATORY TRUE) > create property CreatedOn.in LINK Day (MANDATORY TRUE) > create index UniqueCreatedOn on CreatedOn (out,in) UNIQUE > > create class DeliveredOn extends E > create property DeliveredOn.out LINK Delivery (MANDATORY TRUE) > create property DeliveredOn.in LINK Day (MANDATORY TRUE) > create index UniqueDeliveredOn on DeliveredOn (out,in) UNIQUE > > create vertex Campaign1 > create vertex Campaign2 > > insert into Year (value) values (2016) > insert into Year (value) values (2017) > > create edge CreatedYear from (select from Campaign1) to (select from Year > where value = 2016) > create edge CreatedYear from (select from Campaign1) to (select from Year > where value = 2017) > > insert into Month (value) values (11) > insert into Month (value) values (12) > insert into Month (value) values (1) > > create edge CreatedMonth from (select from Year where value = 2016) to ( > select from Month where value = 11) > create edge CreatedMonth from (select from Year where value = 2016) to ( > select from Month where value = 12) > create edge CreatedMonth from (select from Year where value = 2017) to ( > select from Month where value = 1) > > insert into Day (value) values (1) > insert into Day (value) values (14) > insert into Day (value) values (22) > insert into Day (value) values (26) > insert into Day (value) values (27) > > create edge CreatedDay from (select from Month where value = 11) to ( > select from Day where value = 1) > create edge CreatedDay from (select from Month where value = 11) to ( > select from Day where value = 14) > create edge CreatedDay from (select from Month where value = 12) to ( > select from Day where value = 22) > create edge CreatedDay from (select from Month where value = 1) to (select > from Day where value = 26) > create edge CreatedDay from (select from Month where value = 1) to (select > from Day where value = 27) > > create vertex Lead set name = 'Lead 1' > create vertex Lead set name = 'Lead 2' > create vertex Lead set name = 'Lead 3' > create vertex Lead set name = 'Lead 4' > create vertex Lead set name = 'Lead 5' > > create edge CreatedOn from (select from Lead where name = 'Lead 1') to ( > select from Day where value = 1) > create edge CreatedOn from (select from Lead where name = 'Lead 2') to ( > select from Day where value = 14) > create edge CreatedOn from (select from Lead where name = 'Lead 3') to ( > select from Day where value = 22) > create edge CreatedOn from (select from Lead where name = 'Lead 4') to ( > select from Day where value = 26) > create edge CreatedOn from (select from Lead where name = 'Lead 5') to ( > select from Day where value = 27) > > It shall create the following graph: > > > <https://lh3.googleusercontent.com/-VzMJ4smXs_w/WIvvmR8XH-I/AAAAAAAAABk/st2LACgTD4Q0UoI898dvHwJMNrmfspEnwCLcB/s1600/created_graph.png> > > Please help me figure out this date range query. It's very essential for > the fast data selection. Also, if you have any better ways of achieving the > same results let me know (with the query if possible). One of the reasons > we wanted to implement the graph calendar is because we'll be potentially > dealing with hundreds of thousands records on multiple accounts and using > graph to select data by the date seems like the right thing to do with a > graph DB. > > I'm using OrientDB 2.2.15 and coding in Java. > > Big thanks in advance! > > > -- --- 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 orient-database+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
