Can't sort by multiple nested fields

[barmaley-banned]

query

query MyQuery {
  object(where: {id: {_eq: "1"}}) {
    objectStages {
      objectStageSections(order_by: [
          {stageSection: {order: asc}}
          {stageSection: {name: asc}}
        ]) {
        stageSection {
          name
          code
          id
        }
      }
    }
  }
}

gives error

{
  "errors": [
    {
      "extensions": {
        "internal": {
          "statement": "SELECT  coalesce(json_agg(\"root\" ), '[]' ) AS \"root\" FROM  (SELECT  row_to_json((SELECT  \"_12_e\"  FROM  (SELECT  \"_11_root.ar.root.objectStages\".\"objectStages\" AS \"objectStages\"       ) AS \"_12_e\"      ) ) AS \"root\" FROM  (SELECT  *  FROM \"public\".\"object\"  WHERE ((\"public\".\"object\".\"id\") = ($2))     ) AS \"_0_root.base\" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg(\"objectStages\" ), '[]' ) AS \"objectStages\" FROM  (SELECT  row_to_json((SELECT  \"_9_e\"  FROM  (SELECT  \"_8_root.ar.root.objectStages.ar.objectStages.objectStageSections\".\"objectStageSections\" AS \"objectStageSections\"       ) AS \"_9_e\"      ) ) AS \"objectStages\" FROM  (SELECT  *  FROM \"public\".\"objectStage\"  WHERE ((\"_0_root.base\".\"id\") = (\"objectId\"))     ) AS \"_1_root.ar.root.objectStages.base\" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg(\"objectStageSections\" ORDER BY \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.id\" ASC NULLS LAST, \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.name\" ASC NULLS LAST), '[]' ) AS \"objectStageSections\" FROM  (SELECT  \"_5_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection\".\"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.name\" AS \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.name\", row_to_json((SELECT  \"_6_e\"  FROM  (SELECT  \"_5_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection\".\"stageSection\" AS \"stageSection\"       ) AS \"_6_e\"      ) ) AS \"objectStageSections\", \"_5_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection\".\"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.id\" AS \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.id\" FROM  (SELECT  *  FROM \"public\".\"objectStageSection\"  WHERE ((\"_1_root.ar.root.objectStages.base\".\"id\") = (\"objectStageId\"))     ) AS \"_2_root.ar.root.objectStages.ar.objectStages.objectStageSections.base\" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  \"_4_e\"  FROM  (SELECT  \"_3_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.base\".\"name\" AS \"name\", \"_3_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.base\".\"code\" AS \"code\", \"_3_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.base\".\"id\" AS \"id\"       ) AS \"_4_e\"      ) ) AS \"stageSection\", \"_3_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.base\".\"name\" AS \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.name\", \"_3_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.base\".\"id\" AS \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.id\" FROM  (SELECT  *  FROM \"public\".\"stageSection\"  WHERE ((\"_2_root.ar.root.objectStages.ar.objectStages.objectStageSections.base\".\"stageSectionId\") = (\"id\"))     ) AS \"_3_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.base\"      ) AS \"_5_root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection\" ON ('true')    ORDER BY \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.id\" ASC NULLS LAST, \"root.ar.root.objectStages.ar.objectStages.objectStageSections.or.stageSection.pg.name\" ASC NULLS LAST  ) AS \"_7_root.ar.root.objectStages.ar.objectStages.objectStageSections\"      ) AS \"_8_root.ar.root.objectStages.ar.objectStages.objectStageSections\" ON ('true')      ) AS \"_10_root.ar.root.objectStages\"      ) AS \"_11_root.ar.root.objectStages\" ON ('true')      ) AS \"_13_root\"      ",
          "prepared": true,
          "error": {
            "exec_status": "FatalError",
            "hint": null,
            "message": "column reference \"root.ar.root.objectStages.ar.objectStages.objectStageSections.o\" is ambiguous",
            "status_code": "42702",
            "description": null
          },
          "arguments": [
            "(Oid 114,Just (\"{\\\"x-hasura-role\\\":\\\"admin\\\"}\",Binary))",
            "(Oid 0,Just (\"1\",Text))"
          ]
        },
        "path": "$",
        "code": "unexpected"
      },
      "message": "postgres query error"
    }
  ]
}

removing one of order rules gives normal result

query MyQuery {
  object(where: {id: {_eq: "1"}}) {
    objectStages {
      objectStageSections(order_by: [
          {stageSection: {order: asc}}
        ]) {
        stageSection {
          name
          code
          id
        }
      }
    }
  }
}

[Divyansh12]

query MyQuery {
  object(where: {id: {_eq: "1"}}) {
    objectStages {
      objectStageSections(order_by: [
          {stageSection: {order: asc}},
          {stageSection: {name: asc}}
        ]) {
        stageSection {
          name
          code
          id
        }
      }
    }
  }
}

You have to use " , " between two orderby statements.

[r-moore]

I also get this error, regardless of whether I use a , between the order_by statements.

Same error in Hasura v1.2 and v1.3.

Example - query 'One' succeeds, but query 'Two' fails:

query One {
  balances(order_by: [{date: desc}, {created_at: desc}]) {
    balance
  }
}

✅

query Two {
  customers_by_pk(id: 2) {
    accounts {
      balances(order_by: [{date: desc}, {created_at: desc}]) {
        balance
      }
    }
  }
}

❌ ORDER BY "root.ar.root.accounts.ar.accounts.balances.pg." is ambiguous

Here's the error output:

{
  "errors": [
    {
      "extensions": {
        "internal": {
          "statement": "SELECT  coalesce((json_agg(\"root\" )->0), 'null' ) AS \"root\" FROM  (SELECT  row_to_json((SELECT  \"_9_e\"  FROM  (SELECT  \"_8_root.ar.root.accounts\".\"accounts\" AS \"accounts\"       ) AS \"_9_e\"      ) ) AS \"root\" FROM  (SELECT  *  FROM \"public\".\"customers\"  WHERE ((\"public\".\"customers\".\"id\") = (($2)::integer))     ) AS \"_0_root.base\" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg(\"accounts\" ), '[]' ) AS \"accounts\" FROM  (SELECT  row_to_json((SELECT  \"_6_e\"  FROM  (SELECT  \"_5_root.ar.root.accounts.ar.accounts.balances\".\"balances\" AS \"balances\"       ) AS \"_6_e\"      ) ) AS \"accounts\" FROM  (SELECT  *  FROM \"public\".\"accounts\"  WHERE ((\"_0_root.base\".\"id\") = (\"customer_id\"))     ) AS \"_1_root.ar.root.accounts.base\" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg(\"balances\" ORDER BY \"root.ar.root.accounts.ar.accounts.balances.pg.date\" DESC NULLS FIRST, \"root.ar.root.accounts.ar.accounts.balances.pg.created_at\" DESC NULLS FIRST), '[]' ) AS \"balances\" FROM  (SELECT  row_to_json((SELECT  \"_3_e\"  FROM  (SELECT  \"_2_root.ar.root.accounts.ar.accounts.balances.base\".\"balance\" AS \"balance\"       ) AS \"_3_e\"      ) ) AS \"balances\", \"_2_root.ar.root.accounts.ar.accounts.balances.base\".\"date\" AS \"root.ar.root.accounts.ar.accounts.balances.pg.date\", \"_2_root.ar.root.accounts.ar.accounts.balances.base\".\"created_at\" AS \"root.ar.root.accounts.ar.accounts.balances.pg.created_at\" FROM  (SELECT  *  FROM \"public\".\"balances\"  WHERE ((\"_1_root.ar.root.accounts.base\".\"account_id\") = (\"bank_account_id\"))     ) AS \"_2_root.ar.root.accounts.ar.accounts.balances.base\"    ORDER BY \"root.ar.root.accounts.ar.accounts.balances.pg.date\" DESC NULLS FIRST, \"root.ar.root.accounts.ar.accounts.balances.pg.created_at\" DESC NULLS FIRST  ) AS \"_4_root.ar.root.accounts.ar.accounts.balances\"      ) AS \"_5_root.ar.root.accounts.ar.accounts.balances\" ON ('true')      ) AS \"_7_root.ar.root.accounts\"      ) AS \"_8_root.ar.root.accounts\" ON ('true')      ) AS \"_10_root\"      ",
          "prepared": true,
          "error": {
            "exec_status": "FatalError",
            "hint": null,
            "message": "ORDER BY \"root.ar.root.accounts.ar.accounts.balances.pg.\" is ambiguous",
            "status_code": "42702",
            "description": null
          },
          "arguments": [
            "(Oid 114,Just (\"{\\\"x-hasura-role\\\":\\\"admin\\\"}\",Binary))",
            "(Oid 23,Just (\"\\NUL\\NUL\\NUL\\STX\",Binary))"
          ]
        },
        "path": "$",
        "code": "unexpected"
      },
      "message": "database query error"
    }
  ]
}

[Divyansh12]

@r-moore can you attach your schema representation in graphiql, since

query 'One' succeeds, but query 'Two' fails

It might not be a issue with hasura.

I think its a issue with your schema setup, since I have been using Hasura v1.2 and I have not encountered any error with ORDERBY on multiple nested fields. Only issue with ORDERBY on nested fields is that we can't use WHERE and then sort by ORDERBY and only solution for that is to write custom function. #2912

[r-moore]

It works for me when the nesting is 1 level deep, but not 2. Using a WHERE on the outer query actually seems to make no difference for me.

Another example:

accounts(where: { account_id: { _eq:"ABC123"}}) {
 balances(order_by: [{date: desc}, {created_at: desc}]) {
        balance
      }
}

and

accounts_by_pk(account_id: "ABC123") {
 balances(order_by: [{date: desc}, {created_at: desc}]) {
        balance
      }
}

...both work

but this does not:

customers_by_pk(id: 2) {
    accounts {
      balances(order_by: [{date: desc}, {created_at: desc}]) {
        balance
      }
    }
  }

I will need to check about sharing schema publicly as it is related to a client project.

[r-moore]

I copied part of our schema to a fresh Hasura instance and it works 🤔

Some tables were renamed a while back (from PascalCase to snake_case) leaving the constraints and index names in PascalCase... I don't think that could be causing this issue.

I will keep looking in to our schema to see what is different

[Divyansh12]

You can try to reset metadata of your hasura app to resolve this problem. Go to: settings -> Metadata Actions -> Click Reset Metadata

Note: After resetting metadata you need to track your tables and relations again. You can do this by : Going to Data Tab -> Click on Schema -> Click Track All for Tables -> After sometime Click Track All for relations.

[r-moore]

Here's a schema that it is reproducible on:

{
  "version": 2,
  "tables": [
    {
      "table": {
        "schema": "public",
        "name": "accounts"
      },
      "object_relationships": [
        {
          "name": "business_unit",
          "using": {
            "foreign_key_constraint_on": "business_unit_id"
          }
        }
      ],
      "array_relationships": [
        {
          "name": "balances",
          "using": {
            "foreign_key_constraint_on": {
              "column": "account_id",
              "table": {
                "schema": "public",
                "name": "balances"
              }
            }
          }
        }
      ]
    },
    {
      "table": {
        "schema": "public",
        "name": "balances"
      },
      "object_relationships": [
        {
          "name": "account",
          "using": {
            "foreign_key_constraint_on": "account_id"
          }
        }
      ]
    },
    {
      "table": {
        "schema": "public",
        "name": "business_units"
      },
      "object_relationships": [
        {
          "name": "customer",
          "using": {
            "foreign_key_constraint_on": "customer_id"
          }
        }
      ],
      "array_relationships": [
        {
          "name": "accounts",
          "using": {
            "foreign_key_constraint_on": {
              "column": "business_unit_id",
              "table": {
                "schema": "public",
                "name": "accounts"
              }
            }
          }
        }
      ]
    },
    {
      "table": {
        "schema": "public",
        "name": "customers"
      },
      "array_relationships": [
        {
          "name": "business_units",
          "using": {
            "foreign_key_constraint_on": {
              "column": "customer_id",
              "table": {
                "schema": "public",
                "name": "business_units"
              }
            }
          }
        }
      ]
    }
  ]
}

With this query:

query FailingQuery {
  customers {
    business_units {
      accounts {
        balances(order_by: [{date: desc, created_at: desc}]) {
          balance
          date
        }
      }
    }
  }
}

[r-moore]

I've just tried resetting the metadata and retracking all tables & relationships but the error persists

[Divyansh12]

I've just tried resetting the metadata and retracking all tables & relationships but the error persists

Your schema seems fine, if its working if you replicate the same schema to other app then there might be a issue in your relation settings if you changed name of some fields related to that relation.

[markerdmann]

I'm hitting the same issue. When I have multiple order by clauses, it works when the GQL query is not nested, but if it's nested I get a similar "column reference is ambiguous" error.

[r-moore]

Yeah just to be clear I can reliably reproduce this with a brand new Hasura instance and the schema posted above.

I don't believe it has anything to do with changing relation settings or names of fields

[makinde]

Any word on a fix here?

[abdullah2993]

I faced a similar issue out of nowhere after some debugging it looks like it doesn't happens on hasura/graphql-engine:v1.2.2.cli-migrations-v2 which I was using earlier but happens on the latest version of the docker image i.e. hasura/graphql-engine:latest.cli-migrations-v2.

[m9rc1n]

I got the same issue when using v2.0.0-cloud.2.

[JigneshWorld]

I have the same issue..

[AndreiGusa]

I have the same issue on version v1.3.2

[vcfxb]

I'm also having this issue

[matiasf9]

I'm also having this issue, any updates?

[nrutman]

I just encountered this on 2.x as well.

[sassela]

Hey, folks. Thanks for your patience and detailed bug report. We'll be triaging this soon, and will update here with progress. Confirming I can repro with the following:

DDL on Postgres default DB

create table customers ("id" serial NOT NULL PRIMARY KEY);

create table business_units
(
    "id" serial NOT NULL PRIMARY KEY, customer_id INT,
    CONSTRAINT fk_customer FOREIGN KEY(customer_id) REFERENCES customers(id)
);

create table accounts
(
    "id" serial NOT NULL PRIMARY KEY, business_unit_id INT,
    CONSTRAINT fk_business_unit FOREIGN KEY(business_unit_id) REFERENCES business_units(id)
);

create table balances
(
    "id" serial NOT NULL PRIMARY KEY, account_id INT,
    date TIMESTAMP,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    CONSTRAINT fk_account FOREIGN KEY(account_id) REFERENCES accounts(id)
);

and importing the schema and query linked above.

[soupi]

Hey folks. Thanks again for your patience. We have merged a fix for this bug at ee15c80 and it will be available in the next stable release within two weeks. In the meantime, you can try it out using the Docker image hasurabuild/graphql-engine:mono-branch-dev-58e5e6a-main.ubuntu.