CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4activerecord
Shrolox
Shrolox

Reputation: 663

Rails - Count loop with Active Record

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"]]

Upvotes: 0

Views: 312

Answers (2)

margo
margo

Reputation: 2927

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.

Upvotes: 0

Andrey Deineko
Andrey Deineko

Reputation: 52377

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.

Upvotes: 1

Related Questions