Rails - how to join/group nested tables and get joined values out?

Question

I have the following hierarchy of models where each one has_many of the one below it:

class AccountGroup < ActiveRecord::Base
  has_many :accounts, :inverse_of=>:account_group
  # name: string

class Account < ActiveRecord::Base
  belongs_to :accountGroup, :inverse_of=>:account
  has_many :positions, :inverse_of=>:account

class Position < ActiveRecord::Base
  belongs_to :account, :inverse_of=>:positions
  # net_position: integer

In other words, an AccountGroup contains a bunch of Accounts, and an Account contains a bunch of Positions.

Goal: I want an hash of AccountGroup => (sum of its net_positions). That means there's a GROUP BY involved.

I can do this with raw SQL, but I haven't cracked it with Rails functions. The raw SQL is:

SELECT account_groups.id,SUM(net_position),account_groups.name
FROM account_groups
LEFT JOIN accounts ON accounts.account_group_id = account_groups.id
LEFT JOIN positions ON positions.account_id = accounts.id
GROUP BY account_groups.id,account_groups.name;

Is this something that Rails just can't do?

Answer 1

Rails (4.0.0) can do this - we have two ways to do it currently:

---

1. SQL "Alias" Columns

Rails Scoping For has_many :through To Access Extra Data

#Images
has_many :image_messages, :class_name => 'ImageMessage'
has_many :images, -> { select("#{Image.table_name}.*, #{ImageMessage.table_name}.caption AS caption") }, :class_name => 'Image', :through => :image_messages, dependent: :destroy

---

2. ActiveRecord Association Extensions

This is a little-known feature of Rails, which allows you to play with the collection object. The way it does it is to extend the has_many relationship you have created:

class AccountGroup < ActiveRecord::Base
  has_many :accounts do
      def X
          #your code here
      end
  end
end

We have only got this method working for collections, but you can do all sorts with it. You should look at this tutorial to see more about it

---

Update

We just got this working by using an extension module:

#app/models/message.rb
Class Message < ActiveRecord::Base
    has_many :image_messages #-> join model
    has_many :images, through: :image_messages, extend: ImageCaption
end

#app/models/concerns/image_caption.rb
module ImageCaption

    #Load
    def load
      captions.each do |caption|
        proxy_association.target << caption
      end
    end

    #Private
    private

    #Captions
    def captions
        return_array = []
        through_collection.each_with_index do |through,i|
            associate = through.send(reflection_name)
            associate.assign_attributes({caption: items[i]})
            return_array.concat Array.new(1).fill( associate )
        end
        return return_array
    end

    #######################
    #      Variables      #
    #######################

    #Association
    def reflection_name
        proxy_association.source_reflection.name
    end

    #Foreign Key
    def through_source_key
        proxy_association.reflection.source_reflection.foreign_key
    end

    #Primary Key
    def through_primary_key
        proxy_association.reflection.through_reflection.active_record_primary_key
    end

    #Through Name
    def through_name
        proxy_association.reflection.through_reflection.name
    end

    #Through
    def through_collection
        proxy_association.owner.send through_name
    end

    #Captions
    def items
        through_collection.map(&:caption)
    end

    #Target
    def target_collection
        #load_target
        proxy_association.target
    end

end

Props to this gist for the variable functions

This basically overrides the load ActiveRecord function in the CollectionProxy class, and uses it to create our own proxy_association.target array :)

If you need any information on how to implement, just ask in the comments

Answer 2

You can make this little bit more prettier than raw sql by using rails AR querying methods:

AccountGroup.
  select("account_groups.id, SUM(net_position), account_groups.name").
  joins("LEFT JOIN accounts ON accounts.account_group_id = account_groups.id").
  joins("LEFT JOIN positions ON positions.account_id = accounts.id").
  group("account_groups.id,account_groups.name")

Answer 3

This can be done with pure Arel as well.

AccountGroup.select(
  AccountGroup.arel_table[:id], Arel::Nodes::NamedFunction.new('SUM', [:net_position]), AccountGroup.arel_table[:name]
).joins(
  AccountGroup.arel_table.join(Account.arel_table).on(
    Account.arel_table[:account_group_id].eq(AccountGroup.arel_table[:id])
  ).join_sources
).joins(
  AccountGroup.arel_table.join(Position.arel_table).on(
    Position.arel_table[:account_id].eq(Account.arel_table[:id])
  ).join_sources
).group(
  AccountGroup.arel_table[:id], AccountGroup.arel_table[:name]
)

I'm not 100% sure this will work, I simply copied your SQL from above and put it into scuttle.io

Answer 4

Use include function, in example

ac = AccountGroup.all(:include => :account)
$ AccountGroup Load (0.6ms)  SELECT `account_groups`.* FROM `groups` 
$ Account Load (16.4ms)  SELECT `accounts`.* FROM `accounts` WHERE `accounts`.`id` IN (1010, 3, 4, 202, 203, 204, 9999)

Then you can call ac.account.name or something like that

There are a great Railscast http://railscasts.com/episodes/22-eager-loading?view=asciicast

Answer 5

If you really want to use ActiveRecord for this (no SQL), it will be something like:

ags = AccountGroup.all(:include => {:accounts => :positions})
hash = Hash[ags.map { |ag| [ag, ag.map(&:accounts).flatten.map(&:positions).flatten.map(&:net_position).reduce(0,&:+)]}]

But it will be slower than your SQL, and not any prettier.

Answer 6

Is this something that Rails just can't do?

As this question has been open for about a month, I'm gonna to go ahead and assume the answer to this question is...

Yes.

EDIT: Yes, for Rails 3. But Rails 4 can do it! See accepted answer.

Rails can't do it, outside of using find_by_sql or ActiveRecord::Base.connection.execute(query), which are pretty kludgy and not rails-y.