CODEWITHSUNDEEP
graphqlhasura
user12968335
user12968335

Reputation: 33

How can I create a relationship between `json` column and a `int` (id) column in Hasura + Postgres?

I have 2 tables users and post

Table users has columns id and post, column contains an array of the form [1, 2, 3, 4, 5] - where 1, 2, 3, 4, 5 is id in table post

In the table posts the following columns id and text

Table users:

https://i.sstatic.net/ywdS7.png

Table posts:

https://i.sstatic.net/IBdpb.png

in hasura made an array relation

https://i.sstatic.net/311sd.png

Next I made the following request

{
  users_test {
    postz {
      id
    }
  }
}

I would like to receive such data in response:

    postz: [
       {
         text: 'qwe'
       },
       {
         text: 'sdf'
       }
    ]

But with such a request, I get a trace. error:

{
  "errors": [
    {
      "extensions": {
        "internal": {
          "statement": "SELECT  coalesce(json_agg(\"root\" ), '[]' ) AS \"root\" FROM  (SELECT  row_to_json((SELECT  \"_5_e\"  FROM  (SELECT  \"_4_root.ar.root.postz\".\"postz\" AS \"postz\"       ) AS \"_5_e\"      ) ) AS \"root\" FROM  (SELECT  *  FROM \"public\".\"users_test\"  WHERE ('true')     ) AS \"_0_root.base\" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg(\"postz\" ), '[]' ) AS \"postz\" FROM  (SELECT  row_to_json((SELECT  \"_2_e\"  FROM  (SELECT  \"_1_root.ar.root.postz.base\".\"id\" AS \"id\"       ) AS \"_2_e\"      ) ) AS \"postz\" FROM  (SELECT  *  FROM \"public\".\"posts\"  WHERE ((\"_0_root.base\".\"post\") = (\"id\"))     ) AS \"_1_root.ar.root.postz.base\"      ) AS \"_3_root.ar.root.postz\"      ) AS \"_4_root.ar.root.postz\" ON ('true')      ) AS \"_6_root\"      ",
          "prepared": true,
          "error": {
            "exec_status": "FatalError",
            "hint": "No operator matches the given name and argument type(s). You might need to add explicit type casts.",
            "message": "operator does not exist: json = integer",
            "status_code": "42883",
            "description": null
          },
          "arguments": [
            "(Oid 114,Just (\"{\\\"x-hasura-role\\\":\\\"admin\\\"}\",Binary))"
          ]
        },
        "path": "$",
        "code": "unexpected"
      },
      "message": "postgres query error"
    }
  ]
}

What am I doing wrong and how can I fix it?

Upvotes: 3

Views: 1746

Answers (1)

Sezgi Beck
Sezgi Beck

Reputation: 46

A few suggestions:

  1. There are some typos in your query, as far as I can tell. Try:
{
  users {
    id
    posts {
      text
    }
  }
}
  1. You don't need the post column on the users table. You just need a user_id column on the posts table, and a foreign key constraint from the posts table to the users table using the user_id and id columns of the tables respectively. Check out the docs here:

https://docs.hasura.io/1.0/graphql/manual/schema/relationships/create.html#step-3-create-an-array-relationship

https://docs.hasura.io/1.0/graphql/manual/schema/relationships/database-modelling/one-to-many.html

  1. If you have to have the post array column for some reason, you can use computed fields to create a "relationship" between a json array and another table’s id.

https://docs.hasura.io/1.0/graphql/manual/schema/computed-fields.html#table-computed-fields

Your function would:

  • Take in the json array column

  • Extract the id's

  • Return select * from table where id in id's

Example:

https://jsonb-relationships-hasura.herokuapp.com/console/api-explorer

Computed field definition at: https://jsonb-relationships-hasura.herokuapp.com/console/data/schema/public/tables/authors/modify

Run these queries:

# Get list of articles for each author
query {
  authors {
    id
    name
    articles
  }
}

# Get actual articles for each author
query {
  authors {
    id
    name
    owned_articles {
      id
      title
    }
  }
}

Upvotes: 3

Related Questions