I am, unfortunately, not familiar enough with arel to even make a go at this feature.
I have a lot of code that uses common table expressions, but with lists of values. Right now, making that work requires putting the values into a set of select statements that are unioned together, or the use of Postgres array functions.
Let’s say I have some data that I want to use in a common table expression, in this case a list of employee ids and scores:
employee_id,score
1,100
2,95
3,80
I already have an employees table:
# create table employees (id serial, name varchar);
CREATE TABLE
# insert into employees values (1,'Jim'), (2,'Carol'), (3,'Bob');
INSERT 0 3
# select * from employees;
id | name
----+-------
1 | Jim
2 | Carol
3 | Bob
(3 rows)
If I have the ids and scores in a hash, I can add them to a common table expression using Postgres array functionality:
# select unnest(array[1,2,3]) as employee_id, unnest(array[100,95,80]) as score;
employee_id | score
-------------+-------
1 | 100
2 | 95
3 | 80
(3 rows)
That allows me to add these to a common table expression and reference them as a table:
# with employee_scores as (select unnest(array[1,2,3]) as employee_id, unnest(array[100,95,80]) as score) select id, name, score from employees inner join employee_scores on employee_id=id;
id | name | score
----+-------+-------
1 | Jim | 100
2 | Carol | 95
3 | Bob | 80
(3 rows)
In Rails, it would look something like this:
Employee.with(“employee_scores” => “select unnest(array[1,2,3]) as employee_id, unnest(array[100,95,80]) as score”).joins(“inner join employee_scores on employee_id=id”).select(“id”, “name”, “score”)
The array functionality is unique to Postgres, so a more generalized method of doing the same thing:
# select 1 as id, 100 as score
# union
# select 2 as id, 95 as score
# union
# select 3 as id, 80 as score;
id | score
----+-------
1 | 100
3 | 80
2 | 95
(3 rows)
And that can be used in a common table expression as well.
But there’s another generalized way of handling this:
# with employee_scores (employee_id, score) as (values (1,100), (2,95), (3,80))
select id, name, score from employees inner join employee_scores on employee_id=id;
id | name | score
----+-------+-------
1 | Jim | 100
2 | Carol | 95
3 | Bob | 80
(3 rows)
The issue here is that the column names must appear between the CTE “table” name and the word “AS”.
I would like to propose that we extend Arel such that a CTE that is an array of hashes, each with a consistent key set, gets turned into that particular format. In this case, it would look like this:
employee_scores = [
{ employee_id: 1, score: 100 },
{ employee_id: 2, score: 95 },
{ employee_id: 3, score: 80 }
]
Employee.with(“employee_scores” => employee_scores).joins(“inner join employee_scores on employee_id=id”).select(“id”, “name”, “score”)
This would create this SQL:
with employee_scores (employee_id, score) as (values (1,100), (2,95), (3,80)) select id, name, score from employees inner join employee_scores on employee_id=id;
This is a general, cross-platform way of handling the CTE elegantly, and it doesn’t clash with the current method of allowing an Arel expression in the “with” clause.
Thoughts?