menu

EdgeDB

Bringing state-of-the-art database technology to the masses.

Channels
Team

Self-referencing INSERTs are not allowed

June 21, 2020 at 7:16pm
The EdgeDB community has a new home. This thread is preserved for historical purposes. The content of this conversation may be innaccurrate or out of date. Go to new community home →

Self-referencing INSERTs are not allowed

June 21, 2020 at 7:16pm
I am trying to put together an example on tree structures. Following a variation on the example provided in the documentation but I'm getting this error message: edgedb.errors.QueryError: invalid reference to default::Place: self-referencing INSERTs are not allowed. It's not clear to me from the error message, the documentation or the tests how to resolve this error.
Here's the insert query:
INSERT Place {
name := 'Africa',
parent := (
SELECT Place FILTER .name = 'Earth' LIMIT 1
)
};
Here's the schema:
type Place {
required property name -> str;
link parent -> Place;
link children := .<parent[IS Place];
index on (.name);
}
}
Example from the docs:
INSERT Movie {
title := 'Dune',
director := (
SELECT Person
FILTER
# the last name is sufficient
# to identify the right person
.last_name = 'Villeneuve'
# the LIMIT is needed to satisfy the single
# link requirement validation
LIMIT 1
)
};

June 21, 2020 at 8:03pm
This is because EdgeQL treats matching symbolic names as the same object in the same query scope, and you obviously cannot both be inserting an object and referring to the same object in the INSERT body. The correct way to formulate this query is to declare a different symbol for the nested reference in the WITH block:
WITH ParentPlace := Place
INSERT Place {
name := 'Africa',
parent := (
SELECT ParentPlace FILTER .name = 'Earth' LIMIT 1
)
};
Alternatively, you can use the DETACHED keyword:
INSERT Place {
name := 'Africa',
parent := (
SELECT DETACHED Place FILTER .name = 'Earth' LIMIT 1
)
};
  • reply
  • like
, we need to add this to the cheatsheet and document more prominently in the DML reference.
  • reply
  • like
, we also have tests for self-referencing tree structures that have more examples: setup, queries.
  • reply
  • like
Thank you. For me this raises a larger question: 'When should I use a WITH block?
like-fill
1
  • reply
  • like

June 22, 2020 at 4:16pm
With respect to symbol aliasing like the above it's mostly a matter of taste. I personally prefer DETACHED as it always guarantees an independent set regardless of where in the query I am. WITH blocks are primarily useful when you want to factor out a piece of expression nicely, especially if you use it more than once in your query.
  • reply
  • like