FuelPHPでormのhas_many機能を用いたAND条件の検索を行いたいです。

###発生している問題・エラーメッセージ
ormモデルでhas_manyの機能を使用し検索ページを作っています。
or_whereを用いた複数条件の OR検索は実装出来たのですが
複数条件全てを満たす AND検索がうまく実装出来ません。

...わかりづらい質問になってしまい申し訳ありませんが、宜しくお願いいたします。

###該当のソースコード
下記のような構造のテーブルがあります

class Model_Hoge extends \Model_Orm
{
  protected static $_properties = array(
    "id",
    "contents"
  );

  protected static $_has_many = array(
    'fuga_tables' => array(
      'key_from' => 'id',
      'model_to' => 'Model_Fuga',
      'key_to' => 'hoge_id',
      'cascade_save' => false,
      'cascade_delete' => false,
    )
  );
}

class Model_Fuga extends \Model_Orm
{
  protected static $_properties = array(
    "fuga_id",
    "hoge_id",
    "type"
  );
  protected static $_primary_key = array('fuga_id','hoge_id');

}

###サンプルデータ

Model_Hoge

id	Contents
1	AAA
2	BBB
3	CCC

Model_Fuga

fuga_id	hoge_id	type
1	1	search_type_A
2	1	search_type_B
3	2	search_type_A
4	3	search_type_B

###試したこと

// OR検索
//関連するFugaモデルのtypeがsearch_type_Aまたはsearch_type_BのHogeモデルのデータを取得する
// [成功] Model_Hogeの id => 1,2,3に該当するデータが取得できる
Model_Hoge::query()
->related("fuga")
->where("type","search_type_A")
->or_where("type","search_type_B")
->get();

// AND検索
//関連するFugaモデルのtypeがsearch_type_Aとsearch_type_Bの両方のデータを持っているHogeモデルのデータを取得する
// [失敗] Model_Hogeの id => 1に該当するデータを取得したかったが、取得件数は0件だった
Model_Hoge::query()
->related("fuga")
->where("type","search_type_A")
->where("type","search_type_B")
->get();

AND検索の失敗例のコードは検索結果が0件なのは当然なのですが、他にいい方法がわかりませんでした。

###補足情報
PHP 5.6.19
DATABASE psql (9.3.11)

↑MYSQLでも構いません

行動規範の内容に同意します

回答1件

ベストアンサー

実装したことのない機能だったので学習がてら挑戦してみました。
手をつける前に想像してたよりは一手間必要な感じでした。
構成はFuelPHP1.8 + MySQL5.6.21です。

##re:試したこと

先に、試したことへの回答を。実際に発行されたクエリを確認するには
CodeProfilerが便利ですが、最後に発行されたクエリだけなら
DB::last_query()でも取得できます。まずは失敗するというand検索のほうから。

php
1->where("type","search_type_A")
2->where("type","search_type_B")

このあたりのメソッドで生成されるクエリが以下の内容です。

WHERE `type` = 'search_type_A' AND `type` = 'search_type_B'

**「typeがAかつB」**のものとなっています。条件の照合対象はあくまでテーブル上の1レコードなので
この条件では絶対にヒットしないということでした。続いて一見うまくいっているor検索の
ほうですがこちらも少し問題があるようでした。

WHERE `type` = 'search_type_A' OR `type` = 'search_type_B'

抽出されるfugaレコードが**「typeがAもしくはBのどちらか」**に限定されてしまうため、
もし実際はA、B、C、Dを持つhogeがあったとしても、A、Bだけがリレーションされた状態で
取得されてしまいます。（表示や・計算にfugaを使わないならセーフかも）

##目標とするクエリ

一旦fugaテーブルから条件に適合するhoge_idを集計して、そのhoge_idを元に
hogeテーブルを検索するとよさげです。目標とするクエリはこんな感じ。

SELECT * FROM `hoge`  LEFT JOIN `fuga` ON (`hoge`.`id` = `fuga`.`hoge_id`)
WHERE `hoge`.`id` IN (
    SELECT `hoge_id` FROM `fuga`
    WHERE `type` IN ('search_type_A', 'search_type_B')
    GROUP BY `hoge_id` HAVING count(`hoge_id`) = 2
)

サブクエリ部分の説明
0. 検索typeと一致するfugaレコードのhoge_idをすべて抽出
0. hoge_idでグループ分け
0. count(hoge_id) = 検索type数なら一致とみなす

##FuelPHPで組み立てる

サブクエリ部分はDBクラスのクエリビルダで組み立てて、
メインはORMモデルに任せるのが楽そうです。

php
1$input = ['search_type_A', 'search_type_B'];
2$count = count($input);
3
4//compileメソッドでサブクエリを文字列として取得
5//or検索ならhaving句はいらない
6$sub_query = DB::select('hoge_id')
7    ->from('fugas')
8    ->where('type', 'in', $input)
9    ->group_by('hoge_id')
10    ->having(DB::expr('count(hoge_id) = ' . $count))
11    ->compile();
12
13//DB::exprメソッドでサブクエリをエスケープ無しで生のまま埋め込む
14$data['hoges'] = Model_Hoge::query()
15    ->related('fugas')
16    ->where('id', 'in', DB::expr("({$sub_query})"))
17    ->get();