Converting SQL query to Rails query

Question

I could just use a find_by_sql to accomplish this, but I'd like to do it the Rails way if possible.

My SQL query looks like this:

SELECT regions.id, max(updated_at) FROM demographics 
JOIN regions ON (regions.id = demographics.region_id) 
JOIN region_stats ON (region_stats.region_id = regions.id) 
WHERE region_stats.income_level = 1    
GROUP BY demographics.region_id;

Is there a way to convert this cleanly to Rails?

It's not clear what table the field updated_at belongs to, can you clarify? — PinnyM
– PinnyM, Commented Oct 10, 2012 at 20:58

iouri · Accepted Answer · 2012-10-10 20:49:28Z

1

Untested:

Demographic.joins(:region => [:region_stats]).select('demographics.region_id, max(demographics.updated_at) as `max_date`').where(:region_stats => {:income_level => 1}).group('demographics.region_id')

answered Oct 10, 2012 at 20:49

iouri

2,9291 gold badge17 silver badges11 bronze badges

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

Comments

Teddy · Accepted Answer · 2012-10-10 20:39:45Z

1

Demographic.find(:all,
  :conditions=>[
    "region_stats.income_level = ?",
    1
  ],
  :joins=>"as demographics JOIN regions ON (regions.id = demographics.region_id) JOIN region_stats ON (region_stats.region_id = regions.id)",
  :group=>"demographics.region_id",
  :select=>"regions.id, demographics.max(updated_at)"
)

answered Oct 10, 2012 at 20:39

Teddy

18.6k4 gold badges33 silver badges43 bronze badges

Collectives™ on Stack Overflow

Converting SQL query to Rails query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related