Create custom field predicates

Marten has built-in support for common field predicates, but the framework also allows you to write your own. Custom predicates are useful when you need a lookup that does not map to exact, contains, gt, and the other built-in types. This can include a not-equal comparison, a regular expression match, or a database-specific operator.

Defining a field predicate

Field predicates are subclasses of the Marten::DB::Query::SQL::Predicate::Base abstract class. Each predicate class is responsible for:

• declaring the lookup name used in query sets (for example ne in title__ne)
• generating the SQL clause and bound parameters for that comparison

The lookup name is defined with #predicate_name. The SQL is generated by implementing #to_sql, which receives the current database connection and must return a tuple of {sql, params}.

The base class provides helpers that you should reuse from #to_sql:

• #sql_left_operand(connection) — the quoted column (or field transformation / annotation expression) on the left-hand side
• #sql_params(connection) — the bound parameters, with values converted through the field's #to_db method

caution

Built-in predicates get their SQL operators from each database connection. Custom predicate names are unknown to those mappings, so #to_sql must provide the operator itself. Relying on the default implementation will raise when the connection cannot find an operator for your lookup.

Example: a not-equal predicate

The following predicate adds a ne lookup that translates to a SQL <> comparison. That operator is supported by all official Marten database backends:

class NotEqual < Marten::DB::Query::SQL::Predicate::Base
  predicate_name "ne"

  def to_sql(connection : Marten::DB::Connection::Base)
    {"#{sql_left_operand(connection)} <> %s", sql_params(connection)}
  end
end

The %s placeholder in the SQL string is replaced with a backend-specific parameter marker when the query is executed. Do not interpolate the filter value into the SQL string; always pass it through the returned parameters.

Registering field predicates

In order to use a custom predicate in query sets, you must register it to Marten's global predicates registry.

To do so, call Marten::DB::Query::SQL::Predicate#register with the predicate class:

Marten::DB::Query::SQL::Predicate.register(NotEqual)

The lookup name comes from #predicate_name (here, ne). The call to #register can be made from anywhere in your codebase, but it must happen before the predicate is used in a query set.

Using custom field predicates

Once registered, the predicate is available as a double-underscore lookup on any field, the same way built-in predicates are:

Article.filter(title__ne: "Draft")

Because #sql_left_operand already applies field transformations, the same predicate works on transformed values:

Article.filter(released_on__year__ne: 2022)

Handling backend-specific SQL

Some lookups only exist on certain databases, or use a different operator per backend. In that case, branch on the connection class inside #to_sql.

For example, a case-sensitive regex predicate could be implemented as follows:

class Regex < Marten::DB::Query::SQL::Predicate::Base
  predicate_name "regex"

  def to_sql(connection : Marten::DB::Connection::Base)
    sql = case connection
          when Marten::DB::Connection::PostgreSQL
            "#{sql_left_operand(connection)} ~ %s"
          when Marten::DB::Connection::MySQL
            "#{sql_left_operand(connection)} REGEXP BINARY %s"
          when Marten::DB::Connection::SQLite
            "#{sql_left_operand(connection)} REGEXP %s"
          else
            raise Marten::DB::Errors::UnmetQuerySetCondition.new(
              "The regex predicate is not supported for this database backend"
            )
          end

    {sql, sql_params(connection)}
  end
end

Marten::DB::Query::SQL::Predicate.register(Regex)

Article.filter(title__regex: "^Top")

note

SQLite only provides a REGEXP operator if a corresponding function has been registered on the connection. If your project uses SQLite, make sure that function is available before using this predicate.

Customizing bound values

#sql_params converts the filter value through the field. You can map over those parameters when the bound value should differ from the value passed to #filter — for example to wrap a string for a LIKE pattern, using #sanitize_like_pattern to escape % and _:

class ContainsWord < Marten::DB::Query::SQL::Predicate::Base
  predicate_name "contains_word"

  def to_sql(connection : Marten::DB::Connection::Base)
    params = sql_params(connection).map do |param|
      "% #{connection.sanitize_like_pattern(param.to_s)} %"
    end

    {"#{sql_left_operand(connection)} LIKE %s", params}
  end
end

caution

LIKE syntax varies across backends: SQLite expects ESCAPE '\', and MySQL uses LIKE BINARY for case-sensitive matches. For LIKE-based predicates that must work on every backend, branch on the connection as shown in Handling backend-specific SQL, or reuse a built-in operator whose SQL is already backend-aware:

operator = connection.operator_for_predicate("contains") % "%s"
{"#{sql_left_operand(connection)} #{operator}", params}

Passing "contains" (a built-in predicate name) is required here: operator_for_predicate does not know about custom lookup names.