Dynamic query composition with Ecto

Presentation discusses dynamic query composition with Ecto in Elixir. While describing the Ecto Query types in the process of query composition.

The fields of Ecto.Query struct are presented along the way as intermediary steps in the composition process.

Transcript

1. Building dynamic queries with Ecto Kolawole Gabriel
2. About me • Off work I teach and blog @ http://blog.gabrielo.tech/ • Actively working on Hub88 (Integration Hub) • A Recreational basketball player
3. What I have built with Elixir!
4. Telegram Betting Bot Sports betting bot on telegram
5. Wheel of fortune game engine Promotional game engine.
6. Why dynamic queries? One of the key quality every application should possess is the ability to query its own data and make sense of it, in different forms and shapes.
7. It will be nice to query our data in this form; [ %{field: "operator_id", value: [1,2], operator: "in"} %{field: "amount", value: "1000", operator: "<"}, %{field: "session.user", value: "15", operator: "="}, %{field: "session.supplier_game.game.product.name", value: "TGC", operator: "="}, %{field: "session.operator_game.operator.name", value: "Bender", operator: "="}, %{field: "session.currency.code", operator: "in", value: "BTC, EUR"} ]
8. The models • Transaction • Session • Supplier_game • Game • Product • Operator_game • Operator
9. Query Groups
10. Ecto.Query Ecto.Query{} |> Repo.callback()
11. Ecto.Query
12. Example [ %{field: "amount", value: "1000", operator: ">"}, %{field: "status", operator: "in", value: "value: "TS_SUCCESS, TS_DECLINED"}, %{field: "session.supplier_game.game.product.name", operator: "=", value: "value: "TGC"} ]
13. Our base query from(t in Transaction)
14. Composer Function def compose_query(queryable, expression_list, base_query) do expression_list |> Enum.reduce_while(base_query, fn exp, acc -> queryable |> compose_query(exp, acc) |> case do {:ok, query} -> {:cont, query} {:error, reason} -> {:halt, reason} end end) |> adjust_bindings() end
15. Composer Function def compose_query(queryable, filter, query) do if has_joins(filter.field) do process_nested(filter, queryable, query) else process_simple(filter, queryable, query) end end
16. Simple Queries Processing Using : # Ecto.Query.API.field/2 field(source, field)
17. Simple Queries (Cont) def process_simple(%{field: field, operator: operator, value: value}, queryable, query) do %{field => value} |> cast_filter(queryable) |> compose_filter(field_value_turple, operator, query) end end
18. Simple Queries (Cont) def compose_filter({field, value}, operator, query) do case operator do "=" -> where(query, [q], field(q, ^field) == ^value) ">" -> where(query, [q], field(q, ^field) > ^value) "<" -> where(query, [q], field(q, ^field) < ^value) "in" -> where(query, [q], field(q, ^field) in ^value) end end
19. Intermediate Result
20. Nested Queries Processing def process_nested(filter, queryable, query) do filter.field |> make_assoc_field_binary() # striping field names away from nested field |> build_assoc_query(queryable, query) # append associations to the query |> finalize_assoc_query(query, new_filter) end
21. Nested Queries (Cont) - build_assoc_query Using : # Ecto.Query.join/5 join(query, :inner, [a, ..., z], u in ^right_queryable, field(z, ^field) == u.id)
22. Intermediate Result [1]
23. Intermediate Result [2]
24. Intermediate Result [3]
25. query |> IO.inspect()
26. Lessons and Conclusion • Unstrict nature of Ecto • Knowledge is power
27. Thank You!