I have a query, using the edge path filtering over the type property:
select *, outE('HAS_CONTENT')[type='D'].inV()[0].`name` as `content-name` from Root ORDER BY `content-name` desc limit 5
It works, but is not fulfilling the needs, as I want the sortable field name being fetched more precisely over multiple edge property limitations, namely type and language, producing zero results if no edges match all the restrictions. Unfortunately, neither of the following is supported by the OrientDB SQL syntax:
select *, outE('HAS_CONTENT')[type='D' && language = 'EN'].inV()[0].`name` as `content-name` from Root ORDER BY `content-name` desc limit 5
select *, outE('HAS_CONTENT')[type='D', language = 'EN'].inV()[0].`name` as `content-name` from Root ORDER BY `content-name` desc limit 5
The following is syntactically accepted, but does not seem to match the data structure, producing erratic results:
select *, outE('HAS_CONTENT')[type='D'][language = 'EN'].inV()[0].`name` as `content-name` from Root ORDER BY `content-name` desc limit 5
Is it possible to limit the edge path via multiple edge property filters at once, with current OrientDB SQL syntax?
The answer is: it is possible via so-called LET-variable syntax, whose documentation is not that easy to locate. For the problem above the solution will look like following:
select *, $content_name as `content-name` from Root let $content_name = (select inV()[0].`name` from HAS_CONTENT where @rid = $parent.$current.outE().@rid and type='D' and language = 'EN' ) ORDER BY `content-name` desc limit 5