4

I am using Rails 5.2.2 and have a complex query using a 'WITH' statement that I need to create with a left outer join.

How do I do a WITH statement in active record?

My TOTAL_PROFILES, is driven with a Query object and will change, while the rest will always be constant. So a basically want to wrap my query object results with the WITH statement.

Here is a simplified SQL example and hoping someone can guide me to the best way to accomplish this in rails, as I see no way to do it. I have a bunch of inner joins on the TOTAL_PROFILE table, but don't show them here.

WITH TOTAL_PROFILES
AS (
     SELECT profiles.*
           ,hobbies.*
     FROM profiles
     INNER JOIN 
                 "profiles" 
              ON "hobbies"."hobbyable_id"      = "profiles"."id" 
             AND "hobbies"."hobbyable_type"    = $1 
     WHERE (profiles.latitude  ( BETWEEN 42.055160808444576 
                               AND 42.19989259155542) )
)
, FAVORITE_FOR_USER
    AS (
        SELECT "favorites".* 
          FROM "favorites" 
         WHERE "favorites"."user_id"    = $1 
           AND "favorites"."profile_id" = $2 
         ORDER BY 
               "favorites"."id" ASC LIMIT $3 
       )    
SELECT F."user_id"
     , CASE 
            WHEN F."profile_id" IS NULL
            THEN 0
            ELSE 1
             END                          AS IS_FAVORITE_FOR_USER 
     , T.*           
  FROM 
       TOTAL_PROFILES     T
  LEFT JOIN
       FAVORITE_FOR_USER  F
    ON T."profile_id" = F."profile_id"

How can this be converted into ActiveRecord, or Arel?

a_sql = ProfilesQuery::Search.call(:location => 'New York, NY').to_sql
params = []
params << User.first.id # 1
user_id_param = params.length

wrapper_query = <<~SQL
 SELECT F."user_id"
     , (F."profile_id" IS NOT NULL) AS IS_FAVORITE_FOR_USER
     , T.*
 FROM (#{a_sql}) T
 LEFT JOIN favorites F ON (F.profile_id = T.profile_id AND F."user_id" = $#{user_id_param})
SQL

conn = ActiveRecord::Base.connection.raw_connection
conn.prepare('my_query', wrapper_query )
result = conn.exec_prepared('my_query', ['1'])``
Community
  • 1
  • 1
user2012677
  • 5,465
  • 6
  • 51
  • 113
  • See https://stackoverflow.com/q/46286924/479863, https://stackoverflow.com/q/45091388/479863, and other "arel cte" search results. – mu is too short Apr 07 '19 at 18:26
  • @muistooshort DEPRECATION WARNING: Delegating ast to arel is deprecated and will be removed in Rails 6.0. What are the alternatives? – user2012677 Apr 07 '19 at 19:54
  • This is also concerning, for any answer I am finding: https://blog.bigbinary.com/2018/10/16/rails-5-2-disallows-raw-sql-in-active-record.html – user2012677 Apr 07 '19 at 19:58
  • Sorry, I don't know the alternatives, I find AREL code to be an incomprehensible mess for the most part so I avoid it and often go straight to `find_by_sql` with `connection.quote` calls to make things safe. – mu is too short Apr 07 '19 at 21:15
  • Quote method seems to be deprecated too. Quote_value seems to replace it. What is it suppose to do? – user2012677 Apr 07 '19 at 22:43
  • The quoting methods properly quote/escape strings to prevent SQL injection problems. Some of the deprecation issues might be solved with `Arel.sql(...)` wrappers and/or `.arel` method calls (or might not). – mu is too short Apr 08 '19 at 02:47

0 Answers0