CODEWITHSUNDEEP
sqlruby-on-railsrubyactiverecord
mikelikespie
mikelikespie

Reputation: 5842

Count and Select Object in ActiveRecord with 1 query

We have objects that we want to represent in stacks (think of stacking items in an MMO). There will be duplicate rows.

Let's say our owned_objects table looks like this.

user_id | object_id
1       | 27
1       | 27
3       | 46
3       | 46
5       | 59

I want the query to do

SELECT
  user_id,
  object_id,
  count(*) AS count
FROM owned_objects
GROUP BY
  user_id,
  object_id;

And return either the 3 distinct OwnedObjects (or even just getting the distinct Objects would work too) and a count associated with it.

I know this is possible with SQLAlchemy, but can you do it with ActiveRecord?

Upvotes: 2

Views: 2364

Answers (2)

cwninja
cwninja

Reputation: 9778

How about …

@objects = user.objects.all(:select => "count(*) as count, objects.*", :group => :object_id)

… or similar?

You can then retrieve the counts by a dynamically created attribute on each object:

@object.first.count # the "stack depth" of the first object.

This assumes either a has_and_belongs_to_many :objects or a has_many :objects, :through => :owned_objects on user.

Upvotes: 3

mikelikespie
mikelikespie

Reputation: 5842

Found a solution, but not sure if it's the cleanest (hope it isn't).

Basically I created a SQL view that does that query and created a model for it. There's a plugin for rails that recognizes views on migrations.

Upvotes: 0

Related Questions