descendants error

[getong]

I just call the descendants/1 function but it return the error:

** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "."
SELECT u0."id", u0."name" FROM "agent_1"."user" AS u0 WHERE (u0."id" = ANY(WITH RECURSIVE user_tree AS (
  SELECT id,
         0 AS depth
  FROM user
  WHERE parent_id = $1::bigint
UNION ALL
  SELECT user.id,
         user_tree.depth + 1
  FROM user
    JOIN user_tree
    ON user.parent_id = user_tree.id
  WHERE user_tree.depth + 1 < $2::bigint
)
SELECT id FROM user_tree
)) [1, 2147483647]

And I execute it in the sql command line, the same error msg.

[coryodaniel]

Can you provide the elixir syntax that generated that?

[getong]

just like this:

    my_comment
     |> Comment.descendants
     |> Repo.all

[coryodaniel]

I can't reproduce this, can you make a minimal example in a github that does and share it?

[sfusato]

I have a different error for the same descendants function:

Ecto.Query.CastError at GET /admin/menu/3/nodes
lib/nemo/menus/nodes/menu_node.ex:1: value `3` in `where` cannot be cast to type :binary_id in query:

from m0 in Nemo.Menus.Nodes.MenuNode,
  where: m0.id in fragment("WITH RECURSIVE menu_nodes_tree AS (\n  SELECT id,\n         0 AS depth\n  FROM menu_nodes\n  WHERE parent_id = ?\nUNION ALL\n  SELECT menu_nodes.id,\n         menu_nodes_tree.depth + 1\n  FROM menu_nodes\n    JOIN menu_nodes_tree\n    ON menu_nodes.parent_id = menu_nodes_tree.id\n  WHERE menu_nodes_tree.depth + 1 < ?\n)\nSELECT id FROM menu_nodes_tree\n", type(^3, :binary_id), type(^2147483647, :integer)),
  select: m0

My schema uses the default :id type for primary keys/foreign keys. Not sure if that has anything to do with it.

[coryodaniel]

@sfusato are you setting the FK type in your module? https://github.com/coryodaniel/arbor/blob/master/README.md#options

Can you open a separate ticket and include a minimal example of the module and query that fails

[sfusato]

You are right. The issue was because of my lack of attention. I didn't modify/update the config options I copy-pasted from the documentation to use :id in my case:

use Arbor.Tree, foreign_key_type: :binary_id

Thank you for the swift reply. I apologize for this.