CODEWITHSUNDEEP
ruby-on-railscountparent-childruby-on-rails-4.2
Alejandro Araujo
Alejandro Araujo

Reputation: 149

Display children's count on Parent show - Rails 4

I have 3 classes, Products, ProductDetails, ProductStatuses

The first have the general data.

The second the inventory/stock and details of the products.

The third determines If a ProductDetail (Product serial) is available or not to sell.

I want to display a products' count on stock in Products index (General View).

The models are the followings:

class Product < ActiveRecord::Base
   has_many :product_details
end

class ProductDetail < ActiveRecord::Base
   belongs_to :product, foreign_key: :product_id
   belongs_to :product_status, foreign_key: :status
end

class ProductStatus < ActiveRecord::Base
   has_many :product_details
end

The schema is:

create_table "product_details", force: :cascade do |t|
   t.integer  "product_id", limit: 4
   t.string   "serial",     limit: 255
   t.string   "comment",    limit: 255
   t.datetime "created_at",             null: false
   t.datetime "updated_at",             null: false
   t.integer  "status",     limit: 4
end

create_table "product_statuses", force: :cascade do |t|
   t.boolean  "available"
   t.string   "name",       limit: 255
   t.datetime "created_at",             null: false
   t.datetime "updated_at",             null: false
end

create_table "products", force: :cascade do |t|
   t.string   "name",       limit: 255
   t.datetime "created_at",             null: false
   t.datetime "updated_at",             null: false
   t.boolean  "published"
end

I'm a beginner using rails.

I don't have idea how to get a children count. It sounds like a common question but I could not find a answer for this.

Need help :(

Thanks

Upvotes: 0

Views: 410

Answers (2)

Alejandro Araujo
Alejandro Araujo

Reputation: 149

Solved with this:

Model: product.rb

def self.stock()
   select('products.*, SUM(case when product_statuses.available=true then 1 else 0 end) as product_count')
   .joins('LEFT JOIN `product_details` `product_details` ON `product_details`.`product_id` = `products`.`id`')
   .joins('LEFT JOIN `product_statuses` ON `product_statuses`.`id` = `product_details`.`status`')
   .group('products.id')
end

Controller: products_controller.rb

@products = Product.all.stock

Upvotes: 0

Sean Huber
Sean Huber

Reputation: 3985

You'll have to know a little SQL to get what you want in an efficient manner. Your ActiveRecord query might look something like this:

@products_with_counts = Product.all.select(
  'products.*, count(product_statuses.id) as product_count'
).joins(
  :product_details => :product_statuses
).where(
  :product_statuses => { :available => true }
).group('products.id')

And here's an example of how you can access the counts:

@products_with_counts.each do |product|
  puts "Product: #{product.name} \t Number Available: #{product.product_count}"
end

My syntax might be off on the joins method. Refer to the documentation:

http://guides.rubyonrails.org/active_record_querying.html#joining-tables

The key thing is that you have to write an SQL select clause that'll include a table count on product_statuses and pass it to the select method.

Also, note that this query will only return products that have one or more product_details rows where available is set to true. If you want to return all products, even those that will have a count of zero, you'll have to write a custom SQL joins clause that uses a LEFT JOIN instead of an INNER JOIN and pass it to the joins method. For more info on SQL joins:

http://www.techonthenet.com/sql/joins.php

Upvotes: 1

Related Questions