ActiveRecord: complex query with Sum, Join, Group By and Select multiple fields

Vote count:

0

I've waisted few days for this query but still not figured out the solution.

There are my models

class UserObject < ActiveRecord::Base
  self.table_name = "user_objects"
  belongs_to :user, class_name: 'User'
  belongs_to :the_object, class_name: 'Object'
end

class Object < ActiveRecord::Base
  self.table_name = 'objects'

  has_many :user_objects, class_name: 'UserObject', foreign_key: :the_object_id, inverse_of: :the_object
  has_many :users, through: :user_objects
end

class User < ActiveRecord::Base
  self.table_name = "users"
end

Here is my schema

  create_table "objects", force: true do |t|
    t.float    "importance",        default: 0.5, null: false
  end

  create_table "user_objects", force: true do |t|
    t.integer  "user_id"
    t.integer  "the_object_id"
    t.float    "score",              default: 0.0,   null: false
  end

  create_table "users", force: true do |t|
    t.string   "first_name"
    t.string   "last_name"
  end

I need a query for select objects.importance and sum of user_objects.score. But also I have to select for query only those objects which belongs to user1 and user2.

I wrote a query for select objects.importance

Object.select("objects.imporatnce").joins(:user_objects).
                                    where(user_objects: {user_id: [user1_id,user2_id]}).
                                    group(objects: :id).
                                    having('COUNT("user_objects"."id")=2')

But I still don't know how to count in this query sum of user_objects.score. Next query doesn't work

Mobima::Object.select("objects.id, SUM(user_objects.score)").
               joins(:user_objects).
               where(user_objects: {user_id: [user1_id,user2_id]}).
               group(objects: :id).having('COUNT("user_objects"."id")=2')

I would greatly appreciate any help you can give me in working this problem.

asked 29 secs ago

brytannia

50

ActiveRecord: complex query with Sum, Join, Group By and Select multiple fields