WITH Queries by ActiveRecord::QueryMethods And Return ActiveRecord_Relation rails

Question

I'm using select in with query by find_by_sql:

scope :error_percentage, -> (user_obj) {
  find_by_sql("WITH products_boost_sum AS (SELECT *, (CASE WHEN (city_id =#{user_obj.user.city_id || -1}) THEN 1 ELSE 0 END) + (CASE WHEN (country_id =#{user_obj.country_id || -1}) THEN 1 ELSE 0 END) AS boost_sum FROM products) SELECT *, (CASE WHEN boost_sum = 2 THEN 2 ELSE 0 END) AS error_percentage FROM products_boost_sum")
}

The problem this scope is used by other scopes. So I need to return Active_Relation Object not an array. I checked the QueryMethods of ActiveRecord, but I can't find a method for with Query.

How can I use with Query to return ActiveRelation Object? If there no queryMethod for it, Is that available by select Method?

Answer 1

CTE queries are not supported by Active Record out of the box, but there is a gem that adds support https://github.com/DavyJonesLocker/postgres_ext.

Then you can do queries like

Score.with(my_games: Game.where(id: 1)).joins('JOIN my_games ON scores.game_id = my_games.id')