.netentity-framework-coregraphqlhotchocolate

Can you create an embedded Graphql query?

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?

Solution
    1. It is much better to implement this as a single query so that you can avoid multiple roundtrips to the server from the client.
    2. Since you have control of the server you can define whatever queries you want there. I suggest:
    type Query {
   relatedMessages(username: String!) [Message]
}

    This is assuming you already have a Message type defined.

    If the username is available via context (i.e. this query is only needed for the current logged in user) then you could omit the username variable.

    The relatedMessages query would just run the SQL you've defined.

    Despite its name, GraphQL is unfortunately (or fortunately depending on your point of view) not a full-fledged query language. It lacks built-in filtering and sorting operators, doesn't allow queries to be nested or chained (no WITH, no IN (SELECT…), and has no concept of a JOIN - these all have to be designed and implemented as part of your schema.