Dynamically composing Ecto queries

Questions / Help
1

I’ve been struggling to compose Ecto queries. I have 2 models - a Person model and a traits model like so -

Person -> has_many -> Traits
Trait -> belongs_to -> Person

The schema for Traits model is like this -

schema "person_attributes" do
    field :name, :string
    field :value, :string
    belongs_to :person, App.Person

    timestamps()
end

What I am trying to achieve is dynamically filter people based on certain traits. Eg: find only those people who have a trait called ‘plan’ with a value of ‘Business’.

I should also be able to add more trait-based filters based dynamically, eg: people who have trait with name == ‘plan’ and value == ‘business’ AND another trait with name == ‘medium’ and value == ‘mobile’.

(From the front-end ui, the user can choose to add more trait-based filters via a dropdown and this query should reflect that trait’s name & value.)

I think this is the query that I need. My only issue is that I am not able to generate it dynamically.

from s in Person,
    where: s.account_id == ^1 and s.state == ^"active",
    join: c in Trait, where: c.person_id == p.id,
    where: (c.name == ^"plan" and c.value == "pro") or (c.name == ^"creation_date", fragment("CAST(coalesce(?, '0') AS integer)", c.value) >= 1470216128)

Any help would be highly appreciated!
Thanks!

1 Like
2 
def search(params) do
  query = from s in Person, where: s.account_id == 1

  query = if params.plan != "" do
      where(query, [p], p.name == ^params.plan)
    else
      query
    end
   # other logic for building the query
  query
end

And of course you can use the query variable to compose another like so

query = search(%{q: "search for q"})

query
|> where([p], p.person_id == ^id)
|> Repo.all

This was also a very helpful blog post covering the topic: https://blog.drewolson.org/composable-queries-ecto/

Let me know if this helps.

Cheer,
Benjamin