前回「PostgreSQLの配列型JSONでLIKE検索をする」という記事を書きました。
今回はこれをDoctrineで検索させるかというお話です。
前回調べた内容で発行するクエリ
JSONB型のデータに対してLIKE検索したい場合はサブクエリを使えば解決できました。
1
2
3
4
5
6
7
|
SELECT id, tags
FROM tags
WHERE EXISTS (
SELECT 1
FROM jsonb_array_elements_text(tags) AS tag
WHERE tag LIKE '%静%'
)
|
DoctrinenoDQLではどうすればいいのか？
最初はDQL User Defined Functionsを使って jsonb_array_elements_text を使えるようにすればいいかなと考えました。
自分で作ろうかと思いましたがopsway/doctrine-dbal-postgresql というライブラリが既にあったので使うことにしました。
opsway/doctrine-dbal-postgresqlでjsonb_array_elements_textを使えるようにする
composer requireする以外にも少し作業が必要でした。
composer requireする
まずはcomposer requireします。
1
|
$ composer req opsway/doctrine-dbal-postgresql
|
config/doctrine.yamlを変更する
composer requireするだけではだめで、doctrineの設定を変更する必要があります。
1
2
3
4
5
6
|
// config/doctrine.yaml
doctrine:
orm:
dql:
string_functions:
JSONB_ARRAY_ELEM_TEXT: OpsWay\Doctrine\ORM\Query\AST\Functions\JsonbArrayElementsText
|
これで jsonb_array_elements_text がDoctrineで使えるようになりました。
ただし、JSONB_ARRAY_ELEM_TEXT という名前の関数になっているので間違えないように注意してください。
実際にQueryBuilderで使ってみる
実際にDQLを生成するためのクエリビルダを書いてみましょう。
1
2
3
4
5
6
7
8
9
10
11
12
|
<?php
$tag = '静';
$subQuery = $this->em->createQueryBuilder();
$subQuery->select(1)->from('JSONB_ARRAY_ELEM_TEXT(tags)', 'tag')->andWhere('tag LIKE %:tag%')
->setParameter('tag', $tag)
;
$qb = $this->em->createQueryBuilder();
$qb->select('t')
->from(Tag::class, 't')
->andWhere($qb->expr()->exists($subQuery));
|
これを実行するとエラーが発生します。
1
|
get_class(): Argument #1 ($object) must be of type object, int given
|
これななぜ起きるのかというとFROM句はクラスを指定する想定になっているからエラーが発生します。
つまり、FROM句はEntityしか指定できないということですね。
ChatGPTに聞くと以下のように言ってました。
DQLの主な目的は、エンティティとその関連をクエリすることです。そのため、FROM句では、通常エンティティ（つまり、データベースのテーブルにマップされたクラス）の名前が必要です。そして、SELECT句では、エンティティのプロパティ（つまり、テーブルの列にマップされたクラスのフィールド）を指定します。
したがって、DQLでFROM句に関数を使用することはできません。
つまりDQLだとサブクエリを使ったLIKE検索は無理だということになります。
CASTを使う
PostgreSQLにはCASTをする機能があります。
JSON型からTEXT型に変換することも可能になります。つまりこういうSQLでも同じ結果が得られることです。
1
|
SELECT * FROM tags WHERE CAST(tags as TEXT) LIKE '%静%'
|
CAST関数を作る
もちろんDQLの場合はCAST関数を作ることはできません。
ということで関数を作ります。
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
|
namespace App\Doctrine\x\ORM\Query\AST\Functions;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\AST\Node;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;
class JSONText extends FunctionNode
{
private $expr1;
public function getSql(SqlWalker $sqlWalker)
{
return sprintf(
"CAST(%s AS TEXT)",
$this->expr1->dispatch($sqlWalker)
);
}
public function parse(Parser $parser)
{
$parser->match(Lexer::T_IDENTIFIER);
$parser->match(Lexer::T_OPEN_PARENTHESIS);
$this->expr1 = $parser->StringPrimary();
$parser->match(Lexer::T_CLOSE_PARENTHESIS);
}
}
|
さらにconfig/doctrine.yamlにも追記が必要です。
1
2
3
4
5
6
|
// config/doctrine.yaml
doctrine:
orm:
dql:
string_functions:
JSON_TEXT: App\Doctrine\x\ORM\Query\AST\Functions\JSONText
|
QueryBuilderを修正する
クエリビルダを修正します。
1
2
3
4
5
6
7
8
|
$tag = '静';
$qb = $this->em->createQueryBuilder();
$qb->select('t')
->from(Tag::class, 't')
->andWhere('json_to_text(t.tags) LIKE :tag')
->setParameter('tag', $tag)
;
|
これで無事検索できるようになりました。
まとめ
- DQLではFROM句はEntityのみ指定できる
- サブクエリを使った検索方法はDQLでは無理
- CASTを使ってLIKE検索をする
参考
Symfony find user by role (JSON array Doctrine property)
amazon.co.jp