Hasura Graphql does not return all documents when multiple foreign keys exist in a table to the same foreign field

Question

Query:

user (where: { id: {_eq: 104}}) {
    connections {
      user1
      user2
      status
    }
  }

Response:

{
  "data": {
    "user": [
      {
        "id": 104,
        "connections": [
          {
            "user1": 104,
            "user2": 111,
            "status": "pending"
          }
        ]
      }
    ]
  }
}

Expected:

{
  "data": {
    "user": [
      {
        "id": 104,
        "connections": [
          {
            "user1": 104,
            "user2": 111,
            "status": "pending"
          },
          {
            "user1": 96,
            "user2": 104,
            "status": "connected"
          },
          {
            "user1": 112,
            "user2": 104,
            "status": "pending"
          }
        ]
      }
    ]
  }
}

Why are the last two documents not showing up from the first query?

Connection table definition:

CREATE TABLE connections 
(
    id Integer PRIMARY KEY,
    user1 Integer,
    user2 Integer
    FOREIGN KEY (user1) REFERENCES users (id)
    FOREIGN KEY (user2) REFERENCES users (id)
);

I know this could be more of a SQL question, but it would be great if you can show the graphql version too

Answer 1

You have to create two relationships between users table and connections table,

Then query :

query MyQuery {
  user(where: {id: {_eq: 104}}) {
    connections {
      user1
      user2
      status
    }
    connectionsByUser2 {
      user1
      user2
      status
    }
  }
}

Output: