Issue with Dynamic query?

Questions & Help Questions
,
1

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]
      )
2

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
3

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)