QueriesFilters & Joins

Query Filters & Joins

WHERE operators

where:
  user_id:      { eq:   input.user_id }
  status:       { eq:   "active" }
  price_cents:  { gte:  input.min_price }
  price_cents:  { lte:  input.max_price }
  attend_count: { gt:   0 }
  artist_id:    { neq:  input.excluded_artist }
  show_id:      { in:   input.show_ids }
  show_id:      { nin:  input.excluded_show_ids }
  title:        { like: input.title_pattern }
  venue:        { ilike: input.venue_search }
  purchased_at: { gte_window: input.since_ts }
OperatorSQL equivalent
eq= $param
neq!= $param
gt> $param
gte>= $param
lt< $param
lte<= $param
inIN ($param...)
ninNOT IN ($param...)
likeLIKE $param
ilikeILIKE $param
gte_window>= now() - $param (time window)

Qualify column names with the table when joining: user_following.user_id: { eq: input.user_id }.

Joins

Join projection tables together — no arbitrary SQL joins:

queries:
  shows_for_followed_artists:
    from: artist_show_directory
    joins:
      user_following:
        on:
          artist_show_directory.artist_id: user_following.artist_id
        fields:
          - user_following.user_id
        type: inner
    fields:
      - artist_show_directory.show_id
      - artist_show_directory.title
      - artist_show_directory.venue
      - artist_show_directory.date
    input:
      user_id: { type: string, required: true }
    where:
      user_following.user_id: { eq: input.user_id }
    order_by:
      artist_show_directory.date: asc
    limit: 50

Join types: inner (default), left, right.

The on: block maps primary_table.column: joined_table.column.

order_by

order_by:
  attended_at: desc
  artist_name: asc

Multiple entries apply in declaration order. Prefix column with table name when ambiguous.

limit

limit: 100

Always set a limit on production queries. Unbounded scans on large projection tables will time out under load.

distinct

queries:
  unique_artists_attended:
    from: user_concert_history
    input:
      user_id: { type: string, required: true }
    fields:
      - artist_id
    where:
      user_id: { eq: input.user_id }
    distinct: true

Use when a projection can produce duplicate rows and you only want unique values.

Next steps

Defining QueriesAggregates