Clever Geek Handbook

Count and Select Object in ActiveRecord with 1 request

We have objects that we want to represent as stacks (think about how to stack items in an MMO). There will be duplicate lines.

Let's say our own_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 3 different OwnedObject

(or even just getting a great one Object

) and the counter associated with it.

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

+2


source to share


2 answers


What about...

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

... or similar?



Then you can get the scores using a dynamically created attribute for each object:

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

This assumes either has_and_belongs_to_many :objects

or has_many :objects, :through => :owned_objects

for the user.

+3


source


Found a solution, but not sure if it is the cleanest (hope not).



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

0


source






More articles:


All Articles