Fat_ecto - dynamically build queries

fat_ecto provides methods for dynamically building queries according to the parameters it receives.
Currently it supports the following query functions :

  • where
  • select
  • joins
  • order_by
  • include


    query_opts = %{
    "$select" => %{
      "$fields" => ["name", "location", "rating"],
      "fat_rooms" => ["beds", "capacity"]
    "$order" => %{"id" => "$desc"},
    "$where" => %{"rating" => 4},
    "$group" => "nurses",
    "$include" => %{
      "fat_doctors" => %{
        "$include" => ["fat_patients"],
        "$where" => %{"name" => "ham"},
        "$order" => %{"id" => "$desc"},
        "$join" => "$right"
    "$right_join" => %{
      "fat_rooms" => %{
        "$on_field" => "id",
        "$on_join_table_field" => "hospital_id",
        "$select" => ["beds", "capacity", "level"],
        "$where" => %{"incharge" => "John"}

and the output will be

    iex > FatEcto.FatQuery.build(FatEcto.FatHospital, query_opts)
    #Ecto.Query<from f0 in FatEcto.FatHospital, right_join: f1 in "fat_rooms",
    on: f0.id == f1.hospital_id, right_join: f2 in assoc(f0, :fat_doctors),
    where: f0.rating == ^4 and ^true, where: f1.incharge == ^"John" and ^true,
    group_by: [f0.nurses], order_by: [desc: f0.id],
    select: merge(map(f0, [:name, :location, :rating, :id, {:fat_rooms, [:beds, :capacity]}]), %{^:fat_rooms => map(f1, [:beds, :capacity, :level])}),
    preload: [fat_doctors: #Ecto.Query<from f0 in FatEcto.FatDoctor, left_join: f1 in assoc(f0, :fat_patients), where: f0.name == ^"ham" and ^true, order_by: [desc: f0.id], limit: ^10, offset: ^0, preload: [:fat_patients]>]>

Awesome work! :+1:

This looks like a great library, but with such a powerful interface, I think it should be used extremely carefully. It’s a very easy way to expose serious security vulnerabilities in your application by exposing to the client data that shouldn’t be exposed - a naive example could be a password_hash field on some User or Account schema that could be requested either directly or going through some association. I believe there should be at least a whitelist defining what fields the function is allowed to act upon, exposing just anything seems rather reckless to me.


GraphQL is good for that. Taking, say, Absinthe’s GraphQL parser and parsing out a set of ecto commands would be quite useful and much safer using it’s schema definitions.


totally agree with you. currently its being used in admin panels. What do you propose if I try to support this. what’s the right path/approach for me?

1 Like

Whitelisting mechanism I believe is little complex because this library not only supports include but also custom joins with custom names. I would love to hear your thoughts

1 Like