"Need Help Understanding Elixir Code: Issue with select_merge and Subqueries

I’m currently working on a project where I have encountered an issue with a particular section of my Elixir code involving select_merge and subqueries. Despite my efforts, I can’t seem to figure out why the code isn’t working as expected. Here’s the relevant snippet:

 Instance
    |> join(:inner, [i], su in StateUser,
      on: su.state_id == i.state_id and su.user_id == ^user_id,
      as: :state_users
    )
    |> join(:inner, [i], s in State,
      on: s.state_id == i.state_id and s.organisation_id == ^org_id,
      as: :state
    )
    |> select_merge([i, state: st], %{
      next_state: from(s in State,
      where: s.flow_id == st.flow_id,
      where: s.order == (st.order + 1),
      select: s.id
    ),
      previous_state: from(s in State,
      where: s.flow_id == st.flow_id,
      where: s.order == (st.order - 1),
      select: s.id
    )
    })
    |> preload(instance: [:state, creator: [:profile]])
    |> Repo.paginate(params)
  end

In this code, I’m using select_merge to combine the results of two subqueries, next_state and previous_state, based on certain conditions. However, when I run the code, the results are not as expected, and I suspect there might be an issue with how I’m structuring the subqueries or utilizing select_merge.

Specifically, I’m encountering the following problems:

  1. The subqueries (next_state and previous_state) are not returning the correct data.
  2. The select_merge operation is not merging the results as intended.

Could someone please review the code snippet and provide insights into why the select_merge and subqueries might not be working as expected? Additionally, any suggestions or alternative approaches to achieve the desired result would be greatly appreciated.

Thank you for any assistance or guidance you can provide! If further clarification or details are needed, please let me know.

What SQL is generated by this query? What SQL should be generated by this query?

Since readers can’t execute the query themselves, it will help if you’re specific about the behavior that’s being observed.

Thanks for replying,

== Compilation error in file lib/wraft_doc/document/document.ex ==
** (Ecto.Query.CompileError) unbound variable `st` in query. If you are attempting to interpolate a value, use ^var

This is the error that is generated when I run the above query .

When I update the select_merge part as below and re-run it,

 |> select_merge([i, state: st], %{
      next_state: from(s in State,
      where: s.flow_id == ^st.flow_id,
      where: s.order == ^(st.order + 1),
      select: s.id
    ),
      previous_state: from(s in State,
      where: s.flow_id == ^st.flow_id,
      where: s.order == ^(st.order - 1),
      select: s.id
    )

It generates the below error


== Compilation error in file file.ex ==
** (Ecto.Query.CompileError) `Module.__schema__(:source)` is not a valid query expression.

* If you intended to call an Elixir function or introduce a value,
  you need to explicitly interpolate it with ^

* If you intended to call a database function, please check the documentation
  for Ecto.Query.API to see the supported database expressions

* If you intended to extend Ecto's query DSL, make sure that you have required
  the module or imported the relevant function. Note that you need macros to
  extend Ecto's querying capabilities

    (ecto 3.9.5) expanding macro: Ecto.Query.select_merge/3

Unlike in SQL where simply wrapping a subquery in (…) makes it a subquery you need to be explicit in ecto telling it to do a subquery in select/select_merge. It needs to be select_merge(query, [], %{next_state: subquery(…)}). You also cannot use the bindings of the top level query in the subquery just like that. You’ll need to name the bindings (as you seem to do) and use e.g. parent_as(:state) in the subquery to refer to it.

2 Likes

Thanks for helping me out. It worked :blush: