I’m attempting to replicate an embedded self-referencing SQL query using GraphQl. The situation is that we have records in a database that are created off of other records by an internal user. The query I am trying to do is to get all of the records of a user and the associated records that were created internally from only the username of the original creator. The records in the DB contain the id of the original record to keep the relationship between them. In SQL I could do something like:
select * from message
where originalMessageId in ( select messageId from message where createdByUser = "johnDoe");
(And I have no control over this underlying data so I cant modify the structure)
I could do this as 2 separate GraphQl queries (taking the output of one and feeding it as an array variable to the where of a second) but that seems very inefficient and is going to cause implementation issues due to what I’m using GraphQl for, so I’d rather not.
I have some control over the GraphQl schema (working in .NET 6, with HotChocolate 12 and Ef Core) so I might be able to set up a one to many parent child relation but I feel like I shouldn’t need to and that GraphQl should be able to support such a query.
Any ideas on how to implement this or if it is just not possible in a single query?
