2014.10.30 MySQL

正規化(縦持ち、行持ち)データに対する検索の実装(PHP編)

前回に引き続き、正規化(縦持ち、行持ち)データに対する検索の実装をおこないます。

今回はPHPで、実装をおこないます。

前回の結果として以下のようなSQL文を発行すればよいことがわかりました。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT opt_1.* FROM test_option_2 opt_1
LEFT JOIN test_option_2 opt_2
ON opt_1.name_id = opt_2.name_id
WHERE
(
    opt_1.option_id = 1
    AND
    opt_1.value = "good"
)
AND
(
    opt_2.option_id = 2
    AND
    opt_2.value = "bad"
)

ここで、本機能をPHPおよびPDOを用いて実装しましょう。PDOをの使い方は、各々調べてください。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
<?php
 
class SearchSample{
   
  private $dbname     = "dbname";
  private $user       = "user";
  private $pass       = "pass";
   
  private $pdo        = null;
 
  public function __construct()
    {
        try {
                $this->pdo = new PDO("mysql:dbname={$this->dbname};host=172.16.21.223", $this->user, $this->pass);
        } catch (PDOException $e) {
                exit('データベースに接続できませんでした。' . $e->getMessage());
        }
    }
 
    //JOINのクエリ追加する
    public function addJoin(&$join_query , $num){
        $join_query .= " LEFT JOIN test_option_2 opt_${num} ON name.id = opt_${num}.name_id ";
    }
    //WHEREクエリを追加する
    public function addWhere(&$where_query , $option_id , $option_value , $num){
        $where_query .= " (opt_${num}.option_id = ${option_id} AND opt_${num}.value = \"${option_value}\") ";
    }
 
        //検索クエリを発行する
    public function search($option_id_arr , $option_value_arr){
        $pdo    = $this->pdo;
        $select = "SELECT name.name FROM test_name name ";
        $join   = "";
        $where  = "";
        $query  = "";
        if(count($option_id_arr) == count($option_value_arr)){
            foreach($option_id_arr as $key => $option_id){
                $option_value = $option_value_arr[$key];
                $this->addJOIN($join , $key);
                if($key == 0){
                    $where .= " WHERE ";
                }
                $this->addWhere($where , $option_id , $option_value , $key);
                if($key != count($option_value_arr) - 1){
                    $where .= " AND ";
                }
            }
        }
        $query = $select . $join . $where;
        $stmt  = $pdo->query($query);
        if (!$stmt) {
                $info = $pdo->errorInfo();
        }
        print($query);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}
$Search_obj = new SearchSample();
 
print_r($Search_obj->search(Array(1 , 2) , Array("good" , "bad")));
 
?>

以上のようなPHPを実行すると


(
[0] => Array
(
[name] => taro
)

)

のような結果が返ってきます。これならば、いくつ検索条件があっても、問題ありませんし、JOINの数を検索条件数に限定しているため、一旦横持ちテーブルに変換する処理に比べると格段にはやくなっているはずです。

これに、And検索、OR検索、LIKE検索などの機能を追加すれば十分実用に耐えるものができると思います。本当は、さらに抽象化すべきですが

当社では、社内システムにおいて、検索機能をSymfony2のDBALベースで実装していますが、今回は汎用性を鑑みてPDOベースで実装してみました。

投稿者プロフィール

開発 アルバイト
中の人には主に、
PHP・Symfony2系の人と
Ruby・Rails系の人がいます。
ときどきJavascript・データベースにも手を出すかもしれません。

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

CAPTCHA


Time limit is exhausted. Please reload CAPTCHA.