SQL Query for each row

[axsuul]

Is this bugged in that there seems to be a SQL query for each row in the table? This is from the console:

>> Fruit.all
  Banana Load (0.6ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana')
  Banana Load (0.2ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 1]]
  Banana Load (0.3ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 1
  Banana Load (0.2ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 2]]
  Banana Load (0.3ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 2
  Banana Load (0.8ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 3]]
  Banana Load (0.8ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 3
  Banana Load (0.2ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 4]]
  Banana Load (0.8ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 4
  Banana Load (0.2ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 5]]
  Banana Load (0.4ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 5
  Banana Load (0.3ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 6]]
  Banana Load (0.8ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 6
  Banana Load (0.5ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 7]]
  Banana Load (0.3ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 7
  Banana Load (0.1ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 8]]
  Banana Load (0.8ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 8
  Banana Load (0.3ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 9]]
  Banana Load (0.4ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 9
  Banana Load (0.1ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 10]]
  Banana Load (0.2ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 10
  Banana Load (1.7ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 11]]
  Banana Load (0.4ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 11
  Banana Load (0.2ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 12]]
  Banana Load (0.3ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 12
  Banana Load (0.1ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 13]]
  Banana Load (0.4ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 13
  Banana Load (0.1ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 14]]
  Banana Load (0.2ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 14
  Banana Load (0.1ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 15]]
  Banana Load (0.1ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 15
  Banana Load (0.1ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = ? LIMIT 1  [["id", 16]]
  Banana Load (0.1ms)  SELECT "view_bananas".* FROM "view_bananas" WHERE "view_bananas"."type" IN ('Banana') AND "view_bananas"."id" = 16

migrations/create_fruits.rb

class CreateFruits < ActiveRecord::Migration
  def change
    create_table :fruits do |t|
      t.integer :calories
      t.string :type
      t.timestamps
    end
  end
end

migrations/create_bananas.rb

class CreateBananas < ActiveRecord::Migration
  def up
    create_table :bananas do |t|
      t.string :color
    end

    create_citier_view(Banana)
  end

  def down
    drop_citier_view(Banana)
    drop_table :bananas
  end
end

models/fruit.rb

class Fruit < ActiveRecord::Base
  acts_as_citier
end

models/banana.rb

class Banana < Fruit
  acts_as_citier
end

Environment

Rails 3.1

[DouweM]

Looks like a bug to me. The two queries per record are what happens when a child gets reload called on it, you can test it in console. The find(:all) method used by all calls reload on each child to get the attributes that are in child, but not in root.

[DouweM]

With the fix in pull request #31, only one SELECT query is executed per row. There should still be a way to get all of them using only one query.

[DouweM]

And with another commit appended to pull request #31, only one query is used per type of needed record! That would leave your specific situation at only two queries: One for selecting the Fruits, and one for selecting all of the connected Bananas.

[axsuul]

Awesome! Thanks for your work, hope it gets merged soon =)

[DouweM]

So do I, but until then you can put this in your Gemfile so it gets the citier gem from my repo:

gem 'citier', :git => 'git://github.com/DouweM/citier.git'