

RDBで、あるテーブルを複数条件（AND/OR含む）でフィルタしたい、という際の設計のベスト・プラクティスを知りたいです。

具体的に例を上げると、

[code]

    テーブル名: sample_table
    スキーマ: 
     - id integer
     - name varchar
     - condition1 integer,
     - condition2 integer,
      ....,
     - conditionN integer
    
[/code]

といったカラムを持ったテーブルに対して、

[code]

    select * from sample_table where condition1 = 2 AND condition9 = 1;
    select * from sample_table where (condition1 = 1 AND condition3 = 1) OR condition4 = 3;
    
[/code]

といったSQLを、Nが増えても減っても、  
問題なく発行できるような設計を行いたいです。

手がかりすらわからず、もしよろしければどなたかご教授いただけないでしょうか。  
宜しくおねがい致します。


CC BY-SA 3.0
2015-02-24T02:51:00.700
6998
2015-07-07T08:17:01.603
2015-02-25T05:28:42.490
49
7465
question
mysql sql データベース設計
RDBで、あるテーブルを複数条件でフィルタしたい、という際の設計のベスト・プラクティスを知りたい


## 追記とお詫び

Railsの設計方法だと勘違いしていました。  
この回答は無視してください。

* * *

「手がかりすらわからず」ということなので、Railsのscopeももしかするとご存じないのかな？と思いました。

というわけで一度「scope」という機能を調べてみてください。

ネットを検索するとたくさんのサイトがヒットすると思います。

たとえば以下のサイトで紹介されているコード例なんかは質問の件に近いのではないでしょうか？

<http://ruby-rails.hatenadiary.com/entry/20140814/1407994568>

> スコープ同士をマージすることにより、where句のAND条件を実現することができます。
[code]

    # app/models/user.rb
    class User < ActiveRecord::Base
      scope :inactive, -> { where state: 'inactive' }
      scope :finished, -> { where state: 'finished' }
    end
    
    # 使用方法
    User.inactive.finished
    # => SELECT "users".* FROM "users" WHERE "users"."state" = 'inactive' AND "users"."state" = 'finished'
    
[/code]

あと、検索用フォームを作成する場合はRansackというgemを使うと便利です。  
こちらもネットにたくさん情報が載っています。  
参考までにこちらのリンクを紹介しておきます。

<http://qiita.com/LuckOfWise/items/e020e896e71d47d0c6a4>

「手がかり」はつかめましたでしょうか？  
以上、ご参考までに。


CC BY-SA 3.0
2015-02-24T03:34:28.290
7001
2015-02-24T04:26:11.220
2015-02-24T04:26:11.220
85
85
6998
answer


「設計の」というのが辛いですね。  
条件式がANDだったりORだったり()ついてたり複雑過ぎます。  
（別テーブルに条件出して…とか考えましたが面倒くさそうです）

残念ながら。私は、こんな情けない方法しか思いつきませんでした。  
設計のベストではなく。その設計を前提として、どうやって検索結果を得るかというアプローチになってしまって申し訳ないのですが。

[code]

    DELIMITER //
    CREATE PROCEDURE SAMPLE()
    BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT NULL FROM DUAL WHERE FALSE;
      SELECT * FROM sample_table WHERE <conditions>;
    END
    //
    DELIMITER ;
    CALL SAMPLE;
    DROP PROCEDURE IF EXISTS SAMPLE;
    
[/code]

こうすることでconditionに間違いがあれば  
(例：condition9が存在していないのにcondition9が指定されている)  
SQLEXCEPTIONをキャッチして、SELECT NULL FROM DUAL WHERE FALSEが実行されます。  
SELECT NULL FROM DUAL WHERE FALSEの実行結果は、必ずEmptySetです。

都度プロシージャを作って捨ててを繰り返すので。とっても非エコです。


CC BY-SA 3.0
2015-02-24T05:58:48.807
7010
2015-02-24T05:58:48.807
8335
6998
answer


**「可変長のカラム数を持つテーブルでSQLの条件指定にそのカラムを使いたい」** という質問として回答します。

私の回答はOracleの書き方ですが参考にどうぞ

最初に可変長のカラム数を持つテーブルのカラム名かcount数を取得する。

[code]

    select column_name from all_tab_columns where table_name = 'sample_table' and column_name like 'condition%';
    
[/code]

これから戻り値を元にプログラムでSQLを組み立てたり分岐すると宜しいかと

動的SQLに組み込んでも良いでしょう。  
<http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1297167927>  
<http://docs.oracle.com/cd/E16338_01/appdev.112/b56260/dynamic.htm#CACDDACH>

...  
例のようにconditionNの中身が1桁の整数固定ならテーブルの可変カラムは止めて  
1つのvarchar型カラムにconditionNの中身を順番に入れてしまって文字の位置指定の  
substr等で取り出し/判定をした方が良い気がしますね。

[code]

    select * from sample_table where (condition1 = 1 AND condition3 = 1) OR condition4 = 3;
    
[/code]

を↓のようにパターンマッチで書けますしregexp_likeで正規表現も使えます。

[code]

    select * from sample_table where condition like '1?1%' or condition like '???3%';
    
[/code]


CC BY-SA 3.0
2015-02-25T10:58:52.030
7078
2015-02-25T10:58:52.030
5479
6998
answer


2つのテーブルに分割するのが素直な設計ではないでしょうか。

[code]

    CREATE TABLE sample (
      id integer,
      name text
    );
    
    CREATE TABLE condition (
      sample_id integer,
      n integer,  -- conditionN
      v integer   -- Value
    );
    
[/code]

[code]

    -- condition1 = 2 AND condition9 = 1
    SELECT * FROM sample WHERE id in(
      SELECT sample_id FROM condition WHERE (n,v) = (1,2)
      INTERSECT 
      SELECT sample_id FROM condition WHERE (n,v) = (9,1)
    );
    
    -- (condition1 = 1 AND condition3 = 1) OR condition4 = 3
    SELECT * FROM sample WHERE id in(
      (SELECT sample_id FROM condition WHERE (n,v) = (1,1)
       INTERSECT 
       SELECT sample_id FROM condition WHERE (n,v) = (3,1))
      UNION
      SELECT sample_id FROM condition WHERE (n,v) = (4,3)
    );
    
[/code]

こうしておけば、DB設計は変えずに`N`をどんどん増やせると思います。

なお、上記SELECT文を <http://sqlfiddle.com> で確認したところ、PostgreSQL 9.3では動作しましたが、MySQL
5.6では動作しませんでした。`condition`が予約語なのと、`INTERSECT`機能が存在しないことが原因のようです。  
タグによるとMySQLを使われているようなので、SELECTの際はご注意ください。

* * *

検証に使ったデータ

[code]

    INSERT INTO sample VALUES
      (1, 'one')
     ,(2, 'two')
     ,(3, 'three')
     ,(4, 'four')
    ;
    INSERT INTO condition VALUES
      (1, 1,2)
     ,(1, 9,0)
     ,(2, 1,1)
     ,(2, 3,1)
     ,(2, 4,0)
     ,(3, 4,3)
     ,(4, 1,2)
     ,(4, 9,1)
    ;
    
[/code]


CC BY-SA 3.0
2015-06-07T05:58:31.157
11068
2015-06-07T05:58:31.157
9999
6998
answer