Select vertices with an embedded list value in a range

[Lior Neudorfer]

Hi,

I have a DB with vertices called Person, and edges called AggregatedPhoneCall.

Each edge keeps an indexed embedded list "call_times", which keeps all the times in which a phone call was made between these 2 Persons.

I want to write a query which will return all AggregatedPhoneCall edges which have a value between a given range.

I've tried using this query:

SELECT FROM AggregatedPhoneCall WHERE call_times BETWEEN "2015-08-30 20:00:00" AND "2015-08-30 20:42:31"

but it returns nothing.

I have a very large amount of data, and potentially thousands of call_times per AggregatedPhoneCall, so using the index is a must...

Any ideas?

Using one edge per phone call is not an option (for other reasons that are less important in this context).

Thanks so much,

Lior

[Giulia Brignoli]

Hi Lior,

probably your problem is that you have an embedded list, because Orient can't scroll through the entire list through your query.

I think that the only plausible solution would be to create a javascript function that will extract the various fields of the list and examine them one by one.

Best Regards,

Giulia

[Lior Neudorfer]

It's a bit weird, because the created index *does* include all values in an array.
So if I have an index with all the values from within the lists, it would make sense that I could issue an indexed based range query...

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/PPV2B9PZ_ng/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Giulia Brignoli]

The problem is that not all fields are indexed on the array, but is indexed the entire array.

I think it is for this reason that your query failed.

Regards,

Giulia

[alessand...@gmail.com]

Hi Lior,

I hope that this query can help you

select from AggregatedPhoneCall where call_times in ( select value from (select expand(call_times) from AggregatedPhoneCall) where value between "2015-08-30 20:04:00" and "2015-08-30 20:08:00")

Kind regards,

Alessandro

[Lior Neudorfer]

That would work, Alessandro, but it means traversing and expanding all call_times without using the index.

Giulia - I've made some progress: this query shows that the index *does* index the fields:

select from index:aggregatedphonecall_call_times

I get a table of *individual* keys and rids! That's a good thing...

Now the only problem is that, for some reason, adding "WHERE key < "2015-08-30 20:21:07" doesn't work properly... probably has something to with key being a date field.

Will update if I find a solution.

Thanks for the suggestions all

--

[alessand...@gmail.com]

Hi Lior,

try this query

select expand(distinct(rid)) from index:aggregatedphonecall_call_times where key BETWEEN "2015-08-30 20:00:00" AND "2015-08-30 20:42:31"

Alessandro

[Lior Neudorfer]

It should work - but it doesn't. There's an open issue about it, apparently...

https://github.com/orientechnologies/orientdb/issues/4697

:(

Thanks again for the help.

--

[alessand...@gmail.com]

Hi Lior,

this query seems to work

select expand(distinct(rid)) from (select from index:aggregatedphonecall_call_times) where key between "2015-08-30 20:00:00" AND "2015-08-30 20:42:31"

Alessandro

[Lior Neudorfer]

Alessandro, the query does return results - but if you look at them carefully, you'll see that they're incorrect.

Try putting completely different dates, and you'll see that the results don't change :(

--

[alessand...@gmail.com]

Hi Lior,

I have done this little example

I have only three edges (AggregatedPhoneCall) as you can see in the image

When I run the query I get the correct result

Alessandro