Hacker News Reader

Comment by historyloop | Hacker News Reader

Question: why do you claim EdgeDB is a "relational" database when you can't actually do relational algebra with it? How do you join?

Materialized edges (i.e. what you call "links") are not part of any relational model, it's a part of the graph model. You seem to have implemented a graph database, but possibly without the graph walking capabilities of graph databases.

View on HN

EdgeQL lets you do arbitrary joins as well. Here's how you could compute salaries of employees by department even if you for some reason don't have a link between Employee and Department:

    SELECT Department {
        name,
        employees := (
            SELECT Employee { name, salary } 
            FILTER Employee.department_name = Department.name
        )
    }

Links in EdgeQL are merely an abstraction over the fact that most joins in a well-normalized schema are done over primary keys.

How do I join tables 1:1 without creating a subfield, i.e.:

table1: id, name

table2: table1_id, level

Desired result:

{id, name, level}

The most direct implementation of what you want would be:

  SELECT (Table1.id, Table1.name, Table2.level, Table2.table1_id)
  FILTER Table1.id = Table2.table1_id;

(This has a somewhat annoying extra output of table1_id, which could be projected away if necessary.)

If you want to use edgeql's shapes but still keep the essential join flavor, then something like:

  SELECT (Table1 { id, name }, Table2 { level })
  FILTER Table1.id = Table2.table1_id;

To make it a little more concrete, you can try this on the tutorial database (https://www.edgedb.com/tutorial)

  SELECT (Photo {uri}, User {name, email})
  FILTER Photo.author.id = User.id;

The tutorial database uses links, and so instead of having an author_id we have author.id, but having an author_id property would work just fine---except that then you'd have to do all the joins manually.

You do joins when you traverse schema type paths in EdgeQL, it's just that the actual joins are implicit (well, compared to SQL):

  SELECT User {
    email,
    preferences: {
      name,
      value
    }
  } FILTER .id = '...'

E.g. in the above example we'd join the underlying User and Preferences tables for you.

You can also do cross joins and all other funky stuff.

How would you represent a self join?

If given a recursive schema like this:

    type Tree {
        property value -> str
        link parent -> Tree
    }

you'd traverse the link as usual:

    SELECT Tree {
        value,
        parent: {
            value
        }
    }
    FILTER .parent.parent.parent.value = 'foo'

If there's a need to self-join on an arbitrary property, then you could use a `WITH` clause to explicitly bind the two sets:

    WITH
        T1 := Tree,
        T2 := Tree
    SELECT
        T1 {
            similarly_valued := (SELECT T2 FILTER T1.value = T2.value)
        }

Thanks!

Example from the cheatsheet:

``` WITH P := Person SELECT Person { id, full_name, same_last_name := ( SELECT P { id, full_name, } FILTER # same last name P.last_name = Person.last_name AND # not the same person P != Person ), } FILTER EXISTS .same_last_name ```

Reply on news.ycombinator.com