Dynamic filter for Ecto Preload

Questions / Help
1

Good Morning Everyone.

I’m playing aorund with Ecto Dynamic to enable filtering on any column. I have the following code which works perfectly (it was taken pretty much as is from the docs)

def filter(filters) do
  filter =
    Enum.reduce(
      filters,
      dynamic(true),
      fn
        {:code, value}, dynamic ->
          dynamic([n], ^dynamic and like(p.code, ^value))

        {:name, value}, dynamic ->
          dynamic([n], ^dynamic and like(p.name, ^value))

        {_, _}, dynamic ->
          dynamic
      end
    )

  from(
    p in Post,
    where: ^filter,
    preload: [:comments]
  )
  |> Repo.all()
end

I’d like to be able to extend the filtering to the preloaded comments - so I could filter on Post.name AND comment.foo.

Is there a way to rewrite the above code to enable a filter on the :comments preload (as well as the parent table) or do I have to do this as a seperate :comment query?

cheers

Dave

2

Hey

This thread looks similar, check it out

3

Thanks @fuelen - I had a read and quick attempt but couldn’t get it to work. I’ll give it another go at lunchtime (but any clues as to what the code should look like will be gratefully recevied :- :wink: )

cheers

4

it would be easier to help you if you provide input and expected output of your function :wink:

5

thanks @fuelen. I’m going to have a play at lunchtime - I might come back and ask for some help (with details of course) if I get stuck.

Have a great day.

cheers

Dave

6

Thanks to the pointers fromn @fuelen - I ended up with the following (just putting it here in case anyone else has a similar need.

def filter(filters) do
  filter =
    Enum.reduce(
      filters,
      dynamic(true),
      fn
        {:code, value}, dynamic ->
          dynamic([n], ^dynamic and like(p.code, ^value))

        {:name, value}, dynamic ->
          dynamic([n], ^dynamic and like(p.name, ^value))
          
        {:report_category_name, value}, dynamic ->
          dynamic([{^:report_category, table}], ^dynamic and like(field(table, ^:name), ^value))

        {_, _}, dynamic ->
          dynamic
      end
    )

  from(
    p in Post,
    join: rc in ReportCategory,
    on: p.report_category_id == rc.id,
    as: :report_category,
    where: ^filter,
    select: %{
      title: p.title,
      report_category_name: rc.name
    }
  
  )
  |> Repo.all()
end

cheers

Dave