metabase.mbql.util — metabase/mbql 1.4.1

Utilitiy functions for working with MBQL queries.

->joined-field clj

(->joined-field table-alias field-clause)

Inputs: [table-alias :- s/Str field-clause :- mbql.s/Field] Returns: mbql.s/JoinField

Convert a Field clause to one that uses an appropriate alias, e.g. for a joined table.

add-datetime-units clj

(add-datetime-units absolute-or-relative-datetime n)

Inputs: [absolute-or-relative-datetime :- mbql.s/DateTimeValue n :- s/Num] Returns: mbql.s/DateTimeValue

Return a relative-datetime clause with n units added to it.

add-filter-clause clj

(add-filter-clause outer-query new-clause)

Inputs: [outer-query :- mbql.s/Query new-clause :- (s/maybe mbql.s/Filter)] Returns: mbql.s/Query

Add an additional filter clause to an outer-query. If new-clause is nil this is a no-op.

add-order-by-clause clj

(add-order-by-clause inner-query [_ field :as order-by-clause])

Inputs: [inner-query :- mbql.s/MBQLQuery [_ field :as order-by-clause] :- mbql.s/OrderBy] Returns: mbql.s/MBQLQuery

Add a new :order-by clause to an MBQL inner-query. If the new order-by clause references a Field that is already being used in another order-by clause, this function does nothing.

aggregation-at-index clj

(aggregation-at-index query index)

(aggregation-at-index query index nesting-level)

Inputs: ([query index] [query :- mbql.s/Query index :- su/NonNegativeInt nesting-level :- su/NonNegativeInt]) Returns: mbql.s/Aggregation

Fetch the aggregation at index. This is intended to power aggregate field references (e.g. [:aggregation 0]). This also handles nested queries, which could be potentially ambiguous if multiple levels had aggregations. To support nested queries, you'll need to keep tract of how many :source-querys deep you've traveled; pass in this number to as optional arg nesting-level to make sure you reference aggregations at the right level of nesting.

combine-filter-clauses clj

(combine-filter-clauses filter-clause & more-filter-clauses)

Inputs: [filter-clause & more-filter-clauses] Returns: mbql.s/Filter

Combine two filter clauses into a single clause in a way that minimizes slapping a bunch of :ands together if possible.

datetime-arithmetics? clj

(datetime-arithmetics? clause)

Is a given artihmetics clause operating on datetimes?

datetime-but-not-time-field? clj

(datetime-but-not-time-field? field)

Is field used to record a specific moment in time, i.e. does field have a base type or special type that derives from :type/DateTime but not :type/Time?

datetime-field? clj

(datetime-field? field)

Is field used to record something date or time related, i.e. does field have a base type or special type that derives from :type/DateTime?

For historical reasons :type/Time derivies from :type/DateTime, meaning this function will still return true for Fields that record only time. You can use datetime-but-not-time-field? instead if you want to exclude time Fields.

deduplicate-join-aliases clj

(deduplicate-join-aliases joins)

Inputs: [joins :- [mbql.s/Join]] Returns: mbql.s/Joins

Make sure every join in :joins has a unique alias. If a :join does not already have an alias, this will give it one.

desugar-current-relative-datetime clj

(desugar-current-relative-datetime m)

Replace relative-datetime clauses like [:relative-datetime :current] with [:relative-datetime 0 <unit>]. <unit> is inferred from the :datetime-field the clause is being compared to (if any), otherwise falls back to default.

desugar-does-not-contain clj

(desugar-does-not-contain m)

Rewrite :does-not-contain filter clauses as simpler :not clauses.

desugar-equals-and-not-equals-with-extra-args clj

(desugar-equals-and-not-equals-with-extra-args m)

:= and != clauses with more than 2 args automatically get rewritten as compound filters.

[:= field x y] -> [:or [:= field x] [:= field y]] [:!= field x y] -> [:and [:!= field x] [:!= field y]]

desugar-filter-clause clj

(desugar-filter-clause filter-clause)

Inputs: [filter-clause :- mbql.s/Filter] Returns: mbql.s/Filter

Rewrite various 'syntatic sugar' filter clauses like :time-interval and :inside as simpler, logically equivalent clauses. This can be used to simplify the number of filter clauses that need to be supported by anything that needs to enumerate all the possible filter types (such as driver query processor implementations, or the implementation negate-filter-clause below.)

desugar-inside clj

(desugar-inside m)

Rewrite :inside filter clauses as a pair of :between clauses.

desugar-is-null-and-not-null clj

(desugar-is-null-and-not-null m)

Rewrite :is-null and :not-null filter clauses as simpler := and :!=, respectively.

desugar-time-interval clj

(desugar-time-interval m)

Rewrite :time-interval filter clauses as simpler ones like := or :between.

dispatch-by-clause-name-or-class clj

(dispatch-by-clause-name-or-class x)

Dispatch function perfect for use with multimethods that dispatch off elements of an MBQL query. If x is an MBQL clause, dispatches off the clause name; otherwise dispatches off x's class.

expression-with-name clj

(expression-with-name {inner-query :query} expression-name)

Inputs: [{inner-query :query} :- mbql.s/Query expression-name :- (s/cond-pre s/Keyword su/NonBlankString)] Returns: mbql.s/FieldOrExpressionDef

Return the Expression referenced by a given expression-name.

field-clause->id-or-literal clj

(field-clause->id-or-literal clause)

Inputs: [clause :- mbql.s/Field] Returns: (s/cond-pre su/IntGreaterThanZero su/NonBlankString)

Get the actual Field ID or literal name this clause is referring to. Useful for seeing if two Field clauses are referring to the same thing, e.g.

(field-clause->id-or-literal [:datetime-field [:field-id 100] ...]) ; -> 100 (field-clause->id-or-literal [:field-id 100]) ; -> 100

For expressions (or any other clauses) this returns the clause as-is, so as to facilitate the primary use case of comparing Field clauses.

ga-id? clj

(ga-id? id)

Is this ID (presumably of a Metric or Segment) a GA one?

ga-metric-or-segment? clj

(ga-metric-or-segment? [_ id])

Is this metric or segment clause not a Metabase Metric or Segment, but rather a GA one? E.g. something like [:metric ga:users]. We want to ignore those because they're not the same thing at all as MB Metrics/Segments and don't correspond to objects in our application DB.

is-clause? clj

(is-clause? k-or-ks x)

If x an MBQL clause, and an instance of clauses defined by keyword(s) k-or-ks?

(is-clause? :count [:count 10]) ; -> true (is-clause? #{:+ :- :* :/} [:+ 10 20]) ; -> true

match clj macro

(match x & patterns-and-results)

Return a sequence of things that match a pattern or patterns inside x, presumably a query, returning nil if there are no matches. Recurses through maps and sequences. pattern can be one of several things:

• Keyword name of an MBQL clause
• Set of keyword names of MBQL clauses. Matches any clauses with those names
• A core.match pattern
• A symbol naming a class.
• A symbol naming a predicate function
• _, which will match anything

Examples:

;; keyword pattern (match {:fields [[:field-id 10]]} :field-id) ; -> [[:field-id 10]]

;; set of keywords (match some-query #{:field-id :fk->}) ; -> [[:field-id 10], [:fk-> [:field-id 10] [:field-id 20]], ...]

;; core.match patterns: ;; match any :field-id clause with one arg (which should be all of them) (match some-query [:field-id _]) (match some-query [:field-id (_ :guard #(> % 100))]) ; -> [[:field-id 200], ...]

;; symbol naming a Class ;; match anything that is an instance of that class (match some-query java.util.Date) ; -> [[#inst "2018-10-08", ...]

;; symbol naming a predicate function ;; match anything that satisfies that predicate (match some-query (every-pred integer? even?)) ; -> [2 4 6 8]

;; match anything with _ (match 100 _) ; -> 100

Using core.match patterns

See core.match documentation for more details.

Pattern-matching works almost exactly the way it does when using core.match/match directly, with a few differences:

• mbql.util/match returns a sequence of everything that matches, rather than the first match it finds

• patterns are automatically wrapped in vectors for you when appropriate

• things like keywords and classes are automatically converted to appropriate patterns for you

• this macro automatically recurses through sequences and maps as a final :else clause. If you don't want to automatically recurse, use a catch-all pattern (such as _). Our macro implementation will optimize out this :else clause if the last pattern is _

Returing something other than the exact match with result body

By default, match returns whatever matches the pattern you pass in. But what if you only want to return part of the match? You can, using core.match binding facilities. Bind relevant things in your pattern and pass in the optional result body. Whatever result body returns will be returned by match:

;; just return the IDs of Field ID clauses (match some-query [:field-id id] id) ; -> [1 2 3]

You can also use result body to filter results; any nil values will be skipped:

(match some-query [:field-id id] (when (even? id) id)) ;; -> [2 4 6 8]

Of course, it's more efficient to let core.match compile an efficient matching function, so prefer using patterns with :guard where possible.

You can also call recur inside result bodies, to use the same matching logic against a different value.

&match and &parents anaphors

For more advanced matches, like finding :field-id clauses nested anywhere inside :datetime-field clauses, match binds a pair of anaphors inside the result body for your convenience. &match is bound to the entire match, regardless of how you may have destructured it; &parents is bound to a sequence of keywords naming the parent top-level keys and clauses of the match.

(mbql.u/match {:fields [[:datetime-field [:fk-> [:field-id 1] [:field-id 2]] :day]]} :field-id ;; &parents will be [:fields :datetime-field :fk->] (when (contains? (set &parents) :datetime-field) &match)) ;; -> [[:field-id 1] [:field-id 2]]

match-one clj macro

(match-one x & patterns-and-results)

Like match but returns a single match rather than a sequence of matches.

maybe-unwrap-field-clause clj

(maybe-unwrap-field-clause clause)

Unwrap a Field clause, if it's something that can be unwrapped (i.e. something that is, or wraps, a :field-id or :field-literal). Otherwise return clause as-is.

mbql-clause? clj

(mbql-clause? x)

True if x is an MBQL clause (a sequence with a keyword as its first arg). (Since this is used by the code in normalize this handles pre-normalized clauses as well.)

negate-filter-clause clj

(negate-filter-clause filter-clause)

Inputs: [filter-clause :- mbql.s/Filter] Returns: mbql.s/Filter

Return the logical compliment of an MBQL filter clause, generally without using :not (except for the string filter clause types). Useful for generating highly optimized filter clauses and for drivers that do not support top-level :not filter clauses.

normalize-token clj

(normalize-token token)

Inputs: [token :- su/KeywordOrString] Returns: s/Keyword

Convert a string or keyword in various cases (lisp-case, snake_case, or SCREAMING_SNAKE_CASE) to a lisp-cased keyword.

pre-alias-aggregations clj

(pre-alias-aggregations aggregation->name-fn aggregations)

Inputs: [aggregation->name-fn :- (s/pred fn?) aggregations :- [mbql.s/Aggregation]] Returns: [NamedAggregation]

Wrap every aggregation clause in an :aggregation-options clause, using the name returned by (aggregation->name-fn ag-clause) as names for any clauses that do not already have a :name in :aggregation-options.

(pre-alias-aggregations annotate/aggregation-name [[:count] [:count] [:aggregation-options [:sum [:field-id 1] {:name "Sum-41"}]]) ;; -> [[:aggregation-options [:count] {:name "count"}] [:aggregation-options [:count] {:name "count"}] [:aggregation-options [:sum [:field-id 1]] {:name "Sum-41"}]]

Most often, aggregation->name-fn will be something like annotate/aggregation-name, but for purposes of keeping the metabase.mbql module seperate from the metabase.query-processor code we'll let you pass that in yourself.

pre-alias-and-uniquify-aggregations clj

(pre-alias-and-uniquify-aggregations aggregation->name-fn aggregations)

Inputs: [aggregation->name-fn :- (s/pred fn?) aggregations :- [mbql.s/Aggregation]] Returns: UniquelyNamedAggregations

Wrap every aggregation clause in a :named clause with a unique name. Combines pre-alias-aggregations with uniquify-named-aggregations.

qualified-name clj

(qualified-name x)

Like name, but if x is a namespace-qualified keyword, returns that a string including the namespace.

query->max-rows-limit clj

(query->max-rows-limit
  {{:keys [max-results max-results-bare-rows]} :constraints
   {limit :limit aggregations :aggregation {:keys [items]} :page} :query
   query-type :type})

Calculate the absolute maximum number of results that should be returned by this query (MBQL or native), useful for doing the equivalent of

java.sql.Statement statement = ...; statement.setMaxRows(<max-rows-limit>).

to ensure the DB cursor or equivalent doesn't fetch more rows than will be consumed.

This is calculated as follows:

• If query is MBQL and has a :limit or :page clause, returns appropriate number
• If query has :constraints with :max-results-bare-rows or :max-results, returns the appropriate number
  • :max-results-bare-rows is returned if set and Query does not have any aggregations
  • :max-results is returned otherwise
• If none of the above are set, returns nil. In this case, you should use something like the Metabase QP's max-rows-limit

query->source-table-id clj

(query->source-table-id
  {{source-table-id :source-table source-query :source-query} :query
   query-type :type
   :as query})

Inputs: [{{source-table-id :source-table, source-query :source-query} :query, query-type :type, :as query}] Returns: (s/maybe su/IntGreaterThanZero)

Return the source Table ID associated with query, if applicable; handles nested queries as well. If query is nil, returns nil.

Throws an Exception when it encounters a unresolved source query (i.e., the :source-table "card__id" form), because it cannot return an accurate result for a query that has not yet been preprocessed.

relative-date clj

(relative-date unit amount t)

Return a new Temporal value relative to t using a relative date unit.

(relative-date :year -1 (t/zoned-date-time "2019-11-04T10:57:00-08:00[America/Los_Angeles]")) ;; -> (t/zoned-date-time "2020-11-04T10:57-08:00[America/Los_Angeles]")

replace clj macro

(replace x & patterns-and-results)

Like match, but replace matches in x with the results of result body. The same pattern options are supported, and &parents and &match anaphors are available in the same way. (&match is particularly useful here if you want to use keywords or sets of keywords as patterns.)

replace-in clj macro

(replace-in x ks & patterns-and-results)

Like replace, but only replaces things in the part of x in the keypath ks (i.e. the way to update-in works.)

simplify-compound-filter clj

(simplify-compound-filter filter-clause)

Simplify compound :and, :or, and :not compound filters, combining or eliminating them where possible. This also fixes theoretically disallowed compound filters like :and with only a single subclause, and eliminates nils and duplicate subclauses from the clauses.

time-field? clj

(time-field? field)

Is field used to record a time of day (e.g. hour/minute/second), but not the date itself? i.e. does field have a base type or special type that derives from :type/Time?

unique-name-generator clj

(unique-name-generator)

Return a function that can be used to uniquify string names. Function maintains an internal counter that will suffix any names passed to it as needed so all results will be unique.

(let [unique-name (unique-name-generator)] [(unique-name "A") (unique-name "B") (unique-name "A")]) ;; -> ["A" "B" "A_2"]

uniquify-named-aggregations clj

(uniquify-named-aggregations named-aggregations)

Inputs: [named-aggregations :- [NamedAggregation]] Returns: UniquelyNamedAggregations

Make the names of a sequence of named aggregations unique by adding suffixes such as _2.

uniquify-names clj

(uniquify-names names)

Inputs: [names :- [s/Str]] Returns: (s/constrained [s/Str] distinct? "sequence of unique strings")

Make the names in a sequence of string names unique by adding suffixes such as _2.

(uniquify-names ["count" "sum" "count" "count_2"]) ;; -> ["count" "sum" "count_2" "count_2_2"]

unwrap-field-clause clj

(unwrap-field-clause clause)

Inputs: [clause :- mbql.s/Field] Returns: (s/if (partial is-clause? :field-id) mbql.s/field-id mbql.s/field-literal)

Un-wrap a Field clause and return the lowest-level clause it wraps, either a :field-id or :field-literal.

update-in-unless-empty clj

(update-in-unless-empty m ks f & args)

Like update-in, but only updates in the existing value is non-empty.