Vex · Query DSL

Joins

A query with more than one table in from compiles to a JOIN of each table to an earlier one, on the foreign key between the two. The DSL has no join condition: the ON clause is derived from the foreign keys in the schema.

Query

{
  from: ['bookings', 'members', 'shows'],
  fields: [
    { field: 'shows.title', as: 'show' },
    { field: 'members.name', as: 'member' },
    'bookings.seats',
  ],
}

Tables

CREATE TABLE bookings (
  id text PRIMARY KEY,
  show_night text NOT NULL REFERENCES shows(night),
  member_id text NOT NULL REFERENCES members(id),
  seats integer NOT NULL
)
CREATE TABLE members (id text PRIMARY KEY, name text NOT NULL)
CREATE TABLE shows (night text PRIMARY KEY, title text NOT NULL)

SQL

SELECT s1.title AS "show", m1.name AS "member", b1.seats AS "seats"
FROM bookings AS b1
JOIN members AS m1 ON b1.member_id = m1.id
JOIN shows AS s1 ON b1.show_night = s1.night
LIMIT 100

Output matches the expected value

Runs in this page on the installed package. The input and the expected output are defined by the example.