MBQL join syntax

joins.clj

(def JoinStrategy
  "Strategy that should be used to perform the equivalent of a SQL `JOIN` against another table or a nested query.
  These correspond 1:1 to features of the same name in driver features lists; e.g. you should check that the current
  driver supports `:outer-join` before generating a Join clause using that strategy."
  (s/enum :left-join :right-join :inner-join :outer-join))

(def Join
  "Perform the equivalent of a SQL `JOIN` with another Table or nested `:source-query`. JOINs are either explicitly
  specified in the incoming query, or implicitly generated when one uses a `:fk->` clause.

  In the top-level query, you can reference Fields from the joined table or nested query by the `:fk->` clause for
  implicit joins; for explicit joins, you *must* specify `:alias` yourself; you can then reference Fields by using a
  `:joined-field` clause, e.g.

    [:joined-field \"my_join_alias\" [:field-id 1]]                 ; for joins against other Tabless
    [:joined-field \"my_join_alias\" [:field-literal \"my_field\"]] ; for joins against nested queries"
  (->
   { ;; The condition on which to JOIN. Can be anything that is a valid `:filter` clause. For automatically-generated
    ;; JOINs this is always
    ;;
    ;;    [:= <source-table-fk-field> [:joined-field <join-table-alias> <dest-table-pk-field>]]
    ;;
    :condition                     Filter
    ;;
    ;; *What* to JOIN. Self-joins can be done by using the same `:source-table` as in the query where this is specified.
    ;; YOU MUST SUPPLY EITHER `:source-table` OR `:source-query`, BUT NOT BOTH!
    (s/optional-key :source-table) su/IntGreaterThanZero
    (s/optional-key :source-query) (s/recursive #'Query)
    ;;
    ;; Defaults to `:left-join`; used for all automatically-generated JOINs
    (s/optional-key :strategy)     JoinStrategy
    ;;
    ;; The Fields to include in the results *if* a top-level `:fields` clause *is not* specified. This can be either
    ;; `:none`, `:all`, or a sequence of Field clauses.
    ;;
    ;; *  `:none`: no Fields from the joined table or nested query are included (unless indirectly included by
    ;;    breakouts or other clauses). This is the default, and what is used for automatically-generated joins.
    ;;
    ;; *  `:all`: will include all of the Fields from the joined table or query
    ;;
    ;; *  a sequence of Field clauses: include only the Fields specified. Valid clauses are the same as the top-level
    ;;    `:fields` clause. This should be non-empty and all elements should be distinct. The normalizer will
    ;;    automatically remove duplicate fields for you, and replace empty clauses with `:none`.
    (s/optional-key :fields)       (s/cond-pre
                                    (s/enum :all :none)
                                    (su/distinct (su/non-empty [Field])))
    ;;
    ;; The name used to alias the JOINed table or query. This is usually generated automatically and generally looks
    ;; like `table__via__field`. You can specify this yourself if you need to reference a JOINed field in a
    ;; `:joined-field` clause
    (s/optional-key :alias)        su/NonBlankString
    ;;
    ;; put other stuff in here (such as an ID for UI purposes), we don't care
    s/Keyword                      s/Any}
   (s/constrained
    (fn [{:keys [source-table source-query]}]
      (u/xor source-table source-query))
    "Joins can must have either a `source-table` or `source-query`, but not both.")))

The joins themselves go in

{(s/optional-key :joins) (su/non-empty [Join])}

inside the MBQL query map. e.g.

{:database 1, :type :query, :query {:joins [join-1 join-2]}}