using let blocks in orientdb select statements

Question

Hopefully a quick one - I'm struggling to make a let statement work.

I have a database of People vertexes. The vertexes have ident fields and name fields. This query returns one row - a person named Bob.

select from Person where ident = 1

I want to return all rows with the same name as this person. There are two Bobs in the data (as proof, the following query returns two rows):

select from Person where name = 'Bob'

I think all of the following queries should return those same two rows, but they all return 0 rows. They all involve different ways of using a let statement. Can anyone see what I'm doing wrong?

select name from Person
  let $tmp = (select from Person where ident = 1)
  where name = $tmp.name

select name from Person
  let $tmp = (select name from Person where ident = 1)
  where name = $tmp

select name from Person
  let $tmp = 'Bob'
  where name = $tmp

neRok neRok · Accepted Answer · 2015-10-29T07:18:49

$tmp will be a list of records, so you are asking to compare a string to a list, and it isn't working. You could do the following;

select name from Person
  let $tmp = (select from Person where ident = 1)
  where name = first($tmp).name

That wont return Person records though (only rows of names). Limiting the $tmp query will also improve the performance slightly. So the following is better;

select from Person
  let $tmp = (select from Person where ident = 1 limit 1)
  where name = first($tmp).name

But actually, using the let clause in this manner is not good. The docs say

The LET block contains the list of context variables to assign each time a record is evaluated.

So it is* (*should be) better to rearrange your query entirely;

select expand($persons_with_name) let
$person_with_ident = first((select from Person where ident = 1 limit 1)),
$persons_with_name = (select from Person where name = $parent.$person_with_ident.name)

using let blocks in orientdb select statements

2 Answers