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?
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.