How do you use postgresql WITH in activerecord?
In postgresql, you can read data from "c". I want to know how to use this in rails without putting the whole query in raw sql ..
Here's a sample request: it's 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, the score is calculated not only 1, but for example it works.
This is how I think the code will look like
Task.with('tasks as (select 1 as score, tasks.* from tasks)')
.where('id > 10')
.order(score)
.order(id)
I don't really like using "c" because it is PG specific, but I really need to sort by the computed value. I tried view, but exact fields are required to create a view in PG, and I don't want other coders to have to change the view when they change the original table.
I really want to be able to relate this.
source to share
I don't believe this is supported in pure ActiveRecord without dropping the original 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 addition myself (I'd rather go down to the original SQL in this situation), but it looks like it will allow you to track down chaining behavior.
Once you have this set, you want to use its method from_cte
to define the CTE (operator WITH
) and then chaining, however you want to filter, sort, etc.
So, I think the code will look something like this:
Task.from_cte('tasks', Task.where('id > 10')).order('tasks.score, tasks.id')
Note that the chaining starts after the call to the from_cte
object is complete ActiveRecord::Relation
.
Edit in response to comment from OP:
Ok, you could add more to the chaining internally from_cte
, I guess - it really depends on what you want the CTE to do. You could of course filter the user in the method where
, so that the CTE just contained the user's individual tasks.
source to share