How to convert this raw query to ROM/Sequel?

questions
1

I have this raw query in my repository class. Could use some help with this to convert it to Sequel, thanks!

Can’t find much resources on it in Sequel or ROM, wonder if any expert could help

def by_parent_days(parent, days = nil)
  query_days = (days || DEFAULT_DAYS) - 1

  impressions.read(
    <<~SQL
      SELECT COUNT(id) as counts, MAX(created_at) as date_at
      FROM impressions
      WHERE created_at >= '#{query_days.days.ago.to_date}'::date AND #{column} = '#{value}'
      GROUP BY created_at::date AT TIME ZONE '#{Time.zone.name}'
      ORDER BY date_at ASC
    SQL
  ).map do |row|
    {
      count: row[:counts],
      date: row[:date_at].in_time_zone
    }
  end
end
2

I think I’m sticking with it in this format but data model changed slightly.
Suggestions are welcomed!

  def data_days(event_type, data_condition, days = DEFAULT_DAYS)
    query_days = (days || DEFAULT_DAYS)

    events.read(<<~SQL).map.to_a
      SELECT COUNT(id) as count, MAX(created_at) as date_at
      FROM events
      WHERE created_at >= '#{query_days.days.ago.to_date}'::date
      AND event_type = '#{event_type}'
      AND data @> '#{data_condition.to_json}'
      GROUP BY created_at::date AT TIME ZONE '#{Time.zone.name}'
      ORDER BY date_at ASC
    SQL
  end
3

@eli You can express it in Sequel too, but I’m not 100% sure it looks nicer :wink:

dataset[:impressions].
  select{ [Sequel.as(count(:id), :counts), Sequel.as(max(:created_at), :date_at)] }.
  where{ created_at >= Sequel[query_days.days.ago.to_date].cast(:date)}.
  where(column => value).
  group{ timezone(Time.zone.name, :created_at) }.
  order(Sequel.asc(:date_at))

With some sample data it gives me this SQL, which sould be equivalent to yours:

SELECT count("id") AS "counts", max("created_at") AS "date_at" FROM "impressions" WHERE (("created_at" >= CAST('2019-01-17 11:04:34.829679+0000' AS date)) AND ('test' = 5)) GROUP BY timezone('Europe/Helsinki', "created_at") ORDER BY "date_at" ASC