Problem with Filtering

[redfox1993]

OrientDB Version: 3.0.18

I am new at OrientDB
I have a problem with the following query:

select in('VersionHasDoc')[id = lastversion_id] as lastVersion, lastversion_id from document

This returns empty brackets.
lastVersion | lastversion_id
[] | 1657

But if i do directly:

select in('VersionHasDoc')[id = 1657] as lastVersion, lastversion_id from document
returns as expected:
lastVersion | lastversion_id
#71:6 | 1657

Is there a syntax fault from my side?

[luigidellaquila]

Hi @redfox1993

In this case, both id and lastversion_id are evaluated on the Version document, that is probably not what you need.

I'd suggest you to use a MATCH statement instead:

MATCH
  {class:Document, as:a} <-VersionHasDoc- {as:b, where:(id = $matched.a.lastversion_id)}
RETURN b as lastVerson, a.lastversion_id

Thanks

Luigi

[redfox1993]

Hi Luigi,

Thank you for your fast response.
My example is part of an longer select statement where i want to get properties of the Vertex with the id of the lastversion_id.

I will test your suggestion in the let area :)

[redfox1993]

hi @luigidellaquila,

that match statement takes too long. In dm_document are ~4mio inserts.

The question is: why does the first query with variable dont work, even if the variable has the same value like the second query?

[luigidellaquila]

Hi @redfox1993

How many Document records do you have?
That query is scanning the whole class and fetching all the attached versions, so if it's a big DB you can expect it to take a while...
The execution time should be the same as a SELECT

Thanks

Luigi

[redfox1993]

Hi Luigi,

This is the point. I have a select on the document Vertex. This Vertex has a field lastversion_id an edges to some vertices from class Version. I dont want all versions at this point but the one with the lastversion_id as id.

So i dont understand that the second query from the start post works, but the First dont

[luigidellaquila]

Hi @redfox1993

Not sure I got the point, do you have a WHERE condition in the SELECT?
What is dm_id?

Thanks

Luigi

[redfox1993]

Hi Luigi,

The Vertex "Version" has a unique id which is in the Vertex "document" the lastversion_id to get the last Version of the document.
With :
select in('VersionHasDoc')[id = lastversion_id] as lastVersion, lastversion_id from document

I want to Filter the multiple versions in Vertex"version" to get only the latest one, but this doesnt work.

If i replace the lastversion_id with the integer directly i got the expected result. But i want to take the variable lastversion_id.

[luigidellaquila]

Hi @redfox1993

Property names in square brackets refer to the "bracketed" document. In this case, when you write [id = lastversion_id], both id and lastversion_id are calculated on the Version, that is not what you need, ie. you need lastversion_id to refer to the Document.
This is why your original query doesn't work, or better, why it does not do what you expect.

The SELECT and the MATCH have comparable execution time, so there is no reason why the former (that btw does not do what you expect) should be faster than the latter.

What I didn't get is if you need to calculate the version for a specific document or for all the documents in the DB.

Can you please post an example of the complete query?

Thanks

Luigi

[redfox1993]

Hi Luigi,

I have migrate an rdbms (mssql) to orientdb and want to rebuild a query with many joins.

this is what i did so far:

select
last_version.file_size as file_size,last_version.out('has_filetype')[0].extension, vercount, cmtcount, modification_date, lastversion_id, creation_date, is_folder, parent_id
,doc_name, refdoc_id, index_test_documents, user, doctype
from
(
select
document.in(VersionHasDoc)[id = 11356376][0] as last_version
,document.in('VersionHasDoc').size() as vercount
,document.in('RemarkHasDoc').size() as cmtcount
,document.modification_date as modification_date
,document.lastversion_id as lastversion_id
,document.creation_date as creation_date
,document.is_folder as is_folder
,document.parent_id as parent_id
,document.doc_name as doc_name
,document.refdoc_id as refdoc_id
,document.in('TestdocHasDoc')[0] as index_test_documents
,document.out('has_user')[0] as user
,document.out('DocHasDoctype')[0] as doctype
from
(
select out()[0] as document
from fulltext where rtext lucene 'test'
)
)

at:
document.in(VersionHasDoc)[id = 11356376][0] as last_version

result:
file_size | last_version.out('has_filetype')[0].extension | vercount | cmtcount | modification_date | lastversion_id | creation_date | is_folder | parent_id | doc_name | refdoc_id | index_test_documents | user | doctype
965 | PDF | 1 | 0 | 2019-02-22 07:28:35 | 11356376 | 2019-02-22 07:28:35 | 1 | 0 |   | 0 | #53:677318 | #68:0 | #26:1

i want something like:
document.in(VersionHasDoc)[id = document.lastversion_id][0] as last_version