bharani91
Dynamically composing Ecto queries
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!
First Post!
nerdyworm
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
Popular in Questions
Other popular topics
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #deployment
- #library
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #channels
- #elixirconf
- #exunit
- #discussion
- #code-sync
- #javascript
- #podcasts
- #onsite
- #dialyzer
- #docker
- #authentication
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #blog-post
- #graphql
- #genstage
- #ai
- #websockets
- #supervisor
- #elixirconf-us
- #advent-of-code
- #distillery
- #processes
- #forms
- #api
- #metaprogramming
- #hex
- #security









