Building query dynamically

Questions / Help
1

I am working on this library for building dynamic queries. Currently it supports multiple query options. I am working on joins and I have these params

opts = %{
  "$inner_join" => %{
    "example" => %{
      "$on_field" => "id",
      "$on_join_table_field" => "first_name",
      "$select" => ["id"],
  "$where" => %{"first_name" => %{"$like" => "%Ham %"}}
      
    }
  }
}

I will pass model name and these params and it gives me the query:

Ecto.Query<from j in TestModel.Join, join: e in "example",
on: j.id == e.first_name, where: like(j.first_name, ^"%Ham %"),
select: merge(j, map(e, [:id]))>

I am building this query in small pieces. passing it to the next query option ana at the end I have this complete query building dynamically.

The problem is with this where. I want this where to work on the join model example. But it works on the model name.

 queryable =  Ecto.Query<from j in TestModel.Join, join: e in "example",
 on: j.id == e.first_name>

I pass this query in where

  from(q in queryable, where: like(field(q, ^String.to_existing_atom("first_name")), ^"Ham"))

And it gives me the end query result.

How can i make where to work on the join model(example) instead of the model.

Any ideas or suggestions?

Thanks

2

You can use query composition in this format:

           from(
              [q, ..., c] in queryable,
              where: like(field(c, ^String.to_existing_atom(key)), ^value)
            )

It will bind the where with the join table.

3 Likes
3

This is a great post on building composable queries https://blog.drewolson.org/composable-queries-ecto

3 Likes