Is there a way to access parent object in where clause of child?

[khajaphysist]

Let's say there is a player table as follows

id	power
1	10
2	8
3	4

and I add an array relation can_defeat from player to player. For each player I want to get list of players that he can defeat (lower power). To do this, in the filter clause of can_defeat, I need access to power of the parent object. But I am unable to find how to do this from docs, or is it even possible to do that?

query SomeQuery {
  player {
    id
    can_defeat(where:{how to query?}) {
      id
    }
  }
}

the result of the above query should be

{
  "data": {
    "players": [
      {
        "id": 1,
        "can_defeat": [
          {
            "id": 2
          },
          {
            "id": 3
          }
        ]
      },
      {
        "id": 2,
        "can_defeat": [
          {
            "id": 3
          }
        ]
      },
      {
        "id": 3,
        "can_defeat": []
      }
    ]
  }
}

So, my question is, is it possible to access parent object in the where clause of nested array objects?

[Xetera]

You would need a separate junction table to be able to do this. Maybe something like player_matchups

winner_id	loser_id
1	2
1	3
2	3

And from there you should automatically be able to filter relevant results by the foreign key relationship between players and player_matchups

query SomeQuery {
  player {
    id
    matchups {
      loser {
        id
      }
    }
  }
}

[khajaphysist]

@Xetera Thanks for the suggestion. The player_matchups table is the result of running the query

SELECT p1.id AS winner_id, p2.id AS loser_id, FROM players AS p1 CROSS JOIN players AS p2 WHERE p1.power > p2.power;

which needs to already exist. Here WHERE clause is hardcoded with p1.power > p2.power, but, I want to be able to specify this condition dynamically from the graphql's where condition so I don't have to create a table for every type of filter I need. So I can write following queries (assume that I have an array relation players from player to player)

query CanDefeat {
  player {
    id
    players (where: { some query that resolves to "p1.power > p2.power" }) {
      id
    }
  }
}

query CanBeDefeatedBy {
  player {
    id
    players (where: { some query that resolves to "p1.power < p2.power" }) {
      id
    }
  }
}

query CanHeal {
  player {
    id
    players (where: { some query that resolves to "p1.mana > p2.damage AND p1.level > p2.level AND p1.class = p2.class" }) {
      id
    }
  }
}

And my question is, if it is possible to access the parent object values in the child where clause? If not, is there a way to specify this condition dynamically from the graphql.

One way I see this is possible is by creating a tracked function that takes where clause as parameter and use it in executing dynamic sql commands as shown here.

But I am hoping if there is a better way to do this without relying on dynamic sql, as its not so safe.

[sawankumarbundelkhandi]

I am also looking for something similar, where I want to access the column of the parent table in the where clause of the child table in the graphl query. Is it possible?

[rcauquil]

@khajaphysist @sawankumarbundelkhandi any news on that ? I need the same behavior