How to do dynamic table joins using ecto?

Questions / Help
1

I posted this to stackoverflow, but was told here would a better forum.

I am trying to compose a dynamic query involving a dynamic table join (photos to albums). My first attempt works only on one to many (photos to place):

defmodule Test1 do
  def filter_by_place_id(dynamic, id) do
    dynamic([p], ^dynamic and p.place_id == ^id)
  end
end
dynamic =
  true
  |> Test1.filter_by_place_id(248)

But this will not work for many to many fields. Which I believe require a table join. So my next attempt:

defmodule Test2 do
  def filter_by_place_id({query, dynamic}, id) do
    dynamic = dynamic([p], ^dynamic and p.place_id == ^id)
    {query, dynamic}
  end
  def filter_by_album_id({query, dynamic}, id) do
    query = join(query, :inner, [p], album in assoc(p, :albums), as: :x)
    dynamic = dynamic([{:x, x}], ^dynamic and x.id == ^id)
    {query, dynamic}
  end
end
query = from(p in Photo)
{query, dynamic} =
  {query, true}
  |> Test2.filter_by_place_id(248)
  |> Test2.filter_by_album_id(10)
  |> Test2.filter_by_album_id(11)

The idea is that I would have filter_by_album_id, filter_by_category_id, filter_by_person_id, etc that could be called 0…n times depending on the search string the user entered.

But this fails because the binding :x is hard coded, and obviously I can’t reuse it. But I need a binding to ensure that the where clause refers to the correct join.

But if I try to use as: ^binding instead of as :x, I get the error:

** (Ecto.Query.CompileError) `as` must be a compile time atom, got: `^binding`
    (ecto 3.6.2) expanding macro: Ecto.Query.join/5
    meow.exs:30: Test2.filter_by_album_id/2

So I am not sure where to go from here. Is it possibly to dynamically allocate the binding for a join?

On stack overflow I received the comment " dynamic expressions are allowed for the join's :on option", but really not sure how this could solve the problem at hand.

2

Hi Brian!

I don’t think this approach would work well.
Let’s look at your example of usage:

  {query, true}
  |> Test2.filter_by_place_id(248)
  |> Test2.filter_by_album_id(10)
  |> Test2.filter_by_album_id(11)

What kind of SQL do you expect from this expression? I guess, the result would be the following:

TRUE and photos.place_id = 248 and albums.id = 10 and albums.id = 11

The problem here is that albums.id can’t be 10 and 11 at the same time. If you insert or instead of and then generated SQL:

(TRUE and photos.place_id = 248 and albums.id = 10) or albums.id = 11

but I think you’d expect this result:

TRUE and photos.place_id = 248 and (albums.id = 10 or albums.id = 11)

It would be easier to completely get rid of dynamic and use in operator:

Photo
|> where(place_id: ^248)
|> join(:inner, [p], albums in assoc(p, :albums), as: :albums)
|> where([albums: albums], albums.id in ^[10, 11])

I don’t quite understand why do you need this.

3

Thanks for your response. You have given me something to thing about.

I guess I might have oversimplified. Ideally I don’t want to have to hard code the values for albums. As I have albums, categories, people, and places that are essentially the same. I already have a list of maps that describe the fields. Meaning a better representation of the code would be:

defmodule Test2 do
  def filter_by_place_id({query, dynamic}, id) do
    dynamic = dynamic([p], ^dynamic and p.place_id == ^id)
    {query, dynamic}
  end

  def filter_by_value({query, dynamic}, field, id) do
    query = join(query, :inner, [p], album in assoc(p, ^field.id), as: :x)
    dynamic = dynamic([{:x, x}], ^dynamic and x.id == ^id)
    {query, dynamic}
  end
end

query = from(p in Photo)

{query, dynamic} =
  {query, true}
  |> Test2.filter_by_place_id(248)
  |> Test2.filter_by_value(%{id: :albums}, 10)
  |> Test2.filter_by_value(%{id: :categorys}, 10)

I guess I could group albums together in one query, like you suggest. This might make sense actually. But then I really would need one method for each association type, which I would like to avoid. Especially as the only difference in the functions is the as: keyword.

4

You have to implement filter_by_value function as a macro in order to set :as at compile time.

5

Was hoping to avoid macros. But if that is what I have to do, then that is what I have to do, I guess.

Thanks for you help.

6

Another solution was given on stack overflow:

The other answer gave me an idea, drop the dynamic and just use multiple where clauses. I wasn’t sure this would be supported. It looks like multiple where clauses get anded together.

def filter_by_value({query, dynamic}, field, id) do                            
  query = join(query, :inner, [p], album in assoc(p, ^field.id))               
  |> where([p, ..., x], x.id == ^id)                                           
  {query, dynamic}                                                             
end

Unfortunately this removes the flexibility you get with dynamic, which I would really like, but for now this is an adequate solution.

7

You can also take a look at this free Ecto Cookbook by Dashbit where they explain how to use dynamic queries and much more: The Little Ecto Cookbook - Dashbit ebooks

1 Like
8

Alternate workaround with caveats here.