FatEcto v0.5.0 FatEcto View Source

Description

Currently it's supporting following query functions:

• where
• where_or
• select
• joins
• order_by
• include
• group_by
• aggregate
• distinct

Where

Where include methods from Ecto.Query.API. These are the options where supports.

Example:

    query_ opts = %{
     "$where" => %{
        "name" => "%Joh%",
        "location" => nil,
        "$not_null" => ["total_staff", "address", "phone"],
        "rating" => "$not_null",
        "total_staff" => %{"$between" => [1, 3]}
      }
    }

   iex> build(FatEcto.FatHospital, query_opts)
   iex> #Ecto.Query< from(f0 in FatEcto.FatHospital,
        where:
        f0.total_staff > ^1 and f0.total_staff < ^3 and
          (not is_nil(f0.rating) and
             (f0.name == ^"%Joh%" and
                (is_nil(f0.location) and
                   (not is_nil(f0.phone) and
                      (not is_nil(f0.address) and (not is_nil(f0.total_staff) and ^true))))))
         )>

   query_ opts = %{
      "$where" => %{"rating" => %{"$lte" => "$total_staff"}}
    }

   iex> build(FatEcto.FatHospital, opts)
   iex> #Ecto.Query<from f in FatEcto.FatHospital,
        where: f.rating <= f.total_staff and ^true>

Where_or

Add or clause in the where when building a query.where_or is useful when you want to add multiple or conditions in the where conditional.

Example:

query_opts = %{
    "$where" => %{
      "$or" => %{
        "name" => %{"$like" => "%Joh%"},
        "total_staff" => %{"$between_equal" => [5, 7]}
      }
    }
  }
iex> build(FatEcto.FatHospital, query_opts)
iex> #Ecto.Query<from(f0 in FatEcto.FatHospital,
      where:
        (f0.total_staff >= ^5 and f0.total_staff <= ^7) or
          (like(fragment("(?)::TEXT", f0.name), ^"%Joh%") or ^true))>

Select

Select fields from the schema as well as from the associated schemas. You can pass fields as a list or in a map.

Example:

query_opts = %{
    "$select" => ["name", "location", "rating"],
    "$order" => %{"id" => "$desc"}
}
iex> build(FatEcto.FatHospital, query_opts)
iex> #Ecto.Query<from f in FatEcto.FatHospital, order_by: [desc: f.id],
     select: map(f, [:name, :location, :rating])>

query_ opts = %{
    "$select" => %{
      "$fields" => ["name", "location", "rating"],
      "fat_rooms" => ["beds", "capacity"]
    },
    "$where" => %{"id" => 2}
}
iex> build(FatEcto.FatHospital, query_opts)
iex> #Ecto.Query<from f in FatEcto.FatHospital, where: f.id == ^2 and ^true,
     select: map(f, [:name, :location, :rating, :id, {:fat_rooms, [:beds, :capacity]}])>

Joins

Joins with another table on the type of join you passed in the params. It supports where , select, order_by, group_by . Additionally, it also supports additional_on_clauses. Supported Join types are:

• inner
• left
• right
• full

Example

query_opts = %{
    "$select" => %{"$fields" => ["designation", "experience_years"]},
    "$full_join" => %{
      "fat_patients" => %{
        "$on_field" => "id",
        "$on_table_field" => "doctor_id",
        "$where" => %{"location" => "bullavard"},
        "$select" => ["name", "prescription"],
        "$order" => %{"appointments_count" => "$asc"},
        "$group" => "experience_years"
      }
    }
  }
iex> build(FatEcto.FatDoctor, query_opts)
iex> #Ecto.Query<from f0 in FatEcto.FatDoctor, full_join: f1 in "fat_patients",
     on: f0.id == f1.doctor_id, where: f1.location == ^"bullavard" and ^true,
     group_by: [f1.experience_years], order_by: [asc: f1.appointments_count],
     select: merge(merge(map(f0, [:designation, :experience_years]), %{^"fat_patients" => map(f1, [:name, :prescription])}), %{"$group" => %{^"experience_years" => map(f1, [:name, :prescription]).experience_years}})>

query_opts = %{
    "$inner_join" => %{
      "fat_rooms" => %{
        "$on_field" => "id",
        "$on_table_field" => "hospital_id",
        "$additional_on_clauses" => %{
          "rating" => %{"$not_between" => [1, 3]},
          "total_staff" => %{"$not_between_equal" => [4, 5]}
        },
        "$select" => ["beds", "capacity", "level"],
        "$where" => %{"incharge" => "John"}
      }
    },
    "$where" => %{"rating" => 3}
  }   
iex> build(FatEcto.FatHospital, query_opts)
iex> #Ecto.Query<from(
       h in FatEcto.FatHospital,
       where: h.rating == ^3 and ^true,
       inner_join: r in "fat_rooms",
       on:
        h.id == r.hospital_id and
          ((h.total_staff <= ^4 or h.total_staff >= ^5) and ((h.rating < ^1 or h.rating > ^3) and ^true)),
       where: r.incharge == ^"John" and ^true,
       select: %{^"fat_rooms" => map(r, [:beds, :capacity, :level])}
      )>  

Order_by

order_by returns the query by sort the results as asc or desc order.

Example

query_opts = %{
      "$select" => %{
        "$fields" => ["name", "location", "rating"],
        "fat_rooms" => ["beds", "capacity"]
      },
      "$order" => %{"id" => "$desc"}
    }
    iex> build(FatEcto.FatHospital, opts)
    iex> #Ecto.Query<from f in FatEcto.FatHospital, order_by: [desc: f.id],
         select: map(f, [:name, :location, :rating, :id, {:fat_rooms, [:beds, :capacity]}])>

query_ opts = %{
      "$select" => ["name", "location", "rating"],
      "$order" => %{"id" => "$asc"}
    }
iex> build(FatEcto.FatHospital, query_opts)
iex> #Ecto.Query<from f in FatEcto.FatHospital, order_by: [asc: f.id],
     select: map(f, [:name, :location, :rating])>

Include

include preload tables based on the conditions passed in the query params. You can specify the where , order_by , join, group_by in the include for the associated tables. You can also use nested include to preload nested relationships.

Example

query_opts = %{
    "$include" => %{
      "fat_hospitals" => %{"$include" => %{"fat_rooms" => %{"$include" => ["fat_hospital", "fat_beds"]}}},
      "fat_patients" => %{
        "$include" => %{
          "fat_doctors" => %{
            "$include" => %{"fat_hospitals" => %{"$include" => "fat_rooms", "$where" => %{"name" => "Joh"}}}
          }
        }
      }
    }
  }
iex> build(FatEcto.FatDoctor, query_opts)
iex> #Ecto.Query<from(f0 in FatEcto.FatDoctor,
      left_join: f1 in assoc(f0, :fat_hospitals),
      left_join: f2 in assoc(f1, :fat_rooms),
      left_join: f3 in assoc(f0, :fat_patients),
      left_join: f4 in assoc(f3, :fat_doctors),
      left_join: f5 in assoc(f4, :fat_hospitals),
      where: f5.name == ^"Joh" and ^true,
      limit: ^34,
      offset: ^0,
      preload: [
        ^[
          fat_patients: [fat_doctors: [fat_hospitals: :fat_rooms]],
          fat_hospitals: [fat_rooms: [:fat_hospital, :fat_beds]]
        ]
      ]
    )>

Group_By

group_by groups together rows from the schema that have the same values in the given fields. It also supports multiple group_by fields in a list.

Example

query_opts = %{
      "$inner_join" => %{
        "fat_rooms" => %{
          "$on_field" => "id",
          "$on_table_field" => "hospital_id",
          "$select" => ["beds", "capacity", "level"],
          "$where" => %{"incharge" => "John"}
        }
      },
      "$where" => %{"rating" => 3},
      "$group" => ["rating", "total_staff"]
    }

iex> build(FatEcto.FatHospital, query_opts)
iex> #Ecto.Query<from f0 in FatEcto.FatHospital, join: f1 in "fat_rooms",
     on: f0.id == f1.hospital_id, where: f0.rating == ^3 and ^true,
     where: f1.incharge == ^"John" and ^true, group_by: [f0.rating],
     group_by: [f0.total_staff],
     select: merge(f0, %{^:fat_rooms => map(f1, [:beds, :capacity, :level])})>

query_opts = %{
      "$select" => ["name", "location", "rating"],
      "$order" => %{"id" => "$asc"},
      "$group" => "rating"
    }

iex>  build(FatEcto.FatHospital, query_opts)
iex>  #Ecto.Query<from f in FatEcto.FatHospital, group_by: [f.rating],
      order_by: [asc: f.id], select: map(f, [:name, :location, :rating])>

Aggregate

Aggregate supports aggregate methods that can be passed as a string or a list.

Example

query_opts = %{
    "$aggregate" => %{"$max" => "nurses", "$min" => "capacity"},
    "$where" => %{"capacity" => 5},
    "$group" => "capacity"
  }
iex>  build(FatEcto.FatRoom, query_opts)
iex>  #Ecto.Query<from(f0 in FatEcto.FatRoom,
      where: f0.capacity == ^5 and ^true,
      group_by: [f0.capacity],
      select:
        merge(
          merge(merge(f0, %{"$aggregate" => %{"$max": %{^"nurses" => max(f0.nurses)}}}), %{
            "$aggregate" => %{"$min": %{^"capacity" => min(f0.capacity)}}
          }),
          %{"$group" => %{^"capacity" => f0.capacity}})
        )>

query_opts = %{
    "$aggregate" => %{"$sum" => ["nurses", "beds"], "$avg" => ["capacity", "nurses"]},
    "$where" => %{"capacity" => 5},
    "$group" => "capacity"
  }
iex>  build(FatEcto.FatRoom, query_opts)
iex>  #Ecto.Query<from(from(f0 in FatEcto.FatRoom,
      where: f0.capacity == ^5 and ^true,
      group_by: [f0.capacity],
      select:
        merge(
          merge(
            merge(
              merge(merge(f0, %{"$aggregate" => %{"$avg": %{^"capacity" => avg(f0.capacity)}}}), %{
                "$aggregate" => %{"$avg": %{^"nurses" => avg(f0.nurses)}}
              }),
              %{"$aggregate" => %{"$sum": %{^"nurses" => sum(f0.nurses)}}}
            ),
            %{"$aggregate" => %{"$sum": %{^"beds" => sum(f0.beds)}}}
          ),
          %{"$group" => %{^"capacity" => f0.capacity}}
        )
    ))>
  

Distinct

Adds distinct query expression to avoid duplication of records. You can pass the distinct field name or true.

Example

query_opts = %{
    "$distinct" => "name"
  }

iex> build(FatEcto.FatHospital, opts)
iex> Ecto.Query<from(h in FatEcto.FatHospital, distinct: [asc: h.name])>  

query_opts = %{
    "$distinct" => true
  }

iex> build(FatEcto.FatHospital, opts)
iex> Ecto.Query<from(h in FatEcto.FatHospital, distinct: true)>

You have to pass "$distinct_nested => true" alongwith "$distinct => true" if there are multiple order_by clauses in your query.

Paginator

FAT allows to restrict the number of results you want to return from the repo. You can define limit as limit and offset as skip in the config.

Example

opts = %{
      "$select" => ["name", "location", "rating"],
      "$order" => %{"id" => "$asc"},
      "$group" => "rating",
      "$limit" => 20,
      "$skip" => 0
    }

If no limit is defined then FAT automatically uses default_limit. For this to work you have to define the default_limit in fat_ecto config. see docs for more info.

Blacklist params

Fat ecto provides you control to restrict access to specific fields of different tables in your database. You have to set these fields in your fat ecto config. If these fields are present in the query, fat ecto will raise an error.

Example

blacklist_params: [
    {:fat_rooms, ["description"]},
    {:fat_beds, ["is_active"]},
    {:fat_hospitals, ["phone"]},
    {:fat_doctors, ["name"]},
    {:fat_patients, ["date_of_birth"]}
  ]
query_opts = %{
    "$select" => %{
      "$fields" => ["name", "purpose", "description"],
      "fat_beds" => ["purpose", "description"]
    },
    "$where" => %{"id" => room.id}
  }
iex> Query.build!(FatEcto.FatRoom, query_opts)
iex>  ** (ArgumentError) the fields ["description"] of fat_rooms are not allowed in the query