How do you use the postgresql WITH in activerecord?

Question

In postgresql one can read data from a "with" I want to know how to make use of that in rails without putting the whole query in raw sql..

Here's a sample query: it is totally contrived for this question.

with tasks as (select 1 as score, tasks.* from tasks)
select 1 from tasks where id > 10 order by tasks.score, tasks.id

In my real example score is calculated not just 1 but for the example it works.

Here's how I imagine the code would look

Task.with('tasks as (select 1 as score, tasks.* from tasks)')
    .where('id > 10')
    .order(score)
    .order(id)

I don't really like using the "with" because it is PG specific, but I really need to sort on the calculated value. I tried a view but the creation of the view in PG requires the exact fields, and I don't want other coders to have to alter the view when they alter the source table.

I do really want to be able to chain this.

Answer 1

I don't believe this is supported in pure ActiveRecord without dropping down to raw SQL.

However, there is an add-on, postgres_ext which, among other things, adds CTE support for use with ActiveRecord. I haven't used this add-on myself (I would prefer to drop down into raw SQL in this situation), but it looks like it would allow the chaining behavior you are looking for.

Once you've installed that, you'd want to use its from_cte method to define the CTE (the WITH statement), and then chain however you'd like to filter, sort, etc.

So I think the code would look something like this:

Task.from_cte('tasks', Task.where('id > 10')).order('tasks.score, tasks.id')

Note that the chaining starts after the from_cte call has completed on the ActiveRecord::Relation object that results.

Edit in response to comment from OP:

Well, you could add more to the chaining within the from_cte, I think -- it really depends on what you want the CTE to be. You could certainly also filter by user in the where method such that the CTE just contained a single user's tasks.