Rails 3.2 Organizing a Report Based off of Complex Relationships

Question

I am developing a program for a warehousing/shipping company with the following Data relationships. The skinny regarding the relationships below is that the warehouse receives raw materials(product) from various carriers(clients) and stores them until they are needed to be shipped to the manufacturing plant. When a shipment leaves the warehouse, the manufacturing facility must know which company each raw material originated from. Take a look at the following ERD.

EDIT: My relationships in text form.

shipment.rb

has_many :product_shipments, :dependent => :destroy
has_many :products, :through => :product_shipments

product_shipment.rb

belongs_to :product
belongs_to :shipment

product.rb

has_many :product_shipments
has_many :shipments, :through => :product_shipments, :dependent => :destroy
belongs_to :client

client.rb

  has_many :products, :dependent => :destroy

I'm having trouble generating the queries in a manner that's formatted the way the requirements demand. The shipment report takes a date and must iterate through each client and list the products shipped to the manufacturing facility on that given date. It needs to be generated dynamically and formatted like the following.

Shipment Date: 2013-01-01

Client: 12 Name: ACME Widget Co

Product Name | Product Code | Qty Shipped
_________________________________________ 
nuts & bolts |      gj-3423 |          25
steel sheet  |      4g-2394 |          10
dc Motor     |      cj-c213 |           4


Client: 14 Name: Blah Blah, Inc

Product Name | Product Code | Qty Shipped
_________________________________________ 
spacers      |      gj-3423 |          15
epoxy        |      4g-2394 |          10
dc Motor     |      24-gj19 |           6

Client: 15 Name: Sample Co, Inc

Product Name | Product Code | Qty Shipped
_________________________________________ 
hot roll 48  |      cg-3423 |          15
welding wir  |      4g-2394 |          10
dc Motor     |      c2-1241 |           6
.
.
.
.

The problem is generating Queries using ActiveRecord. It's easy to grab the shipments and products for a given date for ex below. It is not easy to grab the original client that the raw material originated from, then iterate through shipments to the manufacturing facility for that client dynamically.

UPDATE: I am now able to group clients and shipments like above. Now I need to exclude clients where they DON'T have a shipment on the date specified. The below, although somewhat correct, still gives the dreaded O(n) query. Here is what I have.

@clients = Client.includes(:products => :shipments)
@clients.each do |c|
  puts c.name
  c.products.each do |p|
    p.shipments.where("ship_date like '#{@ship_date.strftime("%Y-%m-%d")}%'").each do |s|
      s.product_shipments.joins(:product).each do |ps|
        puts s.bill_of_lading + " " + ps.product_id.to_s + " " + ps.product.product_name.to_s + " " + ps.product.product_code + " " +ps.qty_shipped.to_s + " " + s.ship_date.to_s
        end
    end
  end
end

My issue is how do I organize the query to start with clients and then list products shipped on '2012-06-30. The query gets wacko from that perspective. I am unsure how to generate a query with active record when the relationship is that far removed.

Answer 1

UPDATE: Ok looking at the results you expect in the report, values from ProductShipment (like the quantity attribute) need to be pulled out, so product_shipments must be included in the nested association we're eager loading, otherwise ProductShipments aren't instantiated, it only serves as a join table.

Therefore instead of Client.includes(:products => shipments)... you want :

@clients = Client.includes(:products => {:product_shipments => :shipment}).
                  where("shipments.ship='#{ship_date}'")

Now I don't fully understand your domain model, but when there's a lot of nested associations, I like to spot the ones which hold the most information in a one to one relationship, because they can be seen as center piece. In this case product and shipment can both be understood as extensions of the "master model" product_shipment.

Thus you can write (respectfully to Demeter's law) :

class ProductShipment < AR
  def report_details
    s = shipment; p = product
    "#{s.bill_of_lading} #{p.id} #{p.name} #{p.code} #{quantity} #{s.shipped_on}"
  end
end

Here comes the tricky part: as it is written :products => {:product_shipments => :shipment} Rails understands

product_shipments.shipment but not product_shipment.products

The later would actually trigger a db call... (which we're trying to avoid). Thankfully Rails has another trick in it's pocket :

class ProductShipment < ActiveRecord::Base
  belongs_to :product, inverse_of: :product_shipments
end

class Product < ActiveRecord::Base
  has_many :product_shipments, inverse_of: :product
end

Having insured the mirroring of associations you can now fetch product_shipments through products and get your report with no O(n) calls on the DB :

@client.map {|c| c.products.map {|p| p.product_shipments} }.flatten.each do |ps|
  puts ps.details
end

UPDATE END

---

You must eager load the associated models or you get in the infamous O(n) query.

Shipment.where(:date => someday).includes({:product => :client}).each do |shipmt|
  puts shipmt.date

  shipmt.product.group_by(&:client).each do |client, products|
    puts client

    products.each do |product|
      puts product.details
    end
  end
end

BTW rails does the join directly from shipment to product assuming you have a has_many :through or has_and_belongs_to_many association here, so no need to use the join table (aka product_shipment)

Answer 2

Here's how I did it. Maybe not the best way to do it but it's close. Thanks for @charlysisto for sending me in the right direction with easy loading the relationships. I still get a O(n) on the join query for the lookup table, but could be worse. Any refinements please comment so I can improve the answer.

@clients = Client.order("clients.id ASC").includes(:products => :shipments).where("shipments.ship_date like '#{ship_date}'")
@clients.each do |c|
  puts c.name
  c.products.each do |p|
    p.shipments.each do |s|
      s.product_shipments.joins(:product).each do |ps|
        puts shipment and products stuff
      end
    end
end