FAT

Description

FAT provides methods for dynamically building queries depending on the parameters it receive.

Currently it's supporting following query functions:

• where
• select
• joins
• order_by
• include
• group_by

Installation

For now, you can add directly via github

{:fat_ecto, github: "tanweerdev/fat_ecto"}

Config

config :fat_ecto, :repo,

  seed_base_path: "priv/seed/csvs/",

  base_repo: ExApi.Repo,

  query_repo: ExApi.Repo,

  default_limit: 10,

  max_limit: 100

If available in Hex, the package can be installed by adding fat_ecto to your list of dependencies in mix.exs:

def deps do

[

{:fat_ecto, "~> 0.1.0"}

]

end

Use

Once installed you can importFatEcto.FatQuery inside your module and use the build method. Which is the entry method for building every query. And also the main method for the FatEcto.FatQuery.

build(schema_name, params)

Example

import FatEcto.FatQuery
query_opts = %{
      "$select" => %{
        "$fields" => ["name", "location", "rating"],
        "fat_rooms" => ["beds", "capacity"]
      },
      "$order" => %{"id" => "$desc"},
      "$where" => %{"rating" => 4},
      "$group" => ["total_staff", "rating"],
      "$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"},
          "$order" => %{"level" => "$asc"}
        }
      }
    }
iex> build(FatEcto.FatHospital, query_opts)
iex> #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.total_staff], group_by: [f0.rating], order_by: [asc: f1.level],
     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]>]>

Options:

These are the options supported

see docs for more details.

Where

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

Example:

    query_ opts = %{
      "$where" => %{"designation" => %{"$ilike" => "%surge %"}}
    }

   iex> build(FatEcto.FatDoctor, query_opts)
   iex> #Ecto.Query<from f in FatEcto.FatDoctor,
    where: ilike(fragment("(?)::TEXT", f.designation), ^"%surge %") 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>

see docs for more info

Select

Select include fields from the models as well as from the associated models and also adds foreign key dynamically. It also select fields from the model as a list.

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]}])>

see docs for more info

Joins

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

• inner
• left
• right
• full

Example

query_opts = %{
      "$right_join" => %{
        "fat_rooms" => %{
          "$on_field" => "id",
          "$on_join_table_field" => "hospital_id",
          "$select" => ["beds", "capacity", "level"],
          "$where" => %{"incharge" => "John"},
          "$order" => %{"nurses" => "$asc"}
        }
      }
    }
iex> build(FatEcto.FatHospital, opts)
iex> Ecto.Query<from f0 in FatEcto.FatHospital, right_join: f1 in "fat_rooms",
     on: f0.id == f1.hospital_id, where: f1.incharge == ^"John" and ^true,
     order_by: [asc: f1.nurses],
     select: merge(f0, %{^:fat_rooms => map(f1, [:beds, :capacity, :level])})>

see docs for more info.

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])>

see docs for more info.

Include

include has different sub methods. You can specify the where , order_by , join in the include for the associated schema.

Example

query_opts = %{
      "$include" => %{
        "fat_hospitals" => %{
          "$join" => "$right",
          "$order" => %{"id" => "$desc"},
          "$where" => %{"name" => "Saint"}
        }
      },
      "$where" => %{"name" => "John"}
    }
iex> build(FatEcto.FatDoctor, opts)
iex> #Ecto.Query<from f0 in FatEcto.FatDoctor,
     right_join: f1 in assoc(f0, :fat_hospitals),
     where: f0.name == ^"John" and ^true,
     preload: [fat_hospitals: #Ecto.Query<from f in FatEcto.FatHospital,
     where: f.name == ^"Saint" and ^true, order_by:  [desc: f.id], limit: ^10, offset: ^0>]>

see docs for more info.

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_join_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])>

see docs for more info.

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.

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.

Documentation

Documentation can be generated with ExDoc

and published on HexDocs. Once published, the docs can

be found at https://hexdocs.pm/fat_ecto.