3

I have to update a search builder which builds a relation with CTE. This is necessary because a complex relation (which includes DISTINCT, JOINs etc) is first built and then it's results have to be ordered – all in one query.

Here's a simplified look at things:

rel = User.select('DISTINCT ON (users.id) users.*').where(<lotsastuff>)
rel.to_sql

# SELECT DISTINCT ON (users.id) users.*
#   FROM "users"
#   WHERE <lotsastuff>

rel2 = User.from_cte('cte_table', rel).order(:created_at)
rel2.to_sql

# WITH "cte_table" AS (
#   SELECT DISTINCT ON (users.id) users.*
#     FROM "users"
#     WHERE <lotsastuff>
# ) SELECT "cte_table".* FROM "cte_table"
#   ORDER BY "cte_table"."created_at" ASC

The beauty of it is that rel2 responds as expected e.g. to count.

The from_cte method is provided by the "posgres_ext" gem which appears to have been abandoned. I'm therefore looking for another way to build the relation rel2 from rel.

The Arel docs mention a case which doesn't seem to help here.

Any hints on how to get there? Thanks a bunch!

PS: I know how to do this with to queries by selecting all user IDs in the first, then build a query with IN over the IDs and order there. However, I'm curious whether this is possible with one query (with or without CTE) as well.

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
svoop
  • 3,318
  • 1
  • 23
  • 41
  • Why do you say it's been abandoned? (I hope not; I use that gem a lot!) Their README says "If you need help using the gem please ask the question on Stack Overflow. Be sure to tag the question with DockYard so we can find it." So maybe try that? – Paul A Jungwirth Mar 07 '18 at 20:32
  • The README also says it's "for Rails 4.x" and the last commit on Master dates back to July '17. Good point with the tag, however, not a encouraging sign that I had to create the tag since nobody has ever used it so far. – svoop Mar 08 '18 at 08:11
  • This article might be helpful http://sonnym.github.io/2017/06/05/common-table-expressions-in-activerecord-a-case-study-of-quantiles/ – Nick Mar 08 '18 at 11:49
  • Unrelated but curious on why do you need the distinct check on users. Isn't id the PK? – s1mpl3 Mar 08 '18 at 16:22
  • @s1mpl3 The above code is simplified, my real case is too complex to be posted here. – svoop Mar 09 '18 at 11:55

2 Answers2

2

Since your CTE is non-recursive, you can rewrite it as a subquery in the FROM clause. The only change is that Postgres's planner will optimize it as part of the main query instead of separately (because a CTE is an optimization fence). In ActiveRecord this works for me (tested on 5.1.4):

2.4.1 :001 > rel = User.select("DISTINCT ON (users.id) users.*").where("1=1")
2.4.1 :002 > puts User.from(rel, 'users').order(:created_at).to_sql
SELECT "users".* FROM (SELECT DISTINCT ON (users.id) users.* FROM "users" WHERE (1=1)) users ORDER BY "users"."created_at" ASC

I don't see any way to squeeze a CTE into ActiveRecord without extending it though, like what postgres_ext does. Sorry!

Paul A Jungwirth
  • 23,504
  • 14
  • 74
  • 93
0

From what you've mentioned, I did not understand why do you need to use CTE instead of just a nested query. 

rel = User.select('DISTINCT ON (users.id) users.*').where(<lotsastuff>).arel
inner_query = Arel::Table.new(:inner_query)
composed_cte = Arel::Nodes::As.new(inner_query, rel)
select_manager = Arel::SelectManager.new(composed_cte)
rel2 = select_manager.project('COUNT(*)')
rel2.to_sql
rel3 = select_manager.order('created_at ASC')
rel3.to_sql

you can then execute that sql

khaled_gomaa
  • 3,382
  • 21
  • 24
  • This can't work, but I've tried it nonetheless: `NoMethodError: undefined method 'order' for #` – svoop Feb 27 '18 at 07:49
  • My previous comment refers to a different answer which has been completely replaced meanwhile. Please update an answer rather than entirely replace it for the sake of transparency. Still, thanks for your answer! – svoop Mar 09 '18 at 11:53
  • that was the recommendation of stackoverflow for some reason. I tried to add a new answer. sorry though. – khaled_gomaa Mar 09 '18 at 15:51