Insert multiple where clause based on user input params in Ecto query

Phoenix Forum Questions / Help
, ,
1

I have a product listing page in which I want to give a search product option if the user enters any search term for product name or product price then the search term should be included in the final query otherwise I want to show full product list.

The query is written in my module to fetch all product having word “Widget” in the product name and product price less than $10 is as shown below:

def list_products(params) do
    query = from p in MyStore.Products.Product,
                 where: like(p.name,"%Widget%"),
                 where: p.price <= 10,
                 order_by: [
                   desc: p.id
                 ],
                 select: struct(
                   p,
                   [:id, :name, :quantity, :inserted_at, :updated_at]
                 )
    products = MyStore.Repo.paginate(query,params)
end

Now if the user enters Product name or Product price in the Product filter form I want to include that variable in my query instead of static where clause I have passed in above code. I need the “where” clause optional if user wants to filter product list then “where” clause should be added otherwise it should show the complete product list. It will be great if someone provides me the correct demo code based on the above query.

2

The good way is to reduce a criteria list into a query…

3

It will be helpful if you provide me the code how to achieve for above code as I am learning so there are lots of new things for me

4

Something like this… (it’s related to a User)

  def list_users_query(criteria \\ []) do
    query = from p in User

    Enum.reduce(criteria, query, fn
      {:limit, limit}, query ->
        from p in query, limit: ^limit

      {:offset, offset}, query ->
        from p in query, offset: ^offset

      {:filter, filters}, query ->
        filter_with(filters, query)

      {:order, order}, query ->
        from p in query, order_by: [{^order, ^@order_field}]

      {:preload, preloads}, query ->
        from p in query, preload: ^preloads

      arg, query ->
        Logger.info("args is not matched in query #{inspect arg}")
        query
    end)
  end

  defp filter_with(filters, query) do
    Enum.reduce(filters, query, fn
      {:name, name}, query ->
        pattern = "%#{name}%"
        from q in query, where: ilike(q.name, ^pattern)
    end)
  end

Then You could do…

iex> Accounts.list_users filter: [name: "dmi"], limit: 1
10 Likes
5

How to write the condition if there is no filter I mean if user has landed to product listing page but not applied any filter I think in that case it will not work.

How can I pass order by clause while using reduce criteria ?

6

It’s optional… If You don’t pass criteria, it will not be used

You can also pass an order parameter.