Implement nested data inside a report using Datagrid (2 levels of report)

[helenatxu]

Hello! I created this question in StackOverflow but maybe you already thought about this.

I need to create two levels in a report, to show the number of samples sent, grouped by agent and everything grouped by type of sample.

I've already checked this solution, but it doesn't work for me because it is only for two headers in the table, while I need also the data to be nested.

So this is how it should look like:

+--------------------+--------------------+
| agent              | samples_sent_sum   |
+--------------------+--------------------+
| SampleType1        |   900              |
+--------------------+--------------------+
| Johny              |   300              |
| Sara               |   600              |
+--------------------+--------------------+

+--------------------+--------------------+
| agent              | samples_sent_sum   |
+--------------------+--------------------+
| SampleType2        |   200              |
+--------------------+--------------------+
| Johny              |   100              |
| Sara               |   50               |
| Danny              |   50               |
+--------------------+--------------------+

[bogdan]

I don't see a way you can embed all of that into one grid. I also don't know if and how you want to paginate this grid and weather you want to calculate group stats using SQL or in memory. The answer depends on the average and maximum number of rows in a group.

Suppose that you only want to paginate groups but not data inside the group.

In that case I would do the following:

class SamplesGrid
  scope {
    Sample.select('samples.type, SUM(samples.quantity_sent) as quantity_sent_sum').
    group('samples.type').order('samples.type desc')
  }

  column(:agent) { samples_type }
  column(:quantity_sent_sum)

  def agents_grid(sample_type)
    AgentsGrid.new do
      assets.select do |agent|
        agent.samples_type == sample_type
      end
    end
  end

  def agents
    @agents ||= Agent.joins(:sample_requests => [:samples]).
      select('samples.type, agents.name, sum(samples.quantity_sent) as quantity_sent_sum').
      group('agents.id').where(samples: {type: assets.map(&:samples_type)})
  end
end

class AgentsGrid
  scope {
    []
  }
  column(:agent) { name }
  column(:quantity_sent_sum)
end

# Test case for UI consistency
assert_equal AgentsGrid.column_names, GroupsGrid.column_names

# Controller
@grid = SamplesGrid.new(params[:samples_grid]) do |s|
  s.paginate(params[:page])
end

%table
  = datagrid_header @grid
  - @grid.assets.each do |asset|
    = datagrid_row @grid, asset
    = datagrid_rows @grid.agents_grid(asset.sample_type)

[helenatxu]

Thank you for your fast answer! I'm trying your idea as you proposed, but it gives me this error in the SamplesGrid class: undefined local variable or methodsamples_type'`

in this line of code: column(:agent) { samples_type }

Anyway, let's say I would need to call a method to format the name of the agent, like:

column(:agent, header: 'Agente') do |record|
    record.special_name
end

How this column(:agent) { samples_type } would work in there?

[bogdan]

I was assuming samples_type is a default name for select values. Give an explicit name to the values in select like samples.type as samples_type.

[helenatxu]

Hi @bogdan ! Thanks to your solution I came up with something that works for me.

Now the problem is that I need to add a filter by dates range, so when I use the filter it adds to the current scope the "where" clause with the dates, but each column calls a different function to perform some complex calculations depending on the type of sample. So the default behavior of the filter doesn't work for me. Is the a way for the range filter to just pass the data (the selected range in this case) and update the grid without adding a "where" clause (or anything) to the scope?

Thank you!

[bogdan]

Sure, you can pass an empty block (isn't it obvious?) or add dummy: true option. Any will work.

[helenatxu]

Sorry, maybe I didn't explain myself. When I use the filter, it adds automatically to the current scope a "where" clause with the dates. I don't want that, because it messes up my calculations. How can I avoid that? Also how can I update my data in the columns after that?

[bogdan]

Do you mean something like this?

class MyGrid
  scope { Post }
  filter(:period, :date, range: true, dummy: true)
  column(:number_of_comments_for_period) do |post, grid|
    post.comments.where(created_at: grid.period.first..grid.period.last).count
  end
end

[helenatxu]

It's more complex than that. I don't want the filter to automatically add anything to my scope. I want to get the dates range that the user enters in the datepicker, and pass this dates range somehow to the functions that my columns use to calculate the data they are displaying.

So this is one of my grids:

class MarketingReportsGrid
  include Datagrid

  scope {
    Sample.select('samples.kind').group('samples.kind').order('samples.kind desc')
  }

  filter(
    :date_sent, :date, {
      :range => true,
      :default => proc { [Date.today.last_month..Date.today] }
    }) do |value|
       update_columns_with_range?? <------- I don't know what to do here
  end

  column(:sample_kind, html: true, header: '') do |record|
    sample_kind_title(record)
  end

  column(:total_quantity_sent, html: true, header: 'Samples Sent') do |record, grid|
    get_number_pieces(record, grid)
  end

  column(:surface_samples_sent_box, html: true, header: 'm²') do |record, grid|
    get_surface_by_kind(record, grid)
  end

  def report_by_kind_grid(sample_kind)
    MarketingReportsGrid.new do
      assets.limit(nil).select do |asset|
        if asset.kind == sample_kind
          asset
        end
      end
    end
  end

end

And this is one of the functions in my helper:

def print_pieces(record, grid)

    if record.kind.nil?
      return 0
    end

    if record.attributes.has_key?('agent_id') && !record.agent_id.nil?
      count_pieces = Sample.joins(:sample_shippings, sample_request: [:marketing_project]).select('SUM(samples.quantity_sent) AS total_quantity_sent').where(:sample_requests => {status: ['shipped', 'delivered']}).where('marketing_projects.marketing_agent_id' => record.agent_id).where('samples.kind' => record.kind).where('sample_shippings.date_sent' => grid.date_sent)

else
      count_pieces = Sample.joins(:sample_shippings, :sample_request).select('SUM(samples.quantity_sent) AS total_quantity_sent').where(:sample_requests => {status: ['shipped', 'delivered']}).where('samples.kind' => record.kind).where('sample_shippings.date_sent' => grid.date_sent)
    end

    unless count_pieces.first.total_quantity_sent.nil?
      return count_pieces.first.total_quantity_sent
    end

    return 0
  end

The other function is more complex because depending on the type of the sample if needs to perform some calculations or another. That's why I put them on my helper.

The problem with the filter is that it adds a where clause to my current scope, and I don't want it to do that, and I am already getting my data from another function.

Also I don't know how I would pass to the columns this new data range from the filter.

Do you know understand what I'm trying to do? Thank you for you time @bogdan

[bogdan]

I understand that case. The thing I don't understand is why my proposal doesn't work for you? The goal for filter block is too apply somethings to the scope and you just need to force filter to not do anythings because the filter value is available in every method of the grid no matter what.

All you need is call helper method properly like grid.helper(record) not helper(record, grid).

Here is what you need to do:

class MarketingReportsGrid
  include Datagrid

  scope {
    Sample.select('samples.kind').group('samples.kind').order('samples.kind desc')
  }

  filter(
    :date_sent, :date, {
      :range => true,
      :default => proc { [Date.today.last_month..Date.today] },
      dummy: true
    })

  column(:total_print_prices, html: true) do |record, grid|
    grid.print_prices(record)
  end

  def print_pieces(record)
    if record.kind.nil?
      return 0
    end

    if record.attributes.has_key?('agent_id') && !record.agent_id.nil?
      count_pieces = Sample.joins(:sample_shippings, sample_request: [:marketing_project]).select('SUM(samples.quantity_sent) AS total_quantity_sent').where(:sample_requests => {status: ['shipped', 'delivered']}).where('marketing_projects.marketing_agent_id' => record.agent_id).where('samples.kind' => record.kind).where('sample_shippings.date_sent' => date_sent)

else
      count_pieces = Sample.joins(:sample_shippings, :sample_request).select('SUM(samples.quantity_sent) AS total_quantity_sent').where(:sample_requests => {status: ['shipped', 'delivered']}).where('samples.kind' => record.kind).where('sample_shippings.date_sent' => date_sent)
    end

    unless count_pieces.first.total_quantity_sent.nil?
      return count_pieces.first.total_quantity_sent
    end

    return 0
  end
end

[helenatxu]

Oh, sorry, I didn't understand what you meant with a dummy option :)

Your proposal looks good, I've tried it but it still doesn't update the values in the columns when another date range in selected in the datepicker.

When I select another dates, its creates a hash which is not exactly a data range, that's why my query doesn't like it.

This returns nil for any date: SampleShipping.where('sample_shippings.date_sent' => date_sent)

Because date_sent is like: [Tue, 01 Jan 2019, Wed, 22 May 2019] and it expects something like [Tue, 01 Jan 2019..Wed, 22 May 2019]

I'll try to find a workaround :)

[bogdan]

Datagrid uses [start, finish] to represent ranges instead of start..finish because start and finish can be nil. If don't worry about nils use:

def period_range
  date_sent.first..date_sent.last
end
SampleShipping.where('sample_shippings.date_sent' => period_range)

Otherwise handle them gracefully like:

scope :by_date_range, -> (column, start, end) {
  r = self
  r.where("#{column} >= ?", start) if start
  r.where("#{column} <= ?", end) if end
  r
}

SampleShipping.by_date_range('sample_shippings.date_sent', *date_sent)

[helenatxu]

That's awesome, thank you!

I'm trying the second option with named scopes, but as proposed it throws the error wrong number of arguments (given 2, expected 0), right in the first line: scope :by_date_range, -> (column, start, end) {

What I am missing here?

[bogdan]

Pass arguments explicitly:

SampleShipping.by_date_range('sample_shippings.date_sent', date_sent.first, date_sent.last)

[helenatxu]

That's not the cause of the error, because even if I don't call the named scope at all, it still gives me the same error. I think it could be because the original scope it's not compatible with the named scope:

 scope {
    Sample.select('samples.kind').group('samples.kind')
  }

[helenatxu]

So I finally achieved it with the first option and managing myself the validations.

Thank you again!