Is there a generic solution for custom fields with search?

Questions / Help
1

Hi there!

We need to add custom fields for a couple of entities in our project. We’d like to give our clients the option to choose from three types (string, integer, date) and later offer to query the entities by those fields.

E.g. add custom field "height" of type "integer" to user "John" and set it to 172 and later give me all users with a height bigger than 170cm. We’d also like strings with ilike query and dates with from to.

That seems like a fairly common problem so I was wondering if there is a library that does that.

2

map/jsonb field doesn’t solve your problem? if we’re talking about postgres it might be enough.
you can query with where: fragment("? ->> ?", t.field, ^json_field) > ^value,

3

reading your question again i think i misunderstood it. i did something like that but it’s very custom to the contract we defined for query strings in our APIs. most of the time it gonna be a custom solution because it’s strongly dependent of how you define the contract layer to query stuff from the outside world. it might be easier if you have already defined a contract with something like jsonapi or graphql.

edit: just adding a resource that might be helpful to build such a custom tool that is the field/2 function from ecto query api.

what i did was map a set of keywords that we defined for our api to an intermediary vocabulary between those keywords and the database. it included filtering, ordering and pagination stuff. the implementation details was very custom to our vocabulary but in the end it looked something like that for a query to get data from the database:

    repo_fun = if opts[:paginate], do: &Repo.paginate/2, else: &Repo.all/1
    Post
    |> SearchParams.new_for()
    |> SearchParams.operation(:select)
    |> SearchParams.prepare(params)
    |> SearchParams.apply(Post, repo_fun)

I’ve choose to set the operation explicitly because update/delete queries shouldn’t have ordering statements.

1 Like