CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-5rails-activerecord
Fiodor
Fiodor

Reputation: 806

Rails ActiveRecord includes with condition for the second query

I have three models: Sale, Product and Client. A sale belongs to both a product and a client, each of which has many sales. Client also has many products through sales (basically products which have ever been sold to that client). The query for client.products is

SELECT "products".* FROM "products" INNER JOIN "sales" ON "products"."id" = "sales"."product_id" WHERE "sales"."client_id" = $1

So far, so good. Now I want to eager load sales for products which belong to a client (client.products.includes(:sales)), which correctly fetches all sales for each product for this client, like this:

SELECT "products".* FROM "products" INNER JOIN "sales" ON "products"."id" = "sales"."product_id" WHERE "sales"."client_id" = $1;
SELECT "sales".* FROM "sales" WHERE "sales"."product_id" IN (...)

The problem is I would like to only fetch sales which belong to that client. The query should look like this:

SELECT "products".* FROM "products" INNER JOIN "sales" ON "products"."id" = "sales"."product_id" WHERE "sales"."client_id" = $1;
SELECT "sales".* FROM "sales" WHERE "sales"."client_id" = $1 AND "sales"."product_id" IN (...)

I've tried doing

client.products.includes(:sales).where(sales: { client: client } })

and

client.products.includes(:sales).where(products: { sales: { client: client } })

but unfortunately it only alters the first query like this:

WHERE "sales"."client_id" = $1 AND "sales"."client_id" = $2

Both $1 and $2 are the same value.

Is what I'm trying to do even possible with includes? If so how do I do it?

Upvotes: 0

Views: 149

Answers (1)

PGill
PGill

Reputation: 3521

sales = client.sales.includes(:product)

This will return you all the sales and eager load products for a sale/client.

To get the products you can loop through the sales

sales.each do |sale| 
  sale.product
end

Edit

You can group sales by products

products_with_sales = sales.group_by(&:product)

this will return you an array of hashes 

key = product
value = [sales]

then you can perform you calculations

products_with_sales.each do |product, sales|
  # sales.map { ... }
  # sales.sum(&:method)
end

Upvotes: 1

Related Questions