Skip to main content

Mixing Languages

In Your First Query you queried an array with SQL. In this lesson you will add a property graph, query it with Cypher, and join the two inside a single statement.

Continue in the same file. You will need one more package:

npm install @dortdb/lang-cypher graphology

1. Load a second language​

A DortDB engine can hold more than one language. The one that parses the query text is mainLang; the rest go in additionalLangs:

import { DortDB } from '@dortdb/core';
import { defaultRules } from '@dortdb/core/optimizer';
import { SQL } from '@dortdb/lang-sql';
import { Cypher } from '@dortdb/lang-cypher';

const db = new DortDB({
  mainLang: SQL(),
  additionalLangs: [Cypher({ defaultGraph: 'social' })],
  optimizer: { rules: defaultRules },
});

defaultGraph: 'social' tells Cypher which registered source to match patterns against. You will register that graph next.

2. Register both sources​

Keep the array from the previous lesson, and add a graph of who knows whom:

import { gaLabelsOrType } from '@dortdb/lang-cypher';
import { MultiDirectedGraph } from 'graphology';

const people = [
  { id: 1, name: 'Alice', city: 'Prague' },
  { id: 2, name: 'Bob', city: 'Ankara' },
  { id: 3, name: 'Carol', city: 'Prague' },
  { id: 4, name: 'Dan', city: 'Prague' },
];
db.registerSource(['people'], people);

const social = new MultiDirectedGraph();
for (const p of people) {
  social.addNode(p.id, {
    [gaLabelsOrType]: ['Person'],
    id: p.id,
    name: p.name,
  });
}
social.addEdge(1, 2, { [gaLabelsOrType]: 'KNOWS' });
social.addEdge(1, 3, { [gaLabelsOrType]: 'KNOWS' });
social.addEdge(3, 4, { [gaLabelsOrType]: 'KNOWS' });

db.registerSource(['social'], social);

The gaLabelsOrType symbol key is how the default Graphology adapter finds node labels and edge types. Use it exactly as shown. A plain labels property is not recognized, and patterns like (:Person) would then silently match nothing.

3. Query both at once​

A LANG <name> block switches languages mid-query. Here a Cypher block counts each person's outgoing KNOWS edges, and the surrounding SQL joins that count back to the array:

const result = db.query(`
  SELECT people.name AS name, friends.cnt AS friend_count
  FROM people
  JOIN (
    LANG cypher
    MATCH (p:Person)-[:KNOWS]->(f)
    RETURN p.id AS pid, count(f) AS cnt
  ) AS friends ON people.id = friends.pid
  ORDER BY friends.cnt DESC, people.name
`);

console.log(result.data);

Run the file. You should see:

[ { name: 'Alice', friend_count: 2 }, { name: 'Carol', friend_count: 1 } ]

Bob and Dan are missing because neither has an outgoing KNOWS edge, and this is an inner join.

Two details in that query are worth noticing:

  • The Cypher block sits where a SQL subquery would, and it is aliased like one (AS friends). It returns rows, so SQL can join it.
  • ORDER BY names friends.cnt, not the friend_count alias. With two sources in scope, an unqualified name is ambiguous and DortDB rejects it.

This is not a nested call with a handoff between two engines. Both halves lower to the same operator algebra and are optimized together as one plan.

Where to next​