CODEWITHSUNDEEP
ruby-on-railsactiverecord
Charles
Charles

Reputation: 379

Including nested association attributes in a count

I have classes that look like this:

class Orders < ActiveRecord::Base
  has_many :items
  has_many :types, through: :items
  accepts_nested_attributes_for :items
end

class Item < ActiveRecord::Base
  belongs_to :type
  belongs_to :order
end

class Type < ActiveRecord::Base
  has_many :items
end

An Order has several items on it, here's an overview and item and type:

 <Item:0x00007fbcb185c170
   id: 1,
   finish: "Fir",
   size: "38",
   type_id: 8,
   order_id: 1>

 <Type:0x00007fbcace363e8
   id: 8,
   type: "Door",
   name: "Jeldwen Premium Interior">

Items might have the same type but different finish and size attributes. I need to grab an order from the database and present them in a human readable format, something like this:

Order: {
  Types: 
  {
    Name: "Jeldwen Premium Interior",
    Type: "Door",
    Items: 
    {
        finish: "fir",
        size: "36", 
        quanity: "8" # sum of items in the database with same `finish` and `size`
    }, 
    {
        finish: "fir",
        size: "58", 
        quanity: "8" # sum of items in the database with same `finish` and `size`   
    }
}

I would like to be able to get all this information with one db call and I got part of the way there, I'm just missing type name-

`Order.where(id: 1).joins(:items).joins(:type).group("items.type_id", "items.size", "items.color").count`

Returns the type_id, size, finish, and quanity: 

{[3, "36", "fir"]=>40,
 [3, "48", "fir"]=>36,
 [3, "36", "oak"]=>90,
 [4, "48", "oak"]=>18}

I would like to return type.name and type.name with the count, is that possible? So a row would look something like:

 ["Jeldwen Premium Interior", "door", 3, "36", "fir"]=>40

Upvotes: 0

Views: 674

Answers (1)

fabOnReact
fabOnReact

Reputation: 5942

I had this issue yesterday and I ended up using find_by_sql

you can test the sql in your sql console or at the following site

Your rails query 

.joins(:items).joins(:type).group("items.type_id", "items.size", "items.color").count

can be done with sql

SELECT items.type_id, items.size, items.color, types.name, COUNT(types.id) AS NumberOfTypes, COUNT(types.name) AS NumberOfNames FROM items INNER JOIN types on items.id = types.item_id GROUP BY items.type_id, items.size, items.color;

in a scope

class Item
    scope :items_count, -> { find_by_sql("SELECT items.type_id, items.size, items.color, types.name, COUNT(types.id) AS NumberOfTypes, COUNT(types.name) AS NumberOfNames FROM items INNER JOIN types on items.id = types.item_id GROUP BY items.type_id, items.size, items.color;") }
end

and you can call 

items = Order.first.items_count

it will return an array of items, without count and without types. Something like:

=> [#<Item:0x00564d6bf08c28 id: nil, type: "type1test", size: 133, color: 'blue'>,
#<Item:0x00564d6bef7108 id: nil, type: "type1test", size: 136, color: 'blue'>,
#<Item:0x00564d6bef6e88 id: nil, type: "type1test", size: 137, color: 'blue'>,
#<Item:0x00564d6bef6cf8 id: nil, type: "type1test", size: 132, color: 'blue'>,
#<Item:0x00564d6bef6af0 id: nil, type: "type1test", size: 141, color: 'blue'>,
#<Item:0x00564d6bef68c0 id: nil, type: "type1test", size: 135, color: 'blue'>]

the items array will not have the types, but it will include a column numberofitems with the result of COUNT

items[0].numberoftypes 
=> 6
items[0].numberofnames 
=> 4

Anyway this will not work, because you can not GROUP BY different fields in different tables and have 2 COUNT at the same time.

You can try with sql to test it in this console and see that it is not possible.

It will not return the correct number of count for one of your columns, but this technique it is better then the one you are using right now, because you do not need to do 2 JOINS and it will return the objects.

SQL is very easy and you can refer to https://www.w3schools.com/sql/ to better understand any of the sql included in http://guides.rubyonrails.org/active_record_querying.html

Upvotes: 1

Related Questions