Sort doesn't work with join table filter

[michaelst]

When adding the filter on a many to many relationship where the Database resource has a :name field and the many to many users also has a :name field, the sort does not work.

Database
|> Ash.Query.sort(:name)
|> Ash.Query.filter(users.id == ^user.id or restrict_access == false)

[zachdaniel]

🤔 I really don't see how this can be happening. I've added a test that mimics the behavior:

    post1 =
      Post
      |> Ash.Changeset.new(%{title: "aaa", score: 0})
      |> Api.create!()

    post2 =
      Post
      |> Ash.Changeset.new(%{title: "bbb", score: 1})
      |> Api.create!()

    post3 =
      Post
      |> Ash.Changeset.new(%{title: "ccc", score: 0})
      |> Api.create!()

    PostLink
    |> Ash.Changeset.new()
    |> Ash.Changeset.manage_relationship(:source_post, post1, type: :append)
    |> Ash.Changeset.manage_relationship(:destination_post, post3, type: :append)
    |> Api.create!()

    PostLink
    |> Ash.Changeset.new()
    |> Ash.Changeset.manage_relationship(:source_post, post2, type: :append)
    |> Ash.Changeset.manage_relationship(:destination_post, post2, type: :append)
    |> Api.create!()

    PostLink
    |> Ash.Changeset.new()
    |> Ash.Changeset.manage_relationship(:source_post, post3, type: :append)
    |> Ash.Changeset.manage_relationship(:destination_post, post1, type: :append)
    |> Api.create!()

    assert [
             %{title: "aaa"},
             %{title: "bbb"},
             %{title: "ccc"}
           ] =
             Api.read!(
               Post
               |> Ash.Query.sort(title: :asc)
               |> Ash.Query.filter(linked_posts.title in ["aaa", "bbb", "ccc"])
             )

    assert [
             %{title: "ccc"},
             %{title: "bbb"},
             %{title: "aaa"}
           ] =
             Api.read!(
               Post
               |> Ash.Query.sort(title: :desc)
               |> Ash.Query.filter(linked_posts.title in ["aaa", "bbb", "ccc"])
             )

[zachdaniel]

03:07:13.082 [debug] QUERY OK source="posts" db=0.7ms queue=1.1ms
SELECT p0."id", p0."title", p0."score", p0."public", p0."category", p0."type", p0."price", p0."decimal", p0."status", p0."status_enum", p0."status_enum", p0."point", p0."uniq_one", p0."uniq_two", p0."uniq_custom_one", p0."uniq_custom_two", p0."created_at", p0."updated_at", p0."author_id" FROM "posts" AS p0 INNER JOIN "public"."post_links" AS p1 ON (p1."state"::varchar = $1) AND (p0."id" = p1."source_post_id") INNER JOIN "public"."posts" AS p2 ON (p0."type"::varchar = $2) AND (p2."id" = p1."destination_post_id") WHERE (p0."type"::varchar = $3) AND (p2."title"::varchar = ANY($4::varchar[])) ORDER BY p0."title" DESC ["active", "sponsored", "sponsored", ["aaa", "bbb", "ccc"]]

[zachdaniel]

We explicitly refer to the proper binding for sorting. If you have a chance to reproduce this in a test I'd appreciate it :)

[zachdaniel]

Had more conversation over discord and got this one resolved. Still working on the other one.