1

I'm new to Rails and Arel I created a join query but looks poorly written

join_query = ActiveRecord::Base.connection.execute <<~SQL.strip_heredoc ( SELECT schools.name, sum(q.enrollment), count(*) FROM schools LEFT JOIN ( SELECT sites.school_id, sites.enrollment FROM sites JOIN projects ON sites.project_id = projects.id WHERE sites.school_id IS NOT NULL and projects.type IN ('Startup') AND projects.deleted_at IS NULL ) q ON q.school_id = schools.id GROUP BY schools.name ) SQL join_query.values

How can this be written using Arel?
Also, could you point me out to a good tutorial in this subject?
Thanks in advance

Improve this question

1 Answer 1

Reset to default
2

This is what I could come up with the given SQL:

schools = Arel::Table.new("schools") sites = Arel::Table.new("sites") projects = Arel::Table.new("projects") join = sites.project(:school_id, :enrollment) .join(projects).on(sites[:project_id].eq(projects[:id])) .where( sites[:school_id].not_eq(nil) .and(projects[:type].in("Startup")) .and(projects[:deleted_at].eq(nil)) ) q = Arel::Table.new(join).alias(:q) schools.project(:name, q[:enrollment].sum, Arel.star.count) .join(join, Arel::Nodes::OuterJoin).on(q[:school_id].eq(schools[:id])) .group(schools[:name])

Being a newbie, these are some of the references I often visit:

https://www.rubydoc.info/github/rails/arel (official docs)

https://devhints.io/arel (a very useful cheatsheet but not complete)

https://gist.github.com/mildmojo/3724189 (example of left outer join)

https://jpospisil.com/2014/06/16/the-definitive-guide-to-arel-the-sql-manager-for-ruby.html (comprehensive guide to Arel)

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Awesome! Thanks to you I finally make it work. Aliasing did the trick. I make gist from what I learn gist.github.com/foton/9cc80c5d85c274fb08af17699536a516

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.