ActiveRecord Query

Question

Anyone know how I can create this query using ActiveRecord rather than using SQL?

@leaderboard = ActiveRecord::Base.connection.execute("SELECT name, street, town, county,        avg(volume_used)  FROM `DBName`.`events
join DBName.households on idhouseholds = events.household_id 
group by household_id
order by volume_used ASC;");

Your ActiveRecord connection will be to a specific database. You'll need to connect to DBName (by specifying the correct params in database.yml) before running your query. — Ben
– Ben, Commented Jul 13, 2011 at 12:13

Ben · Accepted Answer · 2011-07-14 19:16:56Z

1

How about...

@leaderboard = Event.select([:name,:street,:town,:county,'avg(volume_used) as avg_used']).joins(:households).group('events.household_id').order('volume_used asc')

assuming that by idhouseholds you actually meant households.id and that you want to order by the aggregate average field you are selecting.

edited Jul 14, 2011 at 19:16

answered Jul 13, 2011 at 12:06

Ben

1,3491 gold badge16 silver badges31 bronze badges

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

5 Comments

user676567 Over a year ago

thanks Ben, tried that and its giving me incorrect averages and its dropping records too. Any other suggestions?

Ben Over a year ago

Can you post your table definitions?

user676567 Over a year ago

create_table :events do |t| t.integer :id t.datetime :start_time t.datetime :end_time t.float :volume_used t.integer :inferred_appliance_id t.integer :household_id t.timestamps end

user676567 Over a year ago

create_table :households do |t| t.string :name t.string :number t.string :street t.string :town t.string :county end

Ben Over a year ago

Thanks. I've updated the code in the answer (the order and group clauses weren't quite right) - give that a try.

Collectives™ on Stack Overflow

ActiveRecord Query

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related