CODEWITHSUNDEEP
sqlruby-on-railsrubyoracle-databaseactiverecord
jaamun
jaamun

Reputation: 181

Rails Correlated Subquery Same table

My problem is converting the following SQL query to ActiveRecord Rails:

SELECT H1.* 
FROM USERS H1 
WHERE DAYS = (SELECT MAX(DAYS) FROM USERS H2
WHERE H1.COUNTRY = H2.COUNTRY
AND H1.NAME = H2.NAME
AND H1.MARKET = H2.MARKET
AND H1.TALL = H2.TALL)

My biggest issue here in converting this to Rails ActiveRecord is that the correlated subquery queries on the same table - "USERS" and aliasing the table names is illegal in ActiveRecord.

Find_by_sql is not an option for me either, since it cannot be used with other ActiveRecord functions.

Upvotes: 1

Views: 1286

Answers (2)

Arup Rakshit
Arup Rakshit

Reputation: 118271

You can write as

User.select("view.*")
    .from("User.group(:name, :country, :market, :tall)
    .maximum(:days), :view)")
    .where("days = ?", view.days)

Take a look at #from method to understand what's going on.

Upvotes: 2

Vikram Jain
Vikram Jain

Reputation: 5588

User.all(:select    => "users.COUNTRY, users.NAME, users.MARKET, users.TALL, users.DAYS",
         :joins     => "JOIN users AS H2 
                              ON H2.COUNTRY = users.COUNTRY AND 
                                 H2.NAME = users.NAME AND 
                                 H2.MARKET = users.MARKET AND 
                                 H2.TALL = users.TALL " ,
         :conditions => ["users.DAYS = H2.MAX(DAYS) "]
).each do |user|
  p "COUNTRY : #{user.COUNTRY}"
  p "NAME : #{user.NAME}"
  p "MARKET : #{user.MARKET}"
  p "TALL : #{user.TALL}"
  p "DAYS : #{user.DAYS}"
end

Upvotes: 1

Related Questions