CODEWITHSUNDEEP
sqlruby-on-rails-3activerecordassociationsarel
user191766
user191766

Reputation:

Rails complex queries from SQL to AREL

For someone who is coming from a non ActiveRecord environment, complex queries are challenging. I know my way quite well in writing SQL's, however I'm having difficulties figuring out how to achieve certain queries in solely AREL. I tried figuring out the examples below by myself, but I can't seem to find the correct answers.

Here are some reasons as to why I'd opt for the AREL way instead of my current find_by_sql-way:

Here are the simplified version of my models:

class Support::Forum < ActiveRecord::Base
  has_many :topics

  def self.top
    Support::Forum.find_by_sql "SELECT forum.id, forum.title, forum.description, SUM(topic.replies_count) AS count FROM support_forums forum, support_topics topic WHERE forum.id = topic.forum_id AND forum.group = 'theme support' GROUP BY forum.id, forum.title, forum.description ORDER BY count DESC, id DESC LIMIT 4;"
  end

  def ordered_topics
    Support::Topic.find_by_sql(["SELECT topics.* FROM support_forums forums, support_topics topics, support_replies replies WHERE forums.id = ? AND forums.id = topics.forum_id AND topics.id = replies.topic_id GROUP BY topics.id ORDER BY topics.pinned DESC, MAX(replies.id) DESC;", self.id])
  end

  def last_topic
    Support::Topic.find_by_sql(["SELECT topics.id, topics.title FROM support_forums forums, support_topics topics, support_replies replies WHERE forums.id = ? AND forums.id = topics.forum_id AND topics.id = replies.topic_id GROUP BY topics.id, topics.title, topics.pinned ORDER BY MAX(replies.id) DESC LIMIT 1;", self.id]).first
  end
end

class Support::Topic < ActiveRecord::Base
  belongs_to :forum, counter_cache: true
  has_many :replies
end

class Support::Reply < ActiveRecord::Base
  belongs_to :topic, counter_cache: true
end

Whenever I can, I try to write stuff like this via AREL and not in SQL (for the reasons mentioned before), but I just can't get my head around the non-basic examples such as the ones above.

Fyi I'm not really looking for straight conversions of these methods to AREL, any directions or insight towards a solution are welcome. Another remark if you however think this is perfectly acceptable solution to write these queries with an sql-finder, please share your thoughts.

Note: If I need to provide additional examples, please say so and I will :)

Upvotes: 2

Views: 883

Answers (1)

the8472
the8472

Reputation: 43042

For anything that doesn't require custom joins or on clauses - i.e. can be mapped to AR relations - you might want to use squeel instead of arel. AREL is basically a heavyweight relational algebra DSL which you can use to write SQL queries from scratch in ruby. Squeel is more of a fancier DSL for active record queries that eliminates most cases where you would use SQL literal statements.

Upvotes: 1

Related Questions