Filter on associations?
chevinbrown opened this issue ยท 9 comments
From the documentation, it's difficult to tell how to filter/query on associated records. Is that part of the feature set? If so, I'll be happy to provide some documentation/contribution.
Yes, it is. It's just because of how the underlying database works with primary/foreign keys. See this issue: #38. I would be supremely grateful for a contribution.
As I understand this issue, this will require multiple db calls?
I think a quick example of a has_one relationship filter/query would be most beneficial. ๐
Perhaps I did not understand you correctly. Are you wanting to filter on a joined table from a given query? In that case, you'll need to base the filter off of that association (or reference it potentially by the relationship key path). I'm happy to consider a particular use case. That might clarify the issue a bit.
Let's say I have a user & users have_one :profile
.
I want to query various things on the profile as well as the "base query" user table.
The user may have an email and various fields, while the profile contains first/last name and various other fields.
I have a bit of a better idea now, which I'll keep working at in my free time this next week. ๐ธ
As a non-working example:
def filter_config(:users) do
defconfig do
text :email
number :status
...
end
end
def pi_filter(:profile) do
defconfig do
text :first_name
...
end
end
{:ok, filter} <- Filtrex.parse_params(filter_config(:users), params["user"] || %{}),
{:ok, profile_filter} <- Filtrex.parse_params(pi_filter(:profile), params["profile"] || %{}),
result <- do_query(filter, profile_filter) do ..
defp do_query(filter, profile_filter, params) do
User
|> preload([:profile, ...])
|> profile_query(profile_filter)
|> Filtrex.query(filter)
|> order_by(^sort(params))
|> paginate(Repo, params, @pagination)
end
def profile_query(query, profile_filter) do
Profile |> Filtrex.query(profile_filter)
end
Obviously this doesn't work, but I imagine some composition should work.
@rcdilorenzo Got it working--let me know your thoughts and I'll provide a readme update showing usage of filtering on associations.
defmodule MyApp.Users.Users do
import Ecto.Query, warn: false
import Filtrex.Type.Config
alias MyApp.Repo
alias MyApp.Users.{User, PersonalInformation}
def filter_users(params \\ %{}) do
with {:ok, filter} <- Filtrex.parse_params(filter_config(:users), params["user"] || %{}),
{:ok, pi_filter} <- Filtrex.parse_params(pi_filter(:pi), params["pi"] || %{}),
users <- do_filter_users(filter, pi_filter, params) do
{:ok, %{users: users}}
else
{:error, error} -> {:error, error}
error -> {:error, error}
end
end
defp do_filter_users(filter, pi_filter, params) do
q1 = User
|> Filtrex.query(filter)
q2 = PersonalInformation
|> Filtrex.query(pi_filter)
qx =
from user in q1,
join: pi in ^q2,
on: pi.user_id == user.id
qx
|> preload([:personal_information, :market])
|> order_by(^sort(params))
|> Repo.filter(params)
end
def filter_config(:users) do
defconfig do
text :email
number :status
...
end
end
def pi_filter(:pi) do
defconfig do
text :first_name
end
end
end
I imagine Ecto 3 will vastly improve this and allow for dynamic building...but until then, I think this is the only way to accomplish what I'm looking for.
Excellent! This looks good. If it's possible to simplify parts, I think that would be even better. For example, the following lines could probably be excluded and yet still convey how association filtering can be accomplished:
# ...
else
{:error, error} -> {:error, error}
error -> {:error, error}
# ...
Yeah, I probably won't include that part b/c those tuples are used on my controllers. ๐ธ
I'll send you a PR! (hopefully sooner than later)