by

SQL クエリの組み立て方

WHERE句の組み立てには，Criteria ( C ) クラス と Criterion ( Q ) クラス という二つのクラスを組み合わせます．

これらの組み合わせにより，複雑な条件を含む SQL を発行することができます．

これらは，DbUtil のメソッドと合わせて使用します．

• select
• get
• update
• delete

ここでは，次のデータベース定義を用います．

<project>
	<database name="sample">
		  <description>サンプル</description>
		  <table name="profile">
		  	 <column name="id" type="serial" />
			 <column name="name" type="string" label="名前" />
			 <column name="age" type="integer" label="年齢" />
		  </table>
	</database>
</project>

一致 / =

SELECT * FROM profile WHERE (profile.id = 10)

<?php
//:
 
$c = new C(Q::eq(Profile::columnId(),10));
$db->select(new Profile(),$c);

不一致 / <>

SELECT * FROM profile WHERE (profile.id <> 10)

<?php
//:
 
$c = new C(Q::neq(Profile::columnId(),10));
$db->select(new Profile(),$c);

大なり / >

SELECT * FROM profile WHERE (profile.id > 10)

<?php
//:
 
$c = new C(Q::gt(Profile::columnId(),10));
$db->select(new Profile(),$c);

以上 / >=

SELECT * FROM profile WHERE (profile.id >= 10)

<?php
//:
 
$c = new C(Q::gte(Profile::columnId(),10));
$db->select(new Profile(),$c);

小なり / <

SELECT * FROM profile WHERE (profile.id < 10)

<?php
//:
 
$c = new C(Q::lt(Profile::columnId(),10));
$db->select(new Profile(),$c);

以下 / <=

SELECT * FROM profile WHERE (profile.id < 10)

<?php
//:
 
$c = new C(Q::lte(Profile::columnId(),10));
$db->select(new Profile(),$c);

一致リスト / in

SELECT * FROM profile WHERE (profile.name IN ('hoge','fuga'))

<?php
//:
 
$c = new C(Q::in(Profile::columnName(),array("hoge","fuga")));
$db->select(new Profile(),$c);

不一致リスト / not in

SELECT * FROM profile WHERE (profile.name NOT IN ('hoge','fuga'))

<?php
//:
 
$c = new C(Q::notIn(Profile::columnName(),array("hoge","fuga")));
$db->select(new Profile(),$c);

前方一致 / like , ilike

SELECT * FROM profile WHERE (profile.name LIKE 'hoge%')

<?php
//:
 
$c = new C(Q::like(Profile::columnName(),"hoge","w*"));
$db->select(new Profile(),$c);

大文字小文字を区別しない場合

SELECT * FROM profile WHERE (profile.name ilike 'hoge%')

<?php
//:
 
$c = new C(Q::ilike(Profile::columnName(),"hoge","w*"));
$db->select(new Profile(),$c);

前方一致 / startswith, istartswith

SELECT * FROM profile WHERE (profile.name LIKE 'hoge%')

<?php
//:
 
$c = new C(Q::startswith(Profile::columnName(),"hoge"));
$db->select(new Profile(),$c);

大文字小文字を区別しない場合

SELECT * FROM profile WHERE (profile.name ilike 'hoge%')

<?php
//:
 
$c = new C(Q::istartswith(Profile::columnName(),"hoge"));
$db->select(new Profile(),$c);

後方一致 / like, ilike

SELECT * FROM profile WHERE (profile.name LIKE '%hoge')

<?php
//:
 
$c = new C(Q::like(Profile::columnName(),"hoge","*w"));
$db->select(new Profile(),$c);

大文字小文字を区別しない場合

SELECT * FROM profile WHERE (profile.name ilike '%hoge')

<?php
//:
 
$c = new C(Q::ilike(Profile::columnName(),"hoge","*w"));
$db->select(new Profile(),$c);

後方一致 / endswith, iendswith

SELECT * FROM profile WHERE (profile.name LIKE '%hoge')

<?php
//:
 
$c = new C(Q::endswith(Profile::columnName(),"hoge"));
$db->select(new Profile(),$c);

大文字小文字を区別しない場合

SELECT * FROM profile WHERE (profile.name ilike '%hoge')

<?php
//:
 
$c = new C(Q::iendswith(Profile::columnName(),"hoge"));
$db->select(new Profile(),$c);

部分一致 / like, ilike

SELECT * FROM profile WHERE (profile.name LIKE '%hoge%')

<?php
//:
 
$c = new C(Q::like(Profile::columnName(),"hoge","*"));
$db->select(new Profile(),$c);

大文字小文字を区別しない場合

SELECT * FROM profile WHERE (profile.name ilike '%hoge%')

<?php
//:
 
$c = new C(Q::ilike(Profile::columnName(),"hoge","*"));
$db->select(new Profile(),$c);

部分一致 / contains, icontains

SELECT * FROM profile WHERE (profile.name LIKE '%hoge%')

<?php
//:
 
$c = new C(Q::contains(Profile::columnName(),"hoge"));
$db->select(new Profile(),$c);

大文字小文字を区別しない場合

SELECT * FROM profile WHERE (profile.name ilike '%hoge%')

<?php
//:
 
$c = new C(Q::icontains(Profile::columnName(),"hoge"));
$db->select(new Profile(),$c);

前方不一致 / not like , not ilike

SELECT * FROM profile WHERE (profile.name NOT LIKE 'hoge%')

<?php
//:
 
$c = new C(Q::notLike(Profile::columnName(),"hoge","w*"));
$db->select(new Profile(),$c);

大文字小文字を区別しない場合

SELECT * FROM profile WHERE (profile.name NOT ilike 'hoge%')

<?php
//:
 
$c = new C(Q::notiLike(Profile::columnName(),"hoge","w*"));
$db->select(new Profile(),$c);

前方不一致 / notStartswith, notiStartswith

SELECT * FROM profile WHERE (profile.name NOT LIKE 'hoge%')

<?php
//:
 
$c = new C(Q::notStartswith(Profile::columnName(),"hoge"));
$db->select(new Profile(),$c);

大文字小文字を区別しない場合

SELECT * FROM profile WHERE (profile.name NOT ilike 'hoge%')

<?php
//:
 
$c = new C(Q::notiStartswith(Profile::columnName(),"hoge"));
$db->select(new Profile(),$c);

後方不一致 / not like, not ilike

SELECT * FROM profile WHERE (profile.name NOT LIKE '%hoge')

<?php
//:
 
$c = new C(Q::notLike(Profile::columnName(),"hoge","*w"));
$db->select(new Profile(),$c);

大文字小文字を区別しない場合

SELECT * FROM profile WHERE (profile.name NOT ilike '%hoge')

<?php
//:
 
$c = new C(Q::notiLike(Profile::columnName(),"hoge","*w"));
$db->select(new Profile(),$c);

後方不一致 / notEndswith, notiEndswith

SELECT * FROM profile WHERE (profile.name NOT LIKE '%hoge')

<?php
//:
 
$c = new C(Q::notEndswith(Profile::columnName(),"hoge"));
$db->select(new Profile(),$c);

大文字小文字を区別しない場合

SELECT * FROM profile WHERE (profile.name NOT ilike '%hoge')

<?php
//:
 
$c = new C(Q::notiEndswith(Profile::columnName(),"hoge"));
$db->select(new Profile(),$c);

部分不一致 / not like, not ilike

SELECT * FROM profile WHERE profile.name NOT LIKE '%hoge%'

<?php
//:
 
$c = new C(Q::notLike(Profile::columnName(),"hoge","*"));
$db->select(new Profile(),$c);

大文字小文字を区別しない場合

SELECT * FROM profile WHERE profile.name NOT ilike '%hoge%'

<?php
//:
 
$c = new C(Q::notiLike(Profile::columnName(),"hoge","*"));
$db->select(new Profile(),$c);

部分不一致 / notContains, notiContains

SELECT * FROM profile WHERE profile.name NOT LIKE '%hoge%'

<?php
//:
 
$c = new C(Q::notContains(Profile::columnName(),"hoge"));
$db->select(new Profile(),$c);

大文字小文字を区別しない場合

SELECT * FROM profile WHERE profile.name NOT ilike '%hoge%'

<?php
//:
 
$c = new C(Q::notiContains(Profile::columnName(),"hoge"));
$db->select(new Profile(),$c);

並び替え / order by * , order by * desc

昇順

SELECT * FROM profile ORDER BY id

<?php
//:
 
$c = new C(Q::order(Profile::columnId()));
$db->select(new Profile(),$c);

降順

SELECT * FROM profile ORDER BY id DESC

<?php
//:
 
$c = new C(Q::orderDesc(Profile::columnId()));
$db->select(new Profile(),$c);

複合

SELECT * FROM profile ORDER BY name, email DESC

<?php
//:
 
$c = new C(Q::order(Profile::columnName()),Q::orderDesc(Profile::columnEmail()));
$db->select(new Profile(),$c);

参考

• データベースから値を取り出す(select)
• テーブルの操作対象行を絞りこむ(where句をつくる)
• Criteriaの設定にショートカットを使う
• OR検索を行う
• select結果を検索条件に使用する
• dbutilで複雑な条件式のSQLを組んでみる