zven21/turbo_ecto

A rich ecto component, including search sort and paginate.

Turbo Ecto

Turbo is a very rich ecto component,including search sort and paginate. Inspiration by ruby ransack and learn from rummage_ecto

Phoenix support turbo_html, check this repos.

Table of contents

• Getting started
• Examples
• Search Matchers
• Features
• Contributing
• Make a pull request
• License
• Credits

Getting started

The package can be installed by adding :turbo_ecto to your list of dependencies in mix.exs:

def deps do
  [
    {:turbo_ecto, "~> 1.0.1"}
  ]
end

Add the Repo of your app and the desired per_page to the :turbo_ecto configuration in config.exs:

config :turbo_ecto, Turbo.Ecto,
  repo: MyApp.Repo,
  per_page: 10

You can also define other configurations with entry_name and pagenate_name in config.exs.

Examples

Category Table Structure:

Field	Type	Comment
name	string

Post Table Structure:

Field	Type	Comment
name	string
body	text
price	float
category_id	integer
available	boolean

iex> params = %{
      "q" => %{"name_and_category_name_like" => "elixir"},
      "s" => "inserted_at+asc",
      "page" = 0,
      "per_page" => 20
     }

iex> Turbo.Ecto.turbo(Turbo.Ecto.Schemas.Post, params)
%{
  datas: [%Turbo.Ecto.Schemas.Post{}],
  paginate: %{
    current_page: 10,
    next_page: 11,
    per_page: 20,
    prev_page: 9,
    total_count: 100,
    total_pages: 20
  }
}

The 2 more commonly used API are as follows：

Turbo.Ecto.turbo(queryable, params, opts \\ [])

• 1 queryable: receives a schema object or an Ecto.Query.t() object

• 2 params: supports 4 parameters.

  • q or filter to receive pattern matching information, e.g. params = %{"q" ⇒ %{"name_like" ⇒ "elixir"}} or params = %{"filter" ⇒ %{"name_like" ⇒ "elixir"}
  • s or sort Receive sort information. e.g. params = %{"sort" ⇒ "position+asc"} or params = %{"s" ⇒ "inserted_at+desc"}
  • page Receive query page number. e.g. params = %{"page" ⇒ 1}
  • per_page Receive the number of pages. e.g. params = %{"per_page" ⇒ 20}

• 3 opts: currently receives the following information:

  • paginate_name: sets the pagination key value of the returned result
  • entry_name: sets the key value of the returned result object
  • prefix: table prefix
  • with_paginate: whether to include pagination information, default true
  • callback: callback processing for queryable

Turbo.Ecto.turboq(queryable, params, opts)

Returns an Ecto.Query.t() object

More example pls move: docs

Search Matchers

List of all possible search_types:

Predicate	Description	Note
*_eq	equal	SQL: col = 'value'
*_not_eq	not equal	SQL: col != 'value'
*_lt	less than	SQL: col < 1024
*_lteq	less than or equal	SQL: col <= 1024
*_gt	greater than	SQL: col > 1024
*_gteq	greater than or equal	SQL: col >= 1024
*_is_present	not null and not empty	Only compatible with string columns. e.g.: q[name_present ]=1 SQL: col is not null AND col != ''
*_is_null	is null true or false	SQL: col is null or col is not null
*_like	contains value	SQL: col LIKE '%value%'
*_ilike	contains any of	SQL: col ILIKE '%value%'
*_is_true	is true or false	SQL: col is true or col is false
*_is_not_true	is true or false	SQL: col is not true or col is false
*_is_false	is true or false	SQL: col is false
*_is_not_false	is true or false	SQL: col is not false
*_is_null	is null	SQL: col is nil
*_is_not_null	is not null	SQL: col is not nil
*_in	match any values in array	e.g.: q[name_in][]=Alice&q[name_in][]=Bob SQL: name in ('Alice', 'Bob')
*_not_in	not contains	SQL: col not in ('Alice', 'Bob')
*_start_with	start with values	SQL: col LIKE '%value'
*_not_start_with	not start with values	SQL: col not LIKE '%value'
*_end_with	end with values	SQL: col LIKE 'value%'
*_not_end_with	not end with values	e.g.: q[name_not_end_with][]=Alice SQL: col not LIKE 'value%'
*_between	begin < between < end	e.g.: q[price_between][]=100&q[price_between][]=200 SQL: 100 <= price and price <= 200)

Contributing

Bug report or pull request are welcome.

Make a pull request

1. Fork it
2. Create your feature branch (git checkout -b my-new-feature)
3. Commit your changes (git commit -am 'Add some feature')
4. Push to the branch (git push origin my-new-feature)
5. Create new Pull Request

Please write unit test with your code if necessary.

Copyright and License

Copyright (c) 2018 Zven Wang

The library is available as open source under the terms of the MIT License.

Credits

• ecto - Very great API.
• ransack - Initial inspiration of this project.
• rummage_ecto - Similar implementation.

turbo_ecto's Issues

Usage with multitenancy libraries like Triplex

For this I need an easy way to pass a prefix param to Repo.

  def list_users do
    Repo.all(Users, prefix: "demo")
  end

and my urbo repo call is:

 Turbo.Ecto.turbo(Users, params, entry_name: "users")

support *_not_eq keyword at searching.

Product table structure

Field	Type	Comment
title	string
body	text
price	float
available	boolean
inserted_at	datetime
uupdated_at	datetime

Input

url_query = http://localhost:4000/products?q[price_not_eq]=100

Transfer:

iex> params = %{"q" => %{"price_not_eq" = 100}}
iex> Turbo.Ecto.turboq(Product, params)

Expect output:

#Ecto.Query<from t in Product, where: t.price != ^100>

support assoc tables at sort params.

support *_blank keyword

Product table structure

Field	Type	Comment
title	string
body	text
price	float
available	boolean
inserted_at	datetime
uupdated_at	datetime

Input:

iex> params = %{"q" => %{"price_blank" = true}}
iex> Turbo.Ecto.turboq(Product, params)

Expect output:

#Ecto.Query<from t in Product, where: t.price = '', or_where: is_nil(t.price)>

Status of the lib

Hi @zven21,

Is this lib still maintained?

`or` operator not work.

Raise actual error reason during search

In the scenario where a search field is not part for the schema(queryable) supplied to turboq/2, here is the current result in the case of an error (RuntimeError) Expected params, got %{"q" => %{"name_like" => "son"}, "s" => "inserted_at+asc"} raised from

turbo_ecto/lib/turbo_ecto/builder.ex

Line 99 in ff3566e

{:error, _} -> raise "Expected `params`, got #{inspect(params)}"

Current error is generic and does not help in terms of debugging issues.

I will suggest returning the exact error reason from the tuple (:error, reason) returned from the with clause execution e.g

turbo_ecto/lib/turbo_ecto/hooks/search/attribute.ex

Line 23 in ff3566e

{_assoc, nil} -> {:error, :attribute_not_found}

currently this is being silenced and a generic error message is returned.

A solution can be to match against the returned error reason atom and provide human readable error messages. Something like;

def run(queryable, params) do
    schema = extract_schema(queryable)
    params = Utils.stringify_keys(params)

    with {:ok, %Search{} = searches} <- Search.run(schema, params),
         {:ok, sorts} <- Sort.run(schema, params),
         {:ok, %Paginate{} = %{limit: limit, offset: offset}} <- Paginate.run(params) do
      relations = build_relations(searches, sorts)
      binding = relations |> build_binding()

      queryable
      |> join(relations)
      |> where(searches, binding)
      |> order_by(sorts, binding)
      |> limit(limit, binding)
      |> offset(offset, binding)
    else
      {:error, :attribute_not_found} -> raise "One or more keys in your search are not contained or can't be linked to the specified schema"
      {:error, _} -> raise "Expected `params`, got #{inspect(params)}"
    end
  end

How to Preload associations data?

Is there a way to preload associations data?

result = Product |> Turbo.Ecto.turbo(params, [entry_name: "entries"])

How we do also preload category?
ps. I am a newbie

A better experience for param query and sort

support *_is_true keyword

support assoc table within search.

Table structure

• Category

Field	Type	Comment
name	string

• Product

Field	Type	Comment
title	string
body	text
price	float
category_id	integer
available	boolean

• Variant

Field	Type	Comment
title	string
price	float
product_id	integer

Input

url_query = http://localhost:4000/varinats?q[product_category_name_like]=elixir

Process:

iex> params = %{"q" => %{"product_category_name_like" = "elixir"}}
iex> Turbo.Ecto.turboq(Variant, params)

Expect output:

#Ecto.Query<from v in Varinat, join: p in Product,
 on: v.product_id == p.id, join: c in Category, on: p.category_id == c.id,
 where: like(c.name, ^"%elixir%")>

support 'and' associate symbol.

Product table structure

Field	Type	Comment
title	string
body	text
price	float
available	boolean
inserted_at	datetime
uupdated_at	datetime

Input

url_query = http://localhost:4000/products?q[title_or_body_like]=elixir

Transfer:

iex> params = %{"q" => %{"title_and_body_like" = "elixir"}}
iex> Turbo.Ecto.turboq(Product, params)

Expect output:

#Ecto.Query<from t in Product, where: like(t.title, ^"elixir"), where: like(t.body, ^"elixir")>

support 'or' associate symbol.

Product table structure

Field	Type	Comment
title	string
body	text
price	float
available	boolean
inserted_at	datetime
uupdated_at	datetime

Input

url_query = http://localhost:4000/products?q[title_or_body_like]=elixir

Transfer:

iex> params = %{"q" => %{"title_or_body_like" = "elixir"}}
iex> Turbo.Ecto.turboq(Product, params)

Expect output:

#Ecto.Query<from t in Product, where: like(t.title, ^"elixir"), or_where: like(t.body, ^"elixir")>

support *_is_null keyword

support *_between keyword

Product table structure

Field	Type	Comment
title	string
body	text
price	float
available	boolean

Input:

url_query = http://localhost:4000/products?q[price_between][]=100&q[price_between][]=200

Transfter

iex> params = %{"q" => %{"price_between" = ["100", "200"]}}
iex> Turbo.Ecto.turboq(Product, params)

Output:

#Ecto.Query<from t in Product, where: (t.price between ^100 and ^200)>

support with . interval between table assoc

The current state of the library

I've read the docs and the title page of the project but still left in some doughts about what features are supported.

Does it support filtering entities by values of their associations and if the answer is yes, where can I see the examples of how it can be done?

If the previous question has positive answer, then the second question is are there any problems with associations namings if for example they have same naming for different sub-associations or if they have underscores in their names? I'm asking because from example it seems that the names are havily used in search form params names.

The third question: are search queries like
SELECT * from entities e where (e.field1=‘value1’ OR e.field1=‘value2’) AND (e.field2=‘value3’ OR e.field2=‘value4’ OR …)
with grouping of AND and OR where closes supported?

Fourth question: Ecto.Query.Api supports functions like datetime_add/3, date_add/3, from_now/2, ago/2 for filtering by date intervals. How can we incorporate them within the search using your library?

support *_present keyword

Product table structure

Field	Type	Comment
title	string
body	text
price	float
available	boolean
inserted_at	datetime
uupdated_at	datetime

Input:

iex> params = %{"q" => %{"price_present" = true}}
iex> Turbo.Ecto.turboq(Product, params)

Expect output:

#Ecto.Query<from t in Product, where: t.price != '', where: not(is_nil(t.price))>

Support for Ecto 3.

Hi!
Wonder if there's any plans to create a public release for supporting Ecto 3. As our library is dependent on your library and we want to upgrade to Ecto 3 (hex wont allow for any non released packages afaik) it would be awesome if it can happen :)

FLEWID-AB/alkemist#24

Support for ecto 3.0

I am trying out a phoenix 1.4 codebase, and for that, the ecto is 3.0. Due to the constaints on this package, I'm unable to use it. Could you please upgrade and release a new version?

Custom Turbo.Ecto.turbo returns

Support Ecto schema Embed

support *_in keyword

Product table structure

Field	Type	Comment
title	string
body	text
price	float
available	boolean
inserted_at	datetime
uupdated_at	datetime

Input:

url_query = http://localhost:4000/products?q[price_in][]=100&q[price_in][]=200

Transfter

iex> params = %{"q" => %{"price_in" = ["100", "200"]}}
iex> Turbo.Ecto.turboq(Product, params)

Output:

#Ecto.Query<from t in Product, where: t.price in (100, 200)>

Improve the implementation of the search section.

Add dummy(demo) for Graphql

Bug: base-query with join generates improper sql

I need to layer the turbo-ecto query with a base-query:

def list_paginated_reviews(params, user) do
    Review
    |> with_friends(user)
    |> preload(...)
    |> Turbo.Ecto.turbo(params)
end
  
def with_friends(review, user) do
  review
  |> join(:inner, [r], uf in UserFriend, on: uf.user_id == ^user.id)
  ...

Trying to filter by category-name:

from r0 in Review,
 join: u1 in UserFriend,
 on: u1.user_id == ^2,
 join: c2 in Category,
 on: c2.id == r0.category_id,
 where: u1.name == ^"Cool stuff",
 order_by: [desc: r0.inserted_at],
 ...

Am I doing something wrong?