CODEWITHSUNDEEP
sqlelixirecto
Mr H
Mr H

Reputation: 5304

How to break a large Ecto.Query in smaller pieces?

Is there a way that I can break up this query in different pieces and build it as I need to. I need different parts of this.

Query works fine, however copy and pasting it just to change different parts is not very clean approach. I have pointed with # for the blocks that I need to reuse. 

 def by_id(user_id, company_id, asset_id) do

        # section 1
        from a in Asset,

        # section 2
        join: acl in AccessList, on: acl.asset_id == a.id, 
        join: c in Company, on: acl.company_id == c.id,
        join: u in User, on: acl.user_id == u.id, 

        # section 3
        where: u.id == ^user_id and c.id == ^company_id

        # section 4 
        and a.id == ^asset_id,
        # section 5
        select: %{ 
            asset_name: a.asset_name, 
            asset_id: a.id,
            inserted_at: a.inserted_at 
            }, 

        # section 6
        group_by: a.id,

        # section 6 
        order_by: a.id

    end

Upvotes: 0

Views: 156

Answers (2)

Robert
Robert

Reputation: 985

I've written extensively on how you might compose Ecto queries. When you combine this technique with pattern matching, you can write complex queries pretty easily.

Here's an example for a system with users being queried based on company. 

def list_users(_user, "admin", params) do
  User
  |> filter_by_params(params |>  Map.to_list())
end

def filter_by_params(query, params) do
  Enum.reduce(params, query, fn
    tuple, query ->
      filter_dataset(query, tuple)
  end)
end

def filter_dataset(query, {"company_id", company_id}) do
  query
  |> join(:inner, [u], c in assoc(u, :company))
  |> where([_u, ..., company], company.id == ^company_id)
end

def filter_dataset(query, _no_matching_tuple), do: query

Upvotes: 0

Aleksei Matiushkin
Aleksei Matiushkin

Reputation: 121000

From Ecto.Query documentation:

Ecto queries are composable. For example, the query above can actually be defined in two parts:

# Create a query
query = from u in User, where: u.age > 18
# Extend the query
query = from u in query, select: u.name

That said, you might:

q1 = from a in Asset

q2 = from a in q1,
     join: acl in AccessList, on: acl.asset_id == a.id, 
     join: c in Company, on: acl.company_id == c.id,
     join: u in User, on: acl.user_id == u.id

etc.

Upvotes: 3

Related Questions