CODEWITHSUNDEEP
mysqlsqlruby-on-railsactiverecord
zoras
zoras

Reputation: 953

Convert SQL to ActiveRecord Query

How can I convert following SQL query to ActiveRecord Query in order to mitigate SQL injection?

jacket_colors ||= [2,21,25,20]
jacket_types = JacketType.find_by_sql(<<-SQL)
  SELECT j2.*, t1.no_count
   FROM jeans j2
   INNER JOIN (
     SELECT
     j1.jean_id AS jean_id,
     COUNT(j1.id) AS no_count
     FROM tracks t
     INNER JOIN jacket_types j1 ON j1.track_id = t.id
     INNER JOIN jeans j2 ON j2.id = j1.jean_id
     WHERE t.status = 0
       AND j1.status IN (#{jacket_colors})
       AND t.type != 'TrekkingTrack'
     GROUP BY j1.jean_id
     HAVING COUNT(j1.id) > 0
   ) t1 ON t1.jean_id = j2.id
 SQL

jacket_types varies with user input.

I tried the following but this doesn't work and produces incorrect SQL.

jacket_colors ||= [2,21,25,20]

Jean.joins(:jacket_types, :track)
.select('jeans.jacket_types_id AS jacket_types_id, COUNT(jeans.id) AS no_count').
where('jeans.status IN (?) AND tracks.status = ? AND tracks.type != ?', jacket_colors, 0, 'TrekkingTrack')
.group('jeans.jacket_types_id')
.having('COUNT(jeans.id) > ?', 0)
.select('jacket_types.*, tracks.no_count').explain

Upvotes: 1

Views: 2543

Answers (1)

uma
uma

Reputation: 2972

Please try it, I think it will work.

Rails Query version:

jacket_colors ||= [2,21,25,20]

Jean.joins(:jacket_types => :track)
 .where('jeans.status IN (?) AND tracks.status = ? AND tracks.type != ?',
 jacket_colors, 0, 'TrekkingTrack').group('jeans.jacket_types_id')
.select('jacket_types.column1, jacket_types.column2, ....., 
 count(jacket_types) no_count').having("no_count > 0")

of

jacket_colors ||= [2,21,25,20]
jacket_types = JacketType.find_by_sql(<<-SQL)
  SELECT j2.*, t1.no_count
  FROM jeans j2
    INNER JOIN (
      SELECT
      j1.jean_id AS jean_id,
      COUNT(j1.id) AS no_count
      FROM tracks t
        INNER JOIN jacket_types j1 ON j1.track_id = t.id
        INNER JOIN jeans j2 ON j2.id = j1.jean_id
        WHERE t.status = 0
        AND j1.status IN (#{jacket_colors})
        AND t.type != 'TrekkingTrack'
        GROUP BY j1.jean_id
        HAVING COUNT(j1.id) > 0
   ) t1 ON t1.jean_id = j2.id
 SQL

Upvotes: 2

Related Questions