CODEWITHSUNDEEP
sqlruby-on-railspostgresqlarel
Luan Gonçalves Barbosa
Luan Gonçalves Barbosa

Reputation: 1245

Convert SQL statement to Arel Rails query

So, let's get straight: I'm struggling to days do convert this SQL Statement:

select * from (
    select distinct on (program_id) editions.*, programs.* 
    from editions inner join programs on editions.program_id = programs.id 
    where programs.station_id = 1) as editionsprograms 
order by fixed desc, published_at desc, time desc 
limit 4;

to an Arel rails query. Someone has any idea about how could make an Arel query that would have the same effect of the above SQL?

Upvotes: 0

Views: 1533

Answers (2)

across
across

Reputation: 546

Recently I found this cool project http://www.scuttle.io/
scuttle - sql to arel converter

Upvotes: 1

Robert Nubel
Robert Nubel

Reputation: 7522

You can use the from method to accomplish the tricky part of your query (selecting from a subquery). The subquery itself should be a straightforward mapping (select, joins, where, etc). So the result would be something like this:

Edition.select('*')
.from(
  Editions.select('DISTINCT ON (program_id) editions.*, programs.*')
          .joins(:programs)
          .where(programs: { station_id: 1 })
)
.order(fixed: :desc, published_at: :desc, time: :desc)
.limit(4)

This will return Edition records, with additional data from the Program record stored on each. To avoid the weirdness of that result type, which may also just throw errors and be unable to resolve, you could either use Arel directly (similar syntax) or use .pluck on the end to pluck only the columns you actually need.

Upvotes: 1

Related Questions