Rails - Count loop with Active Record

Question

I have a rails app where I often need to generate xls files with several values. To do so I load customers orders in my controller (valid is just a custom scope)

@orders = Order.valid.order('id DESC')

In my view, I want to count meals for each orders with different scopes (i removed all xml tags, which are not interesting here):

<% @orders.each do |order| %>
  <%= order.meals.count %>
  <%= order.meals.meat.count %>
  <%= order.meals.fish.count %>
  <%= order.meals.drink.count %>
  <%= order.meals.dessert.count %>
<% end %>

My problem is that this generates a very large number of SQL requests.

I tried to preload meals like this:

@orders = Order.valid.order('id DESC').preload(:meals)

But count requests keep being generated

I also found an old gem: preload_count to do this, but it doesn't works with rails 4.

Is there a way to optimize my requests ?

EDIT

After many tries and help from Andrey Deineko, my request turned into this:

Order.includes(:meals).valid.order('id DESC').references(:meals)

Then i realized it may come from meal types which are in another table in my DB

Order.eager_load(meals: :type).valid.order('orders.id DESC').references(:meals)

But still, here are my db requests:

(0.3ms)  SELECT COUNT(*) FROM "meals" INNER JOIN "meals_orders" ON "meals"."id" = "meals_orders"."meal_id" WHERE "meals_orders"."order_id" = $1  [["order_id", 1044]]
   (0.3ms)  SELECT COUNT(*) FROM "meals" INNER JOIN "meals_orders" ON "meals"."id" = "meals_orders"."meal_id" WHERE "meals_orders"."order_id" = $1  [["order_id", 1044]]
   (0.4ms)  SELECT COUNT(*) FROM "meals" INNER JOIN "types" ON "types"."id" = "meals"."type_id" INNER JOIN "meals_orders" ON "meals"."id" = "meals_orders"."meal_id" WHERE "meals_orders"."order_id" = $1 AND "types"."name" = $2  [["order_id", 1044], ["name", "meat"]]
   (0.4ms)  SELECT COUNT(*) FROM "meals" INNER JOIN "types" ON "types"."id" = "meals"."type_id" INNER JOIN "meals_orders" ON "meals"."id" = "meals_orders"."meal_id" WHERE "meals_orders"."order_id" = $1 AND "types"."name" = $2  [["order_id", 1044], ["name", "meat"]]
   (0.4ms)  SELECT COUNT(*) FROM "meals" INNER JOIN "types" ON "types"."id" = "meals"."type_id" INNER JOIN "meals_orders" ON "meals"."id" = "meals_orders"."meal_id" WHERE "meals_orders"."order_id" = $1 AND "types"."name" = $2  [["order_id", 1044], ["name", "fish"]]
   (0.4ms)  SELECT COUNT(*) FROM "meals" INNER JOIN "types" ON "types"."id" = "meals"."type_id" INNER JOIN "meals_orders" ON "meals"."id" = "meals_orders"."meal_id" WHERE "meals_orders"."order_id" = $1 AND "types"."name" = $2  [["order_id", 1044], ["name", "fish"]]
   (0.3ms)  SELECT COUNT(*) FROM "meals" INNER JOIN "types" ON "types"."id" = "meals"."type_id" INNER JOIN "meals_orders" ON "meals"."id" = "meals_orders"."meal_id" WHERE "meals_orders"."order_id" = $1 AND "types"."name" = $2  [["order_id", 1044], ["name", "drink"]]
   (0.3ms)  SELECT COUNT(*) FROM "meals" INNER JOIN "types" ON "types"."id" = "meals"."type_id" INNER JOIN "meals_orders" ON "meals"."id" = "meals_orders"."meal_id" WHERE "meals_orders"."order_id" = $1 AND "types"."name" = $2  [["order_id", 1044], ["name", "drink"]]
   (0.3ms)  SELECT COUNT(*) FROM "meals" INNER JOIN "types" ON "types"."id" = "meals"."type_id" INNER JOIN "meals_orders" ON "meals"."id" = "meals_orders"."meal_id" WHERE "meals_orders"."order_id" = $1 AND "types"."name" = $2  [["order_id", 1044], ["name", "dessert"]]

When iterating through collections, be sure to use find_each, which is batched, instead of each to prevent loading a potentially massive result set into memory. — Anthony E
– Anthony E, Commented Apr 8, 2016 at 18:06
Thanks, I changed this in my code, but it doesn't answer my question :/ — Shrolox
– Shrolox, Commented Apr 11, 2016 at 9:31
@Shrolox did you find a solution to this problem? The only other thing i can think of is caching the count in a column, in your orders table. — BenKoshy
– BenKoshy, Commented Oct 30, 2020 at 7:51
I don't remember but since I didn't post any solution, I think I went for a workaround like the one you suggested — Shrolox
– Shrolox, Commented Oct 30, 2020 at 10:33

Andrey Deineko · Accepted Answer · 2016-04-08 16:28:46Z

1

go with

@orders = Order.includes(:meals).valid.order('id DESC')

if you want to have only orders with meals (no orders, where meal is nil) go with:

@orders = Order.joins(:meals).valid.order('id DESC')

Have a read about AR querying.

answered Apr 8, 2016 at 16:28

Andrey Deineko

52.5k11 gold badges119 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Shrolox Over a year ago

Tried this, but AR still generates a request to get the count. it's eager_loading counts that i want. Or maybe i misunderstood you

Andrey Deineko Over a year ago

Might by my misunderstanding. Could you try Order.includes(:meals).valid.order('id DESC').references(:meals)?

Shrolox Over a year ago

Thanks. I tried this, but count requests are still firing. As meal scopes refers to another database (Types) i tried to include these with a eager load. I'll update my post to show you.

margo · Accepted Answer · 2016-04-08 16:29:15Z

0

If your models have a has_many belongs_to relationship you can use includes to eager load the associations. This is a good article on eager loading.

answered Apr 8, 2016 at 16:29

margo

2,9371 gold badge17 silver badges31 bronze badges

1 Comment

Shrolox Over a year ago

Thanks. I tried eager_loading, but i can't succeed in preloading counts in only one request.

Collectives™ on Stack Overflow

Rails - Count loop with Active Record

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related