Metabase::Query - Generic Metabase query language role


version 1.003


  package Metabase::Query::SQLite;

  use Moose;
  with 'Metabase::Query';

  # define Moose attributes

  sub prepare {
    my ( $self, $query ) = @_;

    # extract '-where' translate to SQL
    my $pred = $query->{-where};
    my $where = !$pred ? "" : "WHERE " . $self->dispatch_query_op($pred);

    # extract '-limit' and '-order' and translate to SQL

    return "$where $order $limit";

  sub op_eq {
    my ( $self, @args ) = @_;
    return [ "$field = ?", $arg[1] ];

  sub op_and {
    my ( $self, @args ) = @_;

    my @predicates =  map { "($_)" }
                      map { $self->dispatch_query_op($_) } @args;

    return join(" AND ", @predicates);

  # ... implement all other required ops ...


This role describes the simplified query language for use with Metabase and defines the necessary methods to implement it for any particular Metabase backend.

A query is expressed as a data structure of the form:

    -where => [ $operator => @arguments ]
    -order => [ $direction => $field, ... ]
    -limit => $number,

Arguments to an operator must be scalar values, or in the case of logic operators, must be array references of operator/argument pairs.

Where clauses

A where clause predicate must be given as an arrayref consisting of an operator name and a list of one or more arguments.

  -where => [ $operator => @arguments ]

Some operators take a field name as the first argument. A field name must match the expression qr/\A[a-z._]+\z/i

Logic operators

Logic operators take predicates as arguments. The -and and -or operators take a list of predicates. The -not operator takes only a single predicate as an argument.

  [ -and => @predicates ]
  [ -or  => @predicates ]
  [ -not => $one_predicate ]

Comparison operators

Most comparison operators are binary and take two arguments. The first must be the field name to which the operation applies. The second argument must be a non-reference scalar value that the operation is comparing against.

  [ -eq => $field => $value ] # equal
  [ -ne => $field => $value ] # not equal
  [ -gt => $field => $value ] # greater than
  [ -ge => $field => $value ] # greater than or equal to
  [ -lt => $field => $value ] # less than
  [ -le => $field => $value ] # less than or equal to

The exception is the -between operator, which takes a field, a low value and a high value:

  [ -between => $field => $low, $high ]

Matching operator

The matching operator provides rudimentary pattern matching.

  [ -like => $field => $match_string ]

The match string specifies a pattern to match. A percent sign (%) matches zero or more characters and a period (.) matches a single character.

Order clauses

A desired order of results may be specified with an array reference containing direction and field name pairs. Field names must follow the same rules as for "Where clauses". Valid directions are -asc and -desc.

  -order => [ -asc => $field1 ]
  -order => [ -asc => $field1, -desc => $field2 ]

Not all backend will support mixed ascending and descending field ordering and backends may throw an error if ordering is not possible.

Limit clauses

A limit on the number of results returned is specified by a simple key-value pair:

  -limit => NUMBER

The number must be a non-negative integer. A given backend should make a best efforts basis to respect the limit request, but the success of a limit request may be constrained by the nature of a particular backend index.



  $result = $self->dispatch_query_op([-eq => $field, $value]);

Validates that a predicate has a valid operator name, validates the arguments are correctly specified, and dispatches to the appropriate method for the operator name (e.g. op_eq).


  $result = $self->get_native_query( $query );
  @result = $self->get_native_query( $query );

Translates the Metabase query data structure into a backend-native scalar (string, data-structure, etc). It validates the structure of the query and then calls the translate_query method, which must be provided by the class that implements this role.

To support the old key-value API, any keys that do not match -where, -order, or -limit and do not begin with a minus sign will be treated as field names and appended to a -where parameter as equality checks.

The translate_query method will be called with the same context (scalar or list) as the call to get_native_query.


Here is an example example query to return the 10 most recent CPAN Testers reports by a single submitter (specified by creator URI), excluding 'NA' reports:

    -where => [
      -and =>
        [ -eq => 'core.creator' => $creator_uri ],
        [ -eq => 'core.type' => 'CPAN-Testers-Report'],
    -order => [ -desc => 'core.update_time' ],
    -limit => 10,



  my $native = $self->translate_query( $query );

This method should take a query data structure in the form described in this document and return a backend-native query scalar (WHERE/ORDER/LIMIT clauses or comparable data structure). In practice, this means calling dispatch on individual predicates and assembling the results appropriately.

Operator methods

Classes implementing this role must provide the following methods to implement the query operations in the appropriate backend-specific syntax.

  • op_not

  • op_or

  • op_and

  • op_eq

  • op_ne

  • op_gt

  • op_lt

  • op_ge

  • op_le

  • op_like

  • op_between


  • David Golden <>

  • Ricardo Signes <>

  • Leon Brocard <>


This software is Copyright (c) 2013 by David Golden.

This is free software, licensed under:

  The Apache License, Version 2.0, January 2004