I'm trying to find the best way to do a Postgres query with Common Table Expressions in a Rails app, knowing that apparently ActiveRecord doesn't support CTEs.
I have a table called user_activity_transitions which contains a series of records of a user activity being started and stopped (each row refers to a change of state: e.g started or stopped).
One user_activity_id might have a lot of couples started-stopped, which are in 2 different rows.
It's also possible that there is only "started" if the activity is currently going on and hasn't been stopped. The sort_key starts at 0 with the first ever state and increments by 10 for each state change.
id to_state sort_key user_activity_id created_at
1 started 0 18 2014-11-15 16:56:00
2 stopped 10 18 2014-11-15 16:57:00
3 started 20 18 2014-11-15 16:58:00
4 stopped 30 18 2014-11-15 16:59:00
5 started 40 18 2014-11-15 17:00:00
What I want is the following output, grouping couples of started-stopped together to be able to calculate duration etc.
user_activity_id started_created_at stopped_created_at
18 2014-11-15 16:56:00 2014-11-15 16:57:00
18 2014-11-15 16:58:00 2014-11-15 16:59:00
18 2014-11-15 17:00:00 null
The way the table is implemented makes it much harder to run that query but much more flexible for future changes (e.g new intermediary states), so that's not going to be revised.
My Postgres query (and the associated code in Rails):
query = <<-SQL
with started as (
select
id,
sort_key,
user_activity_id,
created_at as started_created_at
from
user_activity_transitions
where
sort_key % 4 = 0
), stopped as (
select
id,
sort_key-10 as sort_key2,
user_activity_id,
created_at as stopped_created_at
from
user_activity_transitions
where
sort_key % 4 = 2
)
select
started.user_activity_id AS user_activity_id,
started.started_created_at AS started_created_at,
stopped.stopped_created_at AS stopped_created_at
FROM
started
left join stopped on stopped.sort_key2 = started.sort_key
and stopped.user_activity_id = started.user_activity_id
SQL
results = ActiveRecord::Base.connection.execute(query)
What it does is "trick" SQL into joining 2 consecutive rows based on a modulus check on the sort key.
The query works fine. But using this raw AR call annoys me, especially since what connection.execute returns is quite messy. I basically need to loop through the results and put it in the right hash.
2 questions:
Bear in mind that I'm quite new to Rails and not a query expert so there might be an obvious improvement...
Thanks a lot!
I'm trying to find the best way to do a Postgres query with Common Table Expressions in a Rails app, knowing that apparently ActiveRecord does support CTEs.
As far as I know ActiveRecord doesn't support CTE. Arel, which is used by AR under the hood, supports them, but they're not exposed to AR's interface.
Is there a way to get rid of the CTE and run the same query using Rails magic?
Not really. You could write it in AR's APIs but you'd just write the same SQL split into a few method calls.
If not, is there a better way to get the results I want in a nice-looking hash?
I tried to run the query and I'm getting the following which seems nice enough to me. Are you getting a different result?
[
{"user_activity_id"=>"18", "started_created_at"=>"2014-11-15 16:56:00", "stopped_created_at"=>"2014-11-15 16:57:00"},
{"user_activity_id"=>"18", "started_created_at"=>"2014-11-15 16:58:00", "stopped_created_at"=>"2014-11-15 16:59:00"},
{"user_activity_id"=>"18", "started_created_at"=>"2014-11-15 17:00:00", "stopped_created_at"=>nil}
]
I assume you have a model called UserActivityTransition you use for manipulating the data. You can use the model to get the results as well.
results = UserActivityTransition.find_by_sql(query)
results.size # => 3
results.first.started_created_at # => 2014-11-15 16:56:00 UTC
Note that these "virtual" attributes will not be visible when inspecting the result but they're there.