« ActionView::Helpers::FormHelperをlocal varibaleで使いたい | Main | PKG_SUGGESTED_OPTIONSなオプションを抜く »

2006年4月15日

参照されていないレコードを探す

[ カテゴリ： Technology ]

今回はSQLの話。 一対一の関係づけを考えます。 一般的には一対多の場合と同じようにどちらかに外部参照キーをつけることになると思います。 このとき、迷子というか相方のいないレコードを探すにはどうしたらいいでしょうか。

例えば、次のようなふたつのtableがあるとします。 

CREATE TABLE computers (
  id            SERIAL NOT NULL,
  name          VARCHAR(8),

  PRIMARY KEY (id)
);

CREATE TABLE keyboards (
  id            SERIAL NOT NULL,
  computer_id   INTEGER,
  keymap        VARCHAR (8),

  PRIMARY KEY (id),
  FOREIGN KEY computer_id
    REFERENCES computers (id) ON DELETE SET NULL
);

テーブルの内容には特に深い意味はありません。 で、KVMとか使ってないとしてコンピュータとキーボードは一対一の関係だとします。 Railsを使ってるとmodelは以下のようになるでしょう。 

class Computer < ActiveRecord::Base
  has_one :keyboard
end

class Keyboard < ActiveRecord::Base
  belongs_to :computer
end

さて、迷子のキーボードを探すのは簡単です。 

SELECT * FROM keyboards WHERE computer_id IS NULL;

問題は迷子のコンピュータを探す場合です。 一般的にどうするかはよくわかりませんが、例えば次のようにすれば実現できます。 

SELECT computers.*
  FROM computers LEFT OUTER JOIN keyboards ON computers.id=keyboards.computer_id
  WHERE keyboards.computer_id IS NULL;

LEFT OUTER JOINして、さらに相方がいないやつを探しています。 Railsのmodelでは、以下のようにしておくといいでしょう。 

class Computer < ActiveRecord::Base
  has_one :keyboard

  class << self
    def orphans(order = nil)
      options = {
        :select => 'computers.*',
        :joins => 'LEFT OUTER JOIN keyboards ON computers.id=keyboards.computer_id',
        :conditions => 'keyboards.computer_id IS NULL',
      }
      options[:order] = order if order
      find(:all, options)

    end
  end
end

class Keyboard < ActiveRecord::Base
  belongs_to :computer

  class << self
    def orphans(order = nil)
      options = {
        :conditions => 'computer_id IS NULL',
      }
      options[:order] = order if order
      find(:all, options)
    end
  end
end
Posted at 01:33

Comments

Post a comment




Remember Me?