Skip to content

Magic Paths

Jump to bottom Edit New page
Oxford Harrison edited this page Nov 15, 2024 · 10 revisions

DOCS • Concepts

💥 Express relationships graphically!

Linked QL supports special path operators, a syntax extension to SQL, that let you tie-in columns from other tables without writing a single JOIN clause. Linked QL draws on its 100% schema awareness to figure out the relevant JOINS behind the scenes.

Where you normally would write...

-- Regular SQL
SELECT title, users.fname AS author_name FROM posts
LEFT JOIN users ON users.id = posts.author

Linked QL lets you draw a path to express the relationship:

-- Linked QL
SELECT title, author ~> fname AS author_name FROM posts

For the sample schema below:

-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    title varchar,
    name varchar,
    role int references roles (id),
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);

A typical relational query would look like:

-- Regular SQL
SELECT book.id, book.title, content, book.created_time, user.id AS author_id, user.title AS author_title, user.name AS author_name 
FROM books AS book LEFT JOIN users AS user ON user.id = book.author

But that can be as simple as:

-- Linked QL
SELECT id, title, content, created_time, author ~> id, author ~> title, author ~> name 
FROM books

✨ Being about 50% of the original code and with whole namespacing exercise gone!

You are able to chain these operators to any level for your multi-level relationships:

-- Linked QL
SELECT * FROM books
WHERE author ~> role ~> codename = 'admin'

and for the different forms of relationships out there (one-to-many, many-to-one, many-to-many), path operators can go in any direction:

-- Linked QL
SELECT * FROM users
WHERE author <~ books ~> title = 'Beauty and the Beast'

Plus, with Linked QL being a superset of SQL, you can combine the new magic together with the old LEFT JOIN/RIGHT JOIN/etc clauses to weild the full power of the language:

-- Linked QL
SELECT users.* FROM users, some_other_table.id
LEFT JOIN some_other_table USING some_other_condition
WHERE author <~ books ~> title = 'Beauty and the Beast'

Example 1:

Schema 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// Basic relational query | MANY-TO-ONE
// DESC: Tie-in fields from the users table
const result = await client.query(
    `SELECT
        title,
        content,
        author ~> name AS author_name
    FROM books
    WHERE author ~> role = $1`,
    ['admin']
);
console.log(result);
Console 
[
    {
        title: 'Beauty and the Beast',
        content: '(C) 2024 [email protected]\nBeauty and the Beast...',
        author_name: 'John Doe'
    },
    {
        title: 'The Secrets of Midnight Garden',
        content: '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...',
        author_name: 'Alice Blue'
    }
]

Example 2:

Schema 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// Structured relational query | MANY-TO-ONE
// DESC: Tie-in structure from the users table
const result = await client.query(
    `SELECT
        title,
        content,
        author: { name, email } AS author
    FROM books
    WHERE author ~> role = $1`,
    ['admin']
);
console.log(result);
Console 
[
    {
        title: 'Beauty and the Beast',
        content: '(C) 2024 [email protected]\nBeauty and the Beast...',
        author: {
            name: 'John Doe',
            email: '[email protected]'
        }
    },
    {
        title: 'The Secrets of Midnight Garden',
        content: '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...',
        author: {
            name: 'Alice Blue',
            email: '[email protected]'
        }
    }
]

Example 3:

Schema 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    parent int references users (id),
    created_time timestamp
);
// Multi-level relational query | MANY-TO-ONE
// DESC: Tie-in multi-level fields from same users table
const result = await client.query(
    `SELECT
        name,
        email,
        parent ~> parent ~> name AS grand_parent
    FROM users
    LIMIT 2`
);
console.log(result);
Console 
[
    {
        name: 'John Doe',
        email: '[email protected]',
        grand_parent: 'Some user 1'
    },
    {
        name: 'Alice Blue',
        email: '[email protected]',
        grand_parent: 'Some user 2'
    }
]

Example 4:

Schema 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// Reversed relational query | ONE-TO-MANY
// DESC: Tie-in entries from the books table
const result = await client.query(
    `SELECT
        name,
        email,
        author <~ books ~> title AS book_title
    FROM users
    WHERE author <~ books ~> content LIKE '%(C) 2024%'`,
);
console.log(result);
Console 
[
    {
        name: 'John Doe',
        email: '[email protected]',
        book_title: 'Beauty and the Beast - Part 1'
    },
    {
        name: 'John Doe',
        email: '[email protected]',
        book_title: 'Beauty and the Beast - Part 2'
    },
    {
        name: 'Alice Blue',
        email: '[email protected]',
        books: 'The Secrets of Midnight Garden - Part 1'
    },
    {
        name: 'Alice Blue',
        email: '[email protected]',
        books: 'The Secrets of Midnight Garden - Part 2'
    }
]

Example 5:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// Reversed, aggregated relational query | ONE-TO-MANY
// DESC: Tie-in structured and aggregated entries from the books table
const result = await client.query(
    `SELECT
        name,
        email,
        author <~ books: { title, content }[] AS books
    FROM users
    WHERE author <~ books ~> content LIKE '%(C) 2024%'`,
);
console.log(result);
Console 
[
    {
        name: 'John Doe',
        email: '[email protected]',
        books: [
            {
                title: 'Beauty and the Beast - Part 1',
                content: '(C) 2024 [email protected]\nBeauty and the Beast...'
            },
            {
                title: 'Beauty and the Beast - Part 2',
                content: '(C) 2024 [email protected]\nBeauty and the Beast...'
            }
        ]
    },
    {
        name: 'Alice Blue',
        email: '[email protected]',
        books: [
            {
                title: 'The Secrets of Midnight Garden - Part 1',
                content: '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...'
            },
            {
                title: 'The Secrets of Midnight Garden - Part 2',
                content: '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...'
            }
        ]
    }
]

Example 6:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// Basic multi-dimensional INSERT | MANY-TO-ONE
// DESC: for each book entry CREATED, CREATE a user with specified email
const result = await client.query(
    `INSERT INTO books (
        title,
        content,
        author ~> email
    ) VALUES (
        'Beauty and the Beast',
        '(C) 2024 [email protected]\nBeauty and the Beast...',
        '[email protected]'
    ), (
        'The Secrets of Midnight Garden'
        '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...',
        '[email protected]'
    )`
);
console.log(result); // true
// Basic multi-dimensional UPSERT | MANY-TO-ONE
// DESC: for each book entry CREATED or UPDATED, CREATE or UPDATE a user with specified email
const result = await client.query(
    `UPSERT INTO books (
        title,
        content,
        author ~> email
    ) VALUES (
        'Beauty and the Beast',
        '(C) 2024 [email protected]\nBeauty and the Beast...',
        '[email protected]'
    ), (
        'The Secrets of Midnight Garden'
        '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...',
        '[email protected]'
    )`
);
console.log(result); // true
// Basic multi-dimensional UPDATE | MANY-TO-ONE
// DESC: for each book entry UPDATED, CREATE or UPDATE a user with specified email
const result = await client.query(
    `UPDATE books
    SET
        title = 'Beauty and the Beast',
        content = '(C) 2024 [email protected]\nBeauty and the Beast...',
        author ~> email = '[email protected]'
    `
);
console.log(result); // true

Example 7:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// Structured multi-dimensional INSERT | MANY-TO-ONE
// DESC: for each book entry CREATED, CREATE a user with specified name and email, RETURNING entire tree
const result = await client.query(
    `INSERT INTO books (
        title,
        content,
        author: (
            name,
            email
        )
    ) VALUES (
        'Beauty and the Beast',
        '(C) 2024 [email protected]\nBeauty and the Beast...',
        (
            'John Doe',
            '[email protected]'
        )
    ), (
        'The Secrets of Midnight Garden'
        '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...',
        (
            'Alice Blue',
            '[email protected]'
        )
    ) RETURNING *`
);
console.log(result);
Console 
[
    {
        id: 1,
        title: 'Beauty and the Beast',
        content: '(C) 2024 [email protected]\nBeauty and the Beast...',
        author: {
            id: 1,
            name: 'John Doe',
            email: '[email protected]',
            phone: '(555) 123-4567',
            role: 'admin',
            created_time: '2024-11-06T18:22:46.709Z'
        },
        created_time: '2024-11-06T18:22:46.709Z'
    },
    {
        id: 2,
        title: 'The Secrets of Midnight Garden',
        content: '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...',
        author: {
            id: 2,
            name: 'Alice Blue',
            email: '[email protected]',
            phone: '(888) 123-4567',
            role: 'admin',
            created_time: '2024-11-06T18:22:46.709Z'
        },
        created_time: '2024-11-06T18:22:46.709Z'
    }
]

Example 8:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// Structured multi-dimensional INSERT | MANY-TO-ONE
// DESC: for each user CREATED, CREATE a book entry with specified title and content, RETURNING entire tree
const result = await client.query(
    `INSERT INTO users (
        name,
        email,
        author <~ books: (
            title,
            content
        )
    ) VALUES (
        'John Doe',
        '[email protected]',
        (
            'Beauty and the Beast',
            '(C) 2024 [email protected]\nBeauty and the Beast...'
        )
    ), (
        'Alice Blue',
        '[email protected]',
        (
            'The Secrets of Midnight Garden',
            '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...'
        )
    ) RETURNING *`
);
console.log(result);
Console 
[
    {
        id: 1,
        name: 'John Doe',
        email: '[email protected]',
        phone: '(555) 123-4567',
        role: 'admin',
        created_time: '2024-11-06T18:22:46.709Z'
        'author <~ books': [
            {
                id: 1,
                title: 'Beauty and the Beast',
                content: '(C) 2024 [email protected]\nBeauty and the Beast...',
                created_time: '2024-11-06T18:22:46.709Z'
            }
        ]
    },
    {
        id: 1,
        name: 'Alice Blue',
        email: '[email protected]',
        phone: '(888) 123-4567',
        role: 'admin',
        created_time: '2024-11-06T18:22:46.709Z'
        'author <~ books': [
            {
                id: 1,
                title: 'The Secrets of Midnight Garden',
                content: '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...',
                created_time: '2024-11-06T18:22:46.709Z'
            }
        ]
    }
]

Example 9:

Schema (as before) 
-- The users table
CREATE TABLE users (
    id int primary key generated always as identity,
    name varchar,
    email varchar,
    phone varchar,
    role varchar,
    created_time timestamp
);
-- The books table
CREATE TABLE books (
    id int primary key generated always as identity,
    title varchar,
    content varchar,
    author int references users (id),
    created_time timestamp
);
// Structured multi-dimensional INSERT | MANY-TO-ONE
// DESC: for each user CREATED, CREATE two book entries with specified titles and contents, RETURNING entire tree
const result = await client.query(
    `INSERT INTO users (
        name,
        email,
        author <~ books: (
            title,
            content
        )
    ) VALUES (
        'John Doe',
        '[email protected]',
        VALUES (
            (
                'Beauty and the Beast - Part 1',
                '(C) 2024 [email protected]\nBeauty and the Beast...'
            ), (
                'Beauty and the Beast - Part 2',
                '(C) 2024 [email protected]\nBeauty and the Beast...'
            )
        )
    ), (
        'Alice Blue',
        '[email protected]',
        VALUES (
            (
                'The Secrets of Midnight Garden - Part 1',
                '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...'
            ), (
                'The Secrets of Midnight Garden - Part 2',
                '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...'
            )
        )
    ) RETURNING *`
);
console.log(result);
Console 
[
    {
        id: 1,
        name: 'John Doe',
        email: '[email protected]',
        phone: '(555) 123-4567',
        role: 'admin',
        created_time: '2024-11-06T18:22:46.709Z'
        'author <~ books': [
            {
                id: 1,
                title: 'Beauty and the Beast - Part 1',
                content: '(C) 2024 [email protected]\nBeauty and the Beast...',
                created_time: '2024-11-06T18:22:46.709Z'
            },
            {
                id: 2,
                title: 'Beauty and the Beast - Part 2',
                content: '(C) 2024 [email protected]\nBeauty and the Beast...',
                created_time: '2024-11-06T18:22:46.709Z'
            }
        ]
    },
    {
        id: 1,
        name: 'Alice Blue',
        email: '[email protected]',
        phone: '(888) 123-4567',
        role: 'admin',
        created_time: '2024-11-06T18:22:46.709Z'
        'author <~ books': [
            {
                id: 1,
                title: 'The Secrets of Midnight Garden - Part 1',
                content: '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...',
                created_time: '2024-11-06T18:22:46.709Z'
            },
            {
                id: 2,
                title: 'The Secrets of Midnight Garden - Part 2',
                content: '(C) 2024 [email protected]\nThe Secrets of Midnight Garden...',
                created_time: '2024-11-06T18:22:46.709Z'
            }
        ]
    }
]
Add a custom footer
Add a custom sidebar
Clone this wiki locally