Issue with Dynamic query?

Lost and confused as to why the below is not working.

My query has two dynamics, the first one has no issues but for some reason the tag_condition is returning values +1 of what is stored in the tag list. If I change the query and add the t.id in ^tags directly as in the second example my query returns the correct results.

So for me example below if ^tags contains “2” the first one is returning posts with tag_id values of 3 and the second is returning post with tag_id values of 2 correctly.

I’m pretty sure this was working for me last week, and since then I rebuilt my project and am now experiencing this error. Any ideas?

tag_condition =
      if tags !== nil do
        dynamic([t], (t.id in ^tags))
      else
        dynamic([t], (not is_nil(t.id) or t.id > 0))
      end

Repo.all(
        from p in Post,
        left_join: pt in Post_Tag, on: p.id == pt.post_id,
        left_join: t in Tag, on: t.id == pt.tag_id,
        where: p.user_id == ^user.id 
          and  p.inserted_at >= ^time_period 
          and  fragment("lower(?) like lower(?)", p.title, ^"%#{query}%"),
        where: ^type_condition,
        where: ^tag_condition,
        order_by: ^order_by,
        distinct: true,
        preload: [:user, :board, :comments, :tags]
      )  

If I remove the dynamic and just add the code as where: it works

      Repo.all(
        from p in Post,
        left_join: pt in Post_Tag, on: p.id == pt.post_id,
        left_join: t in Tag, on: t.id == pt.tag_id,
        where: p.user_id == ^user.id 
          and  p.inserted_at >= ^time_period 
          and  fragment("lower(?) like lower(?)", p.title, ^"%#{query}%"),
        where: ^type_condition,
        where: t.id in ^tags,
        order_by: ^order_by,
        distinct: true,
        preload: [:user, :board, :comments, :tags]
      )

When using positional bindings, order matters. I would figure that the [t] in the dynamic([t]) isn’t representing the t in Tag from your query, but instead the p in Post as that came first.

The solution is to either use named bindings [dynamic example], or to grab the last binding instead:

tag_condition =
      if tags !== nil do
        dynamic([..., t], (t.id in ^tags))
      else
        # alternatively:
        dynamic([_p, _pt, t], (not is_nil(t.id) or t.id > 0))
      end

Thank you so much, works perfectly. I don’t often use joins or use the [p] format when doing queries so maintaining the pattern went over my head. Using the below :slight_smile:

dynamic([_p, _pt, t], t.id in ^tags)