Join Series

Part 1 of the report series

This feature produces a join of your main query against a generated_series() function result. The main motivation behind this feature was the generation of reports. More specifically, reports using created_at that would originally cause undesired roles in the output. PostgreSQL Docs.

You can disable this feature using config.join_series.

How to

The join_series will basically generate a proper join between the generated_series() PG function, using a Range as base, and the column provided on the with: argument.

# SELECT "users".* FROM "users" INNER JOIN GENERATE_SERIES(0::integer, 100::integer) AS series ON "series" = "users"."age"
User.join_series(0..100, with: :age)

The range can be of any type, including dates and times. Here is a list of all options that can be used to configure this operation:

• with: The column on the ON clause of the join;
• as: The name of the generated values;
• step: The step of the series generation. When using with dates and times, a duration (like 1.day) must be provided;
• mode: The type of the join to use. Valid values are: :inner for INNER JOIN, :left for LEFT OUTER JOIN, :right for RIGHT OUTER JOIN, and :full for FULL OUTER JOIN;
• time_zone: A time zone value or attribute. This extra parameter only works with PostgreSQL >= 16.0.
• { |series, table| } When provided with a block, you can customize exactly how the ON clause will be handled.

Here is a full example that will count the number of users created each day for January 2025.

range = Date.new(2025, 1, 1)..Date.new(2025, 1, 31)
User.join_series(range , step: 1.day) do |series, table|
  series.pg_cast(:date).eq(table['created_at'].pg_cast(:date))
end.group('series').count

SELECT COUNT(*) AS "count_all", series AS "series"
FROM "users"
         INNER JOIN GENERATE_SERIES('2025-01-01 00:00:00'::timestamp, '2025-01-31 00:00:00'::timestamp, 'P1D'::interval) AS series
                    ON "series"::date = "users"."created_at"::date
GROUP BY series;