There are times when our database structure is not enough to retrieve specific information. Let’s see a use case!
We have table packages that includes at least two columns: starts_at and ends_at. And we want to know the records that match anniversaries from starts_at to ends_at given a time range. Therefore, any anniversary that matches the given period. That means that we don’t have the records of the anniversaries in the database and we must generate it.
Therefore, a common solution is to have a years table with year column as primary_key. It will have the years from the start of business life until the current day. Another solution would be to create it on demand with a temporary table, however a physical one will work a bit quicker. Anyway, we will need to make a join of these years and the packages to retrieve the anniversaries within the subcriptions.
In this case, we will use a temporary database table (using MySQL) for it:
def anniversaries_on_subscription(from, to)
begin
drop = "DROP TEMPORARY TABLE IF EXISTS years;"
create = "CREATE TEMPORARY TABLE years(year INT PRIMARY KEY);"
insert = "INSERT INTO years VALUES (2010),(2011),(2012),(2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020),(2021),(2022),(2023),(2024),(2025),(2026),(2027),(2028);"
ActiveRecord::Base.connection.exec_query(drop)
ActiveRecord::Base.connection.exec_query(create)
ActiveRecord::Base.connection.exec_query(insert)
### SQL QUERY HERE ###
ensure
ActiveRecord::Base.connection.exec_query(drop)
end
end
As you can see we just make sure that always remove temporary tables. It is only visible inside the connection from which they are created, and they are automatically dropped when the connection no longer exists.
Therefore, any Active Record class could reach that table during the same controller request. But in practice, we can safely do it from an active record class as long as we ensure that we drop it when we do not need it anymore.
Regarding the query, we make a join query between both tables getting firstly years from starts_at to ends_at to obtain anniversaries for WHERE clause:
Package.find_by_sql("
SELECT DISTINCT final.id FROM (
SELECT id, DATE_ADD(MAKEDATE(y.year, 1), INTERVAL DAYOFYEAR(starts_at)-1 DAY) AS anniversary, starts_at, ends_at
FROM packages AS after_packages
INNER JOIN years AS y
ON y.year BETWEEN YEAR(starts_at) AND YEAR(ends_at)
) AS final
WHERE
((anniversary BETWEEN '#{from}' AND '#{to}')
AND (ends_at NOT BETWEEN '#{from}' AND '#{to}')
AND (anniversary > starts_at)
AND (anniversary < ends_at));
")
Obviously there is no way to get that using usually Active Record methods. But it still allows us to use raw sql directly and obtain ActiveRecord::Relation thanks to the find_by_sql method.
And this would be:
def anniversaries_on_subscription(from, to)
begin
drop = "DROP TEMPORARY TABLE IF EXISTS years;"
create = "CREATE TEMPORARY TABLE years(year INT PRIMARY KEY);"
insert = "INSERT INTO years VALUES (2010),(2011),(2012),(2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020),(2021),(2022),(2023),(2024),(2025),(2026),(2027),(2028);"
ActiveRecord::Base.connection.exec_query(drop)
ActiveRecord::Base.connection.exec_query(create)
ActiveRecord::Base.connection.exec_query(insert)
Package.find_by_sql("
SELECT DISTINCT final.id FROM (
SELECT id, DATE_ADD(MAKEDATE(y.year, 1), INTERVAL DAYOFYEAR(starts_at)-1 DAY) AS anniversary, starts_at, ends_at
FROM packages AS after_packages
INNER JOIN years AS y
ON y.year BETWEEN YEAR(starts_at) AND YEAR(ends_at)
) AS final
WHERE
((anniversary BETWEEN '#{from}' AND '#{to}')
AND (ends_at NOT BETWEEN '#{from}' AND '#{to}')
AND (anniversary > starts_at)
AND (anniversary < ends_at));
")
ensure
ActiveRecord::Base.connection.exec_query(drop)
end
end
This is just an example of how we can always use Active Record. Yes, we use SQL queries directly, but we easily retrieve the records the way we need for this specific scenario.