Ruby on Rails – How Do I Added a Where Clause to My Raw SQL Query? – Postgresql

In my Rails app, I have this scope which returns the Articles that have received the most views by joining the Ahoy Events table and my Articles table which is working.

  # article.rb
  scope :trending, (lambda do |time|
    joins("
      INNER JOIN (
        #{Ahoy::Event.where(time: time).to_sql}
      ) AS ahoy_events_by_time
      ON (ahoy_events_by_time.properties->>'id')::Integer = articles.id
    ")
    .select('articles.*, COUNT(ahoy_events_by_time.id) AS views_count')
    .group('articles.id')
    .order(Arel.sql("COUNT(#{count}) DESC"))
  end)

 # controller
 @articles = Article.trending(30.days.ago..Time.zone.now)

I want to do something similar for my Submissions now. However, I want the scope to be include another attribute (submission.contest_id) to further limit the scope.

So I’m trying to edit this now to include contest_id so only the submissions for a given Contest show – so something like this:

@submissions = Submission.trending(30.days.ago..Time.zone.now, @contest.id)

     scope :trending, (lambda do |time|
        joins("
          INNER JOIN (
            #{Ahoy::Event.where(time: time).to_sql}
          ) AS ahoy_events_by_time
          ON (ahoy_events_by_time.properties->>'id')::Integer = submissions.id
        ")
        .select("submissions.*, COUNT(ahoy_events_by_time.id) AS views_count")
        .group('submissions.id')
        .order(Arel.sql("COUNT(#{count}) DESC"))
  end)

What I’d like to do now is pass in another value (@submission.contest_id) so only certain Submissions are included in the query.

So I’m trying to just pass in the contest_id as another param:

@submissions = Submission.trending(30.days.ago..Time.zone.now, @contest.id)

However, I’m doing something wrong because once I try to add contest_id, two things are incorrect.

1. It no longer shows all of the submissions that have that contest_id (if I simply do @submissions = @contest.submissions instead of the trending scope, it shows all of them).
2. It isn’t returning the submissions returned according to the view count (it’s not in the correct order)

I’ve looked at this SO question: Rails Raw SQL WHERE clause

And I’ve tried changing the scope to:

# this results in not all submissions being returned, and an incorrect sort order
  scope :trending, (lambda do |time, contest_id|
    joins("
      INNER JOIN (
        #{Ahoy::Event.where(time: time).to_sql}
      ) AS ahoy_events_by_time
      ON (ahoy_events_by_time.properties->>'id')::Integer = submissions.id
    ")
    .select("submissions.*, COUNT(ahoy_events_by_time.id) AS views_count")
    .where(contest_id: contest_id)
    .group('submissions.id')
    .order(Arel.sql("COUNT(#{count}) DESC"))
  end)

    # this gets a syntax error PG::SyntaxError: ERROR:  syntax error at or near ","
    # LINE 1: SELECT submissions.* WHERE(contest_id = 2), COUNT(ahoy_event...
    
    scope :trending, (lambda do |time, contest_id|
        joins("
          INNER JOIN (
            #{Ahoy::Event.where(time: time).to_sql}
          ) AS ahoy_events_by_time
          ON (ahoy_events_by_time.properties->>'id')::Integer = submissions.id
        ")
        .select("submissions.* WHERE(contest_id = #{contest_id}), COUNT(ahoy_events_by_time.id) AS views_count")
        .where(contest_id: contest_id)
        .group('submissions.id')
        .order(Arel.sql("COUNT(#{count}) DESC"))
      end)

    # I've tried adjusting the commas (to put one before `WHERE`, and to also remove all commas so it reads like `submissions.* WHERE(contest_id = #{contest_id}) COUNT(ahoy_events_by_time.id)`, but the syntax is incorrect still.

I’ve tried looking through the Rails Guides, I could figure out the right way to write this in the Active Record Query Interface Guide (I couldn’t find another guide that dealt with querying the DB).

Thanks in advance for any help!