SQL - FIND REFERENCES

Searches the database for records that contain a link to one or more given Record IDs (RIDs). It is the SQL equivalent of asking "who points at this record?" and is useful for impact analysis before a delete, debugging stray references, or generally inspecting how data is connected.

The command originates from OrientDB and is implemented in ArcadeDB with the same syntax and semantics for compatibility.

Syntax

FIND REFERENCES <rid|(<sub-query>)> [<class-or-bucket-list>]

  • <rid> a single Record ID (e.g. #12:0).

  • (<sub-query>) a parenthesized SELECT (or other query) whose result set provides the RIDs to search for. This is the only way to look up multiple RIDs in one statement.

  • <class-or-bucket-list> an optional bracketed list of types and/or buckets that restricts the scan. Each item is either a type name or bucket:<bucketName>. When omitted the command scans every type in the schema.

A bracketed RID list (e.g. [#12:0, #12:1]) is not a valid target - it is parsed as the class/bucket filter. To search for multiple RIDs in one call, wrap them in a sub-query: FIND REFERENCES (SELECT FROM [#12:0, #12:1]).

Output

The command returns a result set with one row per (searched RID, referrer) pair. Each row contains the following columns:

Column Type Description

rid

RID

The searched Record ID.

referredBy

RID

Record ID of the document, vertex, or edge that holds a link to rid.

fields

List<String>

The access paths inside the referrer where the link was found (for example car., workers.<elem>., relations.<map:primary>., @in. for an edge endpoint).

If no record references the searched RID, the result set is empty - this is not an error.

What is scanned

For every record visited, the command recursively inspects:

  • direct link properties;

  • lists, sets, and other collections (and their nested values);

  • maps (only the values are inspected, not the keys);

  • embedded documents (recursively walking their properties);

  • the @out and @in endpoints of edges, so a vertex RID will report the edges that connect to it.

Scalars and other non-link values are ignored. The optional class/bucket filter restricts which records are visited (and is polymorphic for types - subtypes are included).

Examples

  • Find every record that links to a specific RID:

ArcadeDB> FIND REFERENCES #12:0

  • Restrict the scan to one or more types:

ArcadeDB> FIND REFERENCES #12:0 [Person, Animal]

  • Restrict the scan to a specific type and a specific bucket:

ArcadeDB> FIND REFERENCES #12:0 [Person, bucket:animal]

  • Search for the references to all the records returned by a sub-query:

ArcadeDB> FIND REFERENCES (SELECT FROM Person WHERE name = 'John')

  • Combine a parameterised sub-query with a class/bucket filter:

ArcadeDB> FIND REFERENCES (SELECT FROM Person WHERE name = ?) [Person, bucket:animal]

Performance

FIND REFERENCES performs a sequential scan of the targeted buckets - there is no index that can be used to accelerate "incoming link" lookups generically. On large databases, narrow the scan with the class/bucket filter whenever the set of possible referrers is known in advance.