Convert SQL queries to Activerecord query

Question

How to convert this into active records queries?

@test = @keyword_list.find_by_sql("
  SELECT keywords.keyword, minimum_ranking.min_position from keywords
  JOIN (select keyword_id, min(position) as min_position from rankings group by keyword_id) minimum_ranking
  on minimum_ranking.keyword_id = keywords.id
  where created_at between ('2019-10-15 07:00:00') AND ('#{date}')
")

Not only that before these code, i already have 2 layer of association

   @project = Project.find(params[:id])
   @keywords = @project.keywords
   @pagy, @keyword_list = pagy(@keywords.includes(:rankings).order(current_ranking: :asc), items: 50)

At the moment i been running loop and .where on the views, which making loading the page extremely slow. i am trying to tackle this issue by making a single sql queries in the controller, managed to wrote it in sql but can't figure out to do it in Active Record.

For those of us who don't "do" SQL but who might be able to sort out your ActiveRecord query, it might be helpful if you (1) show your models with the relevant associations and attributes, and (2) describe in plain English what it is you're trying to do. — jvillian

Jay Dorsey Jay Dorsey · Accepted Answer · 2019-10-23T14:48:53

Have a look at https://scuttle.io to get started:

Keyword.select(
  [
    Keyword.arel_table[:keyword], MinimumRanking.arel_table[:min_position]
  ]
).where(
  Arel::Nodes::Between.new(
    :created_at, (
      Arel::Nodes::Group.new(LITERAL<'2019-10-15 07:00:00'>)
    ).and(Arel::Nodes::Group.new(LITERAL<'#{date}'>))
  )
).joins(
  Keyword.arel_table.join(Ranking.arel_table).on(
    MinimumRanking.arel_table[:keyword_id].eq(Keyword.arel_table[:id])
  ).join_sources
)

Convert SQL queries to Activerecord query

1 Answers