🔍
Flutter で Supabase の PGroonga 全文検索を試してみた
Supabase が PGroonga に対応したと聞いたので、Flutter で試してみました。
PGroonga とは
PostgreSQL で全文検索エンジン Groonga を使うための拡張機能（Extensions）です。
チュートリアルのページはこちらです。
今回はこのページを参考にして Supabase のテーブル（インデックス）およびストアドファンクションを実装します。
今回の題材となるアプリケーション
過去にこちらの記事で言及した地図アプリです。
現在位置から指定距離の範囲内にあるスポットを検索して距離が近い順に返すストアドファンクションに、引数とWHERE句の条件（CASE WHENによる）を追加し、検索キーワードを含むスポットを（PGroonga のインデックスで全文検索して）距離が近い順に返すことができるように改修します。
アプリケーションの GitHub リポジトリはこちらです。
PGroonga を有効にする
「Database」-「Extensions」 の 「Available extensions」 から 「PGROONGA」 を探して有効化します。
有効化すると 「PGROONGA」 が 「Enabled extensions」 に移動します。
対象テーブルに全文検索用インデックスを追加する
「SQL Editor」 で 「New query」 から SQL 文を実行（RUN）します。
全文検索用インデックス追加
ALTER TABLE spot_opendata ADD COLUMN ft_text text GENERATED ALWAYS AS (title || ',' || describe || ',' || prefecture || municipality) STORED;
CREATE INDEX pgroonga_content_index
ON spot_opendata
USING pgroonga (ft_text)
WITH (tokenizer='TokenMecab');
最初にALTER TABLEで全文検索対象の生成列を追加しています。
そしてその生成列に対してCREATE INDEXで PGroonga の全文検索インデックスを作成しています（トークナイザには MeCab を指定）。
ストアドファンクションを全文検索対応にする
同様に、「SQL Editor」 で 「New query」 から SQL 文を実行（RUN）します。
ストアドファンクション全文検索対応化
CREATE OR REPLACE
FUNCTION get_spots(point_latitude double precision, point_longitude double precision, dist_limit int, category_id_number int, keywords text)
RETURNS TABLE (
distance double precision,
category_name text,
title text,
describe text,
latitude double precision,
longitude double precision,
prefecture text,
municipality text
) AS $$
BEGIN
RETURN QUERY
SELECT ((ST_POINT(point_longitude, point_latitude)::geography <-> spot_opendata.location::geography) / 1000) AS distance,
category.category_name,
spot_opendata.title,
spot_opendata.describe,
ST_Y(spot_opendata.location),
ST_X(spot_opendata.location),
spot_opendata.prefecture,
spot_opendata.municipality
FROM spot_opendata
INNER JOIN category ON spot_opendata.category_id = category.id
WHERE
(CASE WHEN dist_limit = -1 AND keywords = '' THEN false ELSE true END)
AND
(CASE WHEN dist_limit = -1 THEN true
ELSE (ST_POINT(point_longitude, point_latitude)::geography <-> spot_opendata.location::geography) <= dist_limit END)
AND
(CASE WHEN category_id_number = -1 THEN true
ELSE category.id = category_id_number END)
AND
(CASE WHEN keywords = '' THEN true
ELSE ft_text &@~ keywords END)
ORDER BY distance;
END;
$$ LANGUAGE plpgsql;
ストアドファンクションget_spotsの引数にキーワード（keywords）を追加し、キーワードが空文字でない場合に WHERE ft_text &@~ keywords の条件で全文検索できるようにしました。
あわせて、検索対象とする距離の範囲（dist_limit）に-1が指定されている場合は、現在位置からの距離を限定せず検索するように変更しています。
こちらは SQL Editor でget_spotsを使った検索例です。
アプリケーションに全文検索を組み込む
前回記事で使ったコードをそれぞれ修正し（置き換え）ます。
pubspec.yaml（関連部分）
mapbox_gl: ^0.16.0
supabase_flutter: ^1.4.0
class_definition.dart（関連部分）
import 'package:mapbox_gl/mapbox_gl.dart';
import 'package:supabase_flutter/supabase_flutter.dart';
// 都道府県＋市区町村
class PrefMuni {
String prefecture;
String municipalities;
PrefMuni(this.prefecture, this.municipalities);
String getPrefMuni() {
return prefecture + municipalities;
}
}
// Supabase category の内容
class SpotCategory {
int id;
String name;
SpotCategory(this.id, this.name);
}
// Supabase get_spots の内容
class SpotData {
num distance;
String categoryName;
String title;
String describe;
LatLng latLng;
PrefMuni prefMuni;
SpotData(this.distance, this.categoryName, this.title, this.describe,
this.latLng, this.prefMuni);
}
// 近隣スポット一覧表示画面に渡す内容一式
class NearSpotList {
List<SpotData> spotList;
NearSpotList(this.spotList);
}
supabase_access.dart
import 'package:mapbox_gl/mapbox_gl.dart';
import 'package:supabase_flutter/supabase_flutter.dart';
import 'class_definition.dart';
// Supabase Client
SupabaseClient getSupabaseClient(String supabaseUrl, String supabaseKey) {
return SupabaseClient(supabaseUrl, supabaseKey);
}
Future<List<SpotCategory>> searchSpotCategory(SupabaseClient client) async {
final List<dynamic> items =
await client.from('category').select().order('id', ascending: true);
final List<SpotCategory> resultList = [];
for (dynamic item in items) {
final SpotCategory category =
SpotCategory(item['id'] as int, item['category_name'] as String);
resultList.add(category);
}
return resultList;
}
Future<List<SpotData>> searchNearSpot(SupabaseClient client, LatLng latLng,
int? distLimit, int? categoryId, String? keywords) async {
final List<dynamic> items =
await client.rpc('get_spots', params: {
'point_latitude': latLng.latitude,
'point_longitude': latLng.longitude,
'dist_limit': (distLimit ?? -1),
'category_id_number': (categoryId ?? -1),
'keywords': (keywords ?? '')
});
final List<SpotData> resultList = [];
for (dynamic item in items) {
final SpotData spotData = SpotData(
item['distance'] as num,
item['category_name'] as String,
item['title'] as String,
item['describe'] as String,
LatLng((item['latitude'] as num).toDouble(),
(item['longitude'] as num).toDouble()),
PrefMuni(item['prefecture'] as String, item['municipality'] as String));
resultList.add(spotData);
}
return resultList;
}
呼び出し側のうち全文検索を使うコードはこちらです。起点からの距離が近い順に結果が返ってきます。
supabase_access.dart呼び出し側
import 'package:mapbox_gl/mapbox_gl.dart';
import 'package:supabase_flutter/supabase_flutter.dart';
import 'class_definition.dart';
import 'supabase_access.dart';
SupabaseClient? _supabaseClient;
String _supabaseUrl = '【Supabase の URL】';
String _supabaseKey = '【Supabase の API Key】';
_supabaseClient = getSupabaseClient(_supabaseUrl, _supabaseKey);
final LatLng position = 【起点とする緯度経度】;
final List<SpotData> spotList =
await searchNearSpot(_client!, _latLng!, null, null, keywords);
以上です。
最初既存プロジェクトに「PGROONGA」がなかなか表示されず焦った以外はすんなり実装できました。
2023/1/28 追記：
続きの記事を書きました。
Discussion