-
|
I've put together an example to show the issue I am facing. Imagine an Emails table which contains emails sent and a Contacts table which contains the people who are the From/To/CC, using the EmailContacts table to link them, where RecipientTypeId 0=CC, 1=From, 2=To
I want to get an output similar to this, ideally via REST (but graphql if I must): {
"value": [
{
"EmailID": 1,
"Subject": "SUBJECT: Welcome!",
"Body": "<!DOCTYPE html><html lang=\"en\"><head> <meta charset=\"UTF-8\"> <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\"> <title>Welcome Page</title></head><body> <h1>Welcome!</h1></body></html>",
"CreatedDate": "2023-11-01T00:00:00",
"Sender": "[email protected]",
"To": {
"Items": [
{
"EmailAddress" : "[email protected]"
},
{
"EmailAddress" : "[email protected]"
}
]
},
"CC": {
"Items": [
"[email protected]",
"[email protected]",
"[email protected]",
"[email protected]"
]
}
}
]
}I've given two different formats of nested items for To and CC, the format isn't a major concern. Gist: Create Tables TSQL - Data API Builder Email Example I have encountered multiple problems:
SELECT TOP 1 [table1].[EmailAddress] AS [EmailAddress]
FROM [dbo].[Contacts] AS [table1]
INNER JOIN [dbo].[vw_EmailContactRecipients_From] AS [table3] ON [table3].[ContactID] = [table1].[ContactID]
INNER JOIN [dbo].[vw_EmailContactRecipients_To] AS [table4] ON [table4].[ContactID] = [table1].[ContactID]
INNER JOIN [dbo].[vw_EmailContactRecipients_CC] AS [table5] ON [table5].[ContactID] = [table1].[ContactID]
WHERE [table3].[EmailID] = [table0].[EmailID]
AND [table4].[EmailID] = [table0].[EmailID]
AND [table5].[EmailID] = [table0].[EmailID]I'm quite happy to write any views or stored procedures to shape the data, but that won't help when there are nested items with varying quantities of data as shown below when queried in SQL. Effectively, I want to return the following data highlighted in green only: Is this possible using the Data API Builder? Any help would be very much appreciated. |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 2 replies
-
|
Hi @pholcroft
To return the data you want, this is the query you need: create or alter view dbo.vw_EmailWithContacts
as
select
e.EmailID,
e.Subject,
e.BodyHTML,
e.CreatedDate,
c_from.EmailAddress as EmailFrom,
json_query(EmailTo.EmailAddress) as EmailTo,
json_query(EmailCC.EmailAddress) as EmailCC
from
dbo.Emails e
inner join
dbo.EmailContacts ec_from on e.EmailId = ec_from.EmailId and ec_from.RecipientTypeId = 1
inner join
dbo.Contacts c_from on ec_from.ContactId = c_from.ContactId
outer apply
(select
'["' + string_agg(string_escape(c_to.EmailAddress, 'json'), '","') + '"]' as [Address]
from
dbo.Contacts c_to
inner join
dbo.EmailContacts ec_to on ec_to.RecipientTypeId = 2 and ec_to.ContactId = c_to.ContactId
where
ec_to.EmailId = e.EmailId) as EmailTo(EmailAddress)
outer apply
(select
'["' + string_agg(string_escape(c_cc.EmailAddress, 'json'), '","') + '"]' as [Address]
from
dbo.Contacts c_cc
inner join
dbo.EmailContacts ec_cc on ec_cc.RecipientTypeId = 0 and ec_cc.ContactId = c_cc.ContactId
where
ec_cc.EmailId = e.EmailId) as EmailCC(EmailAddress)The query need to some manual JSON string manipulation to get the best JSON shape, but nothing too complex. In future with improved Azure SQL (and SQL Server) JSON support, code could be simplified, most likely by getting rid of the "entities": {
"Email": {
"source": {
"object": "dbo.vw_EmailWithContacts",
"type": "view",
"key-fields": [
"EmailID"
]
},
"graphql": {
"enabled": true,
"type": {
"singular": "Email",
"plural": "Emails"
}
},
"rest": {
"enabled": true,
"path": "emails"
},
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "*"
}
]
}
]
}
}and the result will be something like: {
"value": [
{
"EmailID": 1,
"Subject": "SUBJECT: Welcome!",
"BodyHTML": "<!DOCTYPE html><html lang=\"en\"><head> <meta charset=\"UTF-8\"> <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\"> <title>Welcome Page</title></head><body> <h1>Welcome!</h1></body></html>",
"CreatedDate": "2023-11-01T00:00:00",
"EmailFrom": "[email protected]",
"EmailTo": [
"[email protected]"
],
"EmailCC": [
"[email protected]",
"[email protected]"
]
},
{
"EmailID": 2,
"Subject": "SUBJECT: Big Day Tomorrow!",
"BodyHTML": "<!DOCTYPE html><html lang=\"en\"><head> <meta charset=\"UTF-8\"> <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\"> <title>Big Day Tomorrow</title></head><body> <h1>Big Day!</h1></body></html>",
"CreatedDate": "2023-11-02T00:00:00",
"EmailFrom": "[email protected]",
"EmailTo": [
"[email protected]"
],
"EmailCC": [
"[email protected]",
"[email protected]"
]
}
]
}In will also work with GraphQL if you prefer, query {
emails {
items {
EmailID
Subject
BodyHTML
CreatedDate
EmailFrom
EmailTo
EmailCC
}
}
}but the injected JSON will be escaped (so it need to be unescaped on the client side): {
"data": {
"emails": {
"items": [
{
"EmailID": 1,
"Subject": "SUBJECT: Welcome!",
"BodyHTML": "<!DOCTYPE html><html lang=\"en\"><head> <meta charset=\"UTF-8\"> <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\"> <title>Welcome Page</title></head><body> <h1>Welcome!</h1></body></html>",
"CreatedDate": "2023-11-01T00:00:00.000Z",
"EmailFrom": "[email protected]",
"EmailTo": "[\"[email protected]\"]",
"EmailCC": "[\"[email protected]\",\"[email protected]\"]"
},
{
"EmailID": 2,
"Subject": "SUBJECT: Big Day Tomorrow!",
"BodyHTML": "<!DOCTYPE html><html lang=\"en\"><head> <meta charset=\"UTF-8\"> <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\"> <title>Big Day Tomorrow</title></head><body> <h1>Big Day!</h1></body></html>",
"CreatedDate": "2023-11-02T00:00:00.000Z",
"EmailFrom": "[email protected]",
"EmailTo": "[\"[email protected]\"]",
"EmailCC": "[\"[email protected]\",\"[email protected]\"]"
}
]
}
} |
Beta Was this translation helpful? Give feedback.
-
|
@yorek Thank you very much for your response, this solves my problem completely. I didn't even think about combining string aggregation and json_query(), nice solution. :) I will open an issue for "Allow Tables and Views as Source Entities in Relationships" As returning data as JSON will solve a lot of issues with manipulating data, is it worth requesting an enhancement to give us the option not to escape JSON in GraphQL? I found the examples in the repository demonstrate well the straightforward solutions. However, I believe it would be beneficial for future users facing similar challenges to also see scenarios where the out-of-the-box solution falls short and how to address them. Would you be open to me contributing this example with your solution? I wanted to check in advance because you may prefer to focus solely on cases that align with the intended use of the features in DAB. |
Beta Was this translation helpful? Give feedback.
-
|
Great to hear :)
|
Beta Was this translation helpful? Give feedback.
Hi @pholcroft
that's right, as per design choice, relationships only work with GraphQL.
I do think relationship should work on a View too, so I ask you to open an issue to improve DAB and remove this limitation.
There is an issue when there are multiple relationship using the same table, and a fix is being investigated (#1859)
To return the data you want, this is the query you need: