Filtering dataset by a generated column

[tomasmiguez]

Hello,
we currently have a slight issue that was solved not optimally.
We have a model Expense that has associated Payments, both have an amount. We would like to know when an Expense is paid, for that we implemented a method:

class Expense < Sequel::Model
[...]
  def status
    if payments.sum(&:amount) == amount
      'PAID'
    else
      'UNPAID'
    end
  end
[...]
end

that works fine, the issue we encounter was when trying to filter. We expose an HTTP API that automatically generates payloads based on standard filtering syntax, which gets parsed to wheres on Sequel's datasets:

[...]
      def parse_eq(field, value)
        @dataset.where(field_expr(field) => to_value(field, value))
      end
[...]

If we wanted to filter by this status property, it wouldn't be possible as there is no mechanism (that I know of) to inject custom logic on dataset's where.

What we ended doing is having a plugin that extends the datasets methods with a custom filter, that each model can implement:

class Expense < Model
[...]
  dataset_module do # rubocop:disable Metrics/BlockLength
    def custom_filtered(filter_params)
      status_filter = filter_params&.delete('status')&.split('.')

      return where_status(status_filter.last) unless status_filter.nil?

      self
    end
  end
[...]
end

The issue with this implementation is that it requires duplicating the parsing of the filters logic inside each model, which is really undesirable for us.

Is there any mechanism or suggestion on how we might extend a dataset's where with our custom logic? We would love being able to do something like

@dataset.where(status: 'PAID')

and maybe that would call something like

class Expense < Model
[...]
  dataset_module do
    def filter_by_status(value)
       ...
    end
  end
[...]
end

or something like that?

[jeremyevans]

In your example, I'm not sure why you couldn't handle the :status field specially inside parse_eq.

To answer your general question about handling where keys specially, Sequel already has something like that for filtering by associations (

sequel/lib/sequel/model/associations.rb

Line 3061 in 96f5e93

def complex_expression_sql_append(sql, op, args)

). You could override complex_expression_sql_append as that code does to implement custom filtering. However, it wouldn't be as simple as calling filter_by_status.

A more direct approach would be overriding filter_expr:

  def filter_expr(expr, &block)
    if expr.is_a?(Hash) && expr.has_key?(:status)
      expr = expr.dup
      status_cond = expr.delete(:status)
      (expr.empty? ? (block ? super(&block) : self) : super).filter_by_status(status_cond)
    else
      super
    end
  end

It's likely possible to write a plugin that would allow something like:

  plugin :filter_transformer, status: :filter_by_status

[tomasmiguez]

I'm not sure why you couldn't handle the :status field specially inside parse_eq

If for each virtual column we implemented custom filtering on the general filter parser, it sounds like it could get hard to follow really fast. And status logic is specific to Expense, maybe we will have another model with a status as well that is persisted in the db, or calculated differently.

Those suggestions look good I'll give a shot to implementing a plugin with that structure, thanks!