SQLアンチパターン覚え書き

ActiveRecord::Schema.define do
  create_table :users do |t|
    t.string :friends_ids
  end
end

user = User.create!
user.friends_ids = 8.times.collect { User.create!.id }.join(",")
user.friends_ids  # => "2,3,4,5,6,7,8,9"

ActiveRecord::Schema.define do
  create_table :users do |t|
    t.string :avatar1
    t.string :avatar2
  end
end

ActiveRecord::Schema.define do
  create_table :users do |t|
    t.string :name, index: true
    t.timestamps    index: true
    t.index [:name, :created_at, :updated_at]
    t.index [:created_at, :updated_at]
    t.index [:name, :created_at]
    t.index [:name, :updated_at]
  end
end

ActiveRecord::Base.connection.indexes(:users).count  # => 7

user = User.create!(file_path: "path/to/file.png")
File.exist?(user.file_path)  # => false

ActiveRecord::Schema.define do
  create_table :users do |t|
    t.string :password
  end
end

user = User.create!(password: "foo")
user.password  # => "foo"

ActiveRecord::Schema.define do
  create_table :users do |t|
    t.column :foo, "ENUM('a', 'b')"
  end
end

User.create!(foo: "a")  # => #<User id: 1, foo: "a">
User.create!(foo: "b")  # => #<User id: 2, foo: "b">

User.order("RAND()").take  # => #<User id: 1>

User.offset(rand(User.count)).take  # => #<User id: 1>

ActiveRecord::Schema.define do
  create_table :users do |t|
    t.float :a
    t.column :b, :double
    t.column :c, "DECIMAL(65, 30)"
  end
end

v = 5.5555555555555555555555555555555555555
user = User.create!(a: v, b: v, c: v).reload
user.a.to_d  # => 0.555556e1
user.b.to_d  # => 0.5555555555555555e1
user.c.to_d  # => 0.5555555555555555e1

id = "0 or name = 'admin'"
User.where("id = #{id}").to_sql  # => "SELECT `users`.* FROM `users` WHERE (id = 0 or name = 'admin')"
User.where("id = #{id}").take    # => #<User id: 1, name: "admin">

User.pluck(:id)                                       # => [1, 3]
sql = "SELECT id + 1 FROM users WHERE (id + 1) NOT IN (SELECT id FROM users) LIMIT 1"
id = ActiveRecord::Base.connection.select_value(sql)  # => 2
User.create!(id: id)                                  # => #<User id: 2>

Article.where(["content LIKE ?", "%キーワード%"])

class User < ActiveRecord::Base
end

ActiveRecord::Schema.define do
  create_table :users do |t|
  end
  create_table :profiles, id: false do |t|
    t.belongs_to :user
  end
end

class User < ActiveRecord::Base
  has_one :profile
end

class Profile < ActiveRecord::Base
  self.primary_key = :user_id
  belongs_to :user
end

user = User.create!                  # => #<User id: 1>
user.create_profile!(:user => user)  # => #<Profile user_id: 1>

ActiveRecord::Schema.define do
  create_table :users do |t|
  end
  create_table :comments do |t|
    t.belongs_to :commentable, polymorphic: true
  end
end

class User < ActiveRecord::Base
  has_many :comments, as: :commentable
end

# あらゆるレコードにコメントできるモデル
class Comment < ActiveRecord::Base
  has_many :comments, as: :commentable # 自分に対してもコメントできるようにするため
  belongs_to :commentable, polymorphic: true
end

user = User.create!              # => #<User id: 1>
comment = user.comments.create!  # => #<Comment id: 1, commentable_type: "User", commentable_id: 1>
comment.comments.create!         # => #<Comment id: 2, commentable_type: "Comment", commentable_id: 1>
comment = user.comments.create!  # => #<Comment id: 3, commentable_type: "User", commentable_id: 1>
comment.comments.create!         # => #<Comment id: 4, commentable_type: "Comment", commentable_id: 3>

ActiveRecord::Schema.define do
  create_table :users do |t|
  end
  create_table :articles do |t|
    t.belongs_to :user, foreign_key: true
  end
end

Article.create!(user_id: 1) rescue $!  # => #<ActiveRecord::InvalidForeignKey: Mysql2::Error: Cannot add or update a child row: a foreign key constraint fails (`__test__`.`articles`, CONSTRAINT `fk_rails_3d31dad1cc` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))>

ActiveRecord::Schema.define do
  (2000...2100).each do |year|
    create_table "users_#{year}" do |t|
    end
  end
end

User.table_name = :users_2016
User.create!  # => #<User id: 1>

User.table_name = :users_2017
User.create!  # => #<User id: 1>

CREATE TABLE users (
 id INTEGER AUTO_INCREMENT NOT NULL,
 created_at DATETIME,
 PRIMARY KEY (id, created_at)
);

ALTER TABLE users PARTITION BY HASH (YEAR(created_at)) PARTITIONS 3;
EXPLAIN PARTITIONS SELECT * FROM users;

User.create!(name: "a", score: 1)
User.create!(name: "a", score: 2)
User.create!(name: "b", score: 3)
User.create!(name: "b", score: 4)

sql "SELECT id, AVG(score) FROM users GROUP BY name"  # => [{"id"=>1, "AVG(score)"=>1.5}, {"id"=>3, "AVG(score)"=>3.5}]

sql "SELECT id, AVG(score) FROM users GROUP BY name"  # => #<ActiveRecord::StatementInvalid: Mysql2::Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column '__test__.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by>

sql <<~EOS  # => [{"user_id"=>1, "count_a"=>2, "count_b"=>2}]
SELECT f1.user_id,
       COUNT(a1.id) AS count_a,
       COUNT(a2.id) AS count_b
FROM favorites f1
LEFT JOIN articles a1 ON (f1.article_id = a1.id AND a1.name = 'a')
LEFT JOIN favorites f2 USING (user_id)
LEFT JOIN articles a2 ON (f2.article_id = a2.id AND a2.name = 'b')
WHERE f1.user_id = 1
GROUP BY f1.user_id
EOS

User.count rescue 0  # => 0

user = User.create!
"#{user.age}歳"  # => "歳"

user = User.create!(age: -1)
"#{user.age != -1 ? user.age : "?"}歳"  # => "?歳"

User.create!(age: 20)
User.average(:age).to_i  # => 9

User.where.not(age: -1).average(:age).to_i  # => 20

10.times { User.create!(bin: "x" * 1.megabyte) }

Benchmark.ms { User.all.to_a         }  # => 13.596000033430755
Benchmark.ms { User.select(:id).to_a }  # => 0.3570000408217311
Benchmark.ms { User.pluck(:id)       }  # => 0.2530000638216734