How to make fragment truly dynamic?

I have a table in my app that displays a bunch of records. The table’s column’s are configurable by the user, as in they can show and hide different columns.

I would like to add a text search function. I know how to do this using Postgres full-text searching, by utilizing to_tsvector() inside a fragment, e.g.:

dynamic([project: p, user: u], fragment("to_tsvector('english', ? || ' ' || ? || ' ' || ? || ' ' || ?) @@ plainto_tsquery(?)",,, u.first_name, u.last_name, ^term))

However, I want to improve this by performing the search only on the table columns that the user currently has enabled. The reason I want to do this is to avoid confusing them by searching on columns that are not visible to them.

How can I make the above fragment dynamic in that manner?

Did you consider caching the tsvector in another table based on the settings and search that instead? You can then rebuild the cache when the user settings change.

Not a direct answer to your question, admittedly, but AFAIK the only way to conditionally build fragments would be to have one dynamic for every version, which would be pretty awkward in your case. Of course, I guess you’d still need that conditional logic in the cache builder, but that might be slightly less intrusive/preferable

Ecto does support slice(^list) with fragment, but it doesn’t seem to be able to deal with dynamic values as part of the provided list. Adding that would be awesome and would allow for your usecase.

Doesn’t that only render into a SQL list, eg in (?) with comma separators?

I does also do functions with variable parameter size, like concat(…). That does work with a list of parameters already, but doesn’t with dynamic values as part of the list.

1 Like