What is the best approach for building dynamic queries in .drift files (if there is one)?

[RedyAu]

Hello!
My query defined in a .drift file might look familiar, as I posted a question about it last week :P

query.drift

song_fulltext_search(:match_string AS TEXT, :match_arguments AS TEXT):
  SELECT
      bm25(songs_fts, 0.0, 0.0, 10.0, 0.5, 5.0, 5.0, 2.0, 0.0, 0.0) AS rank
      ,uuid
      ,source_bank_id
      ,pitch_field
      ,snippet(songs_fts, 2, '<?', '?>', '...', 30) AS match_title
      ,snippet(songs_fts, 3, '<?', '?>', '...', 40) AS match_lyrics
      ,snippet(songs_fts, 4, '<?', '?>', '...', 15) AS match_composer
      ,snippet(songs_fts, 5, '<?', '?>', '...', 15) AS match_poet
      ,snippet(songs_fts, 6, '<?', '?>', '...', 15) AS match_translator
    FROM songs_fts
    WHERE songs_fts MATCH :match_string
    AND :match_arguments
    ORDER BY rank;

I've added an AND clause to the WHERE, which I'd like to populate. Obviously, the way I did it above doesn't work, as the match_arguments get quoted. In the end, I'd like to achieve something like this:

SELECT * FROM songs_fts
  WHERE
    songs_fts MATCH 'hello'
  AND (
	content_map->>'content_tags' LIKE '%great%'
	OR
	content_map->>'content_tags' LIKE '%stuff%'
  );

The part of the query after the AND needs to be generated dynamically, as the user sets and removes different filters on different fields of the data. They should even be able to change the logic operand (AND/OR).

I can of course string-interpolate my way to hell and make a custom query after all with no safety.
And I know I could probably make something like this with the Dart API, but that would need to be a separate query (or run entirely in dart), as the Dart API can't be used for FTS.

So TLDR:

• Can I insert unescaped SQL in a .drift at runtime trough a variable?
• If not, can this be substituted somehow for building a query dynamically (amount and order of operands change, not just arguments)?
• If not, what soulution comes to mind for this usecase?

Thank you!

[simolus3]

This sounds like a perfect fit for Dart components in SQL. Drift re-interprets the $ variable syntax as a point where expressions generated by the query builder at runtime can be injected. So if you define a query like this:

custom_filter: SELECT * FROM songs_fts
  WHERE
    songs_fts MATCH 'hello'
  AND $filter;

Drift should generate a method like this:

Selectable<Song> customFilter({required Expression<bool> Function(SongsFts songsFts) filter})

Which you can then use like this (or use custom expressions for this part only if you need ->>, that operator is not available through the query API yet):

await customFilter(filter: (songsFts) {
  final contentTags = songsFts.contentMap.jsonExtract<String>(r'$.content_tag');
  return (contentTags.like('%great%')) | (contentTags.like('%stuff%'));
}).get();

[RedyAu]

Ahh, thank you so much! Perfect.
You didn't have to go all-out with that example, the link alone is so helpful. I just didn't notice the significance of this in the docs...

[RedyAu]

For any future traveller here - This is what I came up with in the end:
reformatus/lyric@7bf26c1#diff-86f16178446662f9397446354dc04b011ba38b7f5b72a445f1eb33649def50dfR127