Create dynamic bindings for where clause

Phoenix Forum Questions / Help
,
1

Hello,

the where macro (among others) gets an array of query positions as an argument ie query |> where([c0, c1, c2], c2.name = 32). How could I create the array of named bindings [c0, c1, c2] and the actual binding (c2) dynamically ? For example I lets say I have something like {name: 2} and I’d like to get both [c0, c1, c2] and c2.name=32 (or field(c2, ^“name”) in order to create a dynamic filter. Is this even possible? Do I need to use macros? Can somebody point me to the correct direction.

Thank you

2

i don’t know if you found this thread yet Dynamic Queries in Ecto
If not dynamic ecto queries may be your answer.

3

Hello @wolfiton yes I’m aware of dynamic an I’ve used it in other situations, however this is not what I want for this. I’d just like some pointers on how I could implement my requirement.

One other way to describe what I want are two functions (or macros) like

def get_dynamic_bindings(number)

and

def get_binding_name(number)

which I could call in the bindings in the where function i.e something like

query |> where(get_dynamic_bindings(3), field(get_binding_name(3), ^name)=32)

and this would be transformed to

query |> where([c0 ,c1, c2], field(c2, ^name)=32)

Is this maybe better explained ?

4

Hello,

Yes it is more clear now.

5

While you’ve explained how you want to do it - it’s not clear why you would want to do it in this particular fashion.

I’m just mentioning this because in many use cases it makes sense to switch away from positional bindings and use named bindings instead - that is, any binding that may need to be referenced later in a composition is named so that it can be referenced by name rather than position later.

6 Likes
6

@peerreynders yes I guess I fell into the trap of asking of a way to implement the solution I had in my mind than describing the actual problem :slight_smile:

I more or less want to create a dynamic query filtering mechanism. For example, I’ve got the following query:

    whs_query =
      from(w in Withholding,
        join: b in Beneficiary,
        on: [id: w.beneficiary_id],
        join: a in Authority,
        on: [id: w.authority_id],
        join: ak in AuthorityKind,
        on: [id: a.authority_kind_id],
        preload: [beneficiary: b, authority: {a, authority_kind: ak}],
        order_by: [asc: w.pay_date, asc: w.id]
      )

and I want to filter on various fields from all relations (w, b, a, ak).

Now in non-dynamic case, I’ll create a schemaless changeset using something like

    types = %{
      status: :string,
      afm: :string,
      # year: :integer,
      authority: :string,
      authority_kind: :string
    }

     {%{}, types}
     |> Ecto.Changeset.cast(params, Map.keys(types))

and finally retrieve the changes from the changeset and filter on the initial query:

changes = Map.fetch!(changeset, :changes) |> IO.inspect()

    whs_query =
      case changes[:status] do
        nil -> whs_query
        v -> whs_query |> where([w], w.status == ^v)
      end

  whs_query =
      case changes[:afm] do
        nil -> whs_query
        v -> whs_query |> where([w, b], b.afm == ^v)
      end


# etc

This works fine however it is a lot of work to do all this again and again for all my filters.

So instead I thought of modeling the filters I’d need for each query using a simple structure like:

filters= [
      %{name: :status, type: :string, pos: 0},
      %{name: :afm, type: :string, pos: 1}
    ]

– please notice that the pos defines the position in the named query i.e status is for Withholding (pos 0) while afm is for Beneficiary (pos 1) –

and then do something like:

    changeset = QueryFilterEx.make_filter_changeset(filters, filter_params)
    
    whs_query =
      from(w in whs_query,
        join: b in Beneficiary,
        on: [id: w.beneficiary_id],
        join: a in Authority,
        on: [id: w.authority_id],
        join: ak in AuthorityKind,
        on: [id: a.authority_kind_id],
        preload: [beneficiary: b, authority: {a, authority_kind: ak}],
        order_by: [asc: w.pay_date, asc: w.id]
      ) |>  QueryFilterEx.filter(filters, changeset)

So now the problem is that I have the initial query which is passed to QueryFilterEx.filter and I want to dynamically filter it using the filters struct I defined before to add where clauses to it. This leads me to the problem I described in my 1st and 2nd post :frowning:

Also, concerning named bindings i really don’t think that it would solve my problem because I still wouldn’t be able to dynamically construct the aliases of the bindings in the where clauses.

BR,
Serafeim

1 Like
7

After some clarifications on the #elixir-lang IRC channel from @josevalim it seems that @peerreynders suggestion was correct. Using named bindings can solve this problem!

So, if you have a query with proper named bindings you can pass them to your filter specification in order to be used when querying. Here’s a small snippet on how you can such dynamic filters:

  query =  from(w in whs_query, as: :withholding,
             join: b in Beneficiary, as: :beneficiary,
             on: [id: w.beneficiary_id])

  binding = :withholding
  field = :number
  value = "1"
​
  query |> where(
            [{^binding, t}],
            field(t, ^field) == ^value
          )

Thank you very much for helping with this !

6 Likes